Unified records for database query with Sql

Basic need is if a record has an Attribute of "Urgent", then the attributevalue should be displayed in the Urgent column. If the record has an attribute value of "closed", then the attributevalue must be displayed in the "Closed" column.

I have a query below. My problem is that among the results I am getting back, there are two records with the same RequesterID (one with a valid value in "Urgent" column and one with a value in "Closed" colum) My problem is that I need these two particular records to be displayed as one record.

Any ideas?

SELECT DISTINCT r.RequesterID, sr.ModifiedDate, p.FirstName + ' ' + p.LastName AS RequesterName, CASE WHEN sa.Attribute = 'Urgent' THEN sa.AttributeValue ELSE NULL END AS Urgent, CASE WHEN sa.Attribute = 'Closed' THEN sa.AttributeValue ELSE NULL END AS Closed FROM Requester AS r INNER JOIN SubRequester AS sr ON r.RequesterID = sr.RequesterID INNER JOIN SubRequesterAttribute AS sa ON sr.SubRequesterID = sa.SubRequesterID CROSS JOIN Personnel AS p WHERE (r.UserID = p.ContractorID OR r.UserID = p.EmployeeID) AND (sa.Attribute IN ('Urgent', 'Closed')) GROUP BY r.RequesterID, sr.ModifiedDate, p.FirstName, p.LastName, sa.Attribute, sa.AttributeValue

-------------Problems Reply------------

You will need to join to your sub requester attribute table to the query twice. One with the attribute of Urgent and one with the attribute of Close.

You will need to LEFT join to these for the instances where they may be null and then reference each of the tables in your SELECT to show the relevent attribute.

I also wouldn't reccomend the cross join. You should perform your "OR" join on the personnel table in the FROM clause rather than doing a cross join and filtering in the WHERE clause.

EDIT: Sorry, my first response was a bit rushed. Have now had a chance to look further. Due to the sub requester and the sub requester attribute both being duplicates you need to split them both up into a subquery. Also, your modified date could be different for both values. So i've doubled that up. This is completely untested, and by no means the "optimum" solution. It's quite tricky to write the query without the actual database to check against. Hopefully it will explain what I meant though.

SELECT
r.RequesterID,
p.FirstName + ' ' + p.LastName AS RequesterName,
sra1.ModifiedDate as UrgentModifiedDate,
sra1.AttributeValue as Urgent,
sra2.ModifiedDate as ClosedModifiedDate,
sra2.AttributeValue as Closed
FROM
Personnel AS p
INNER JOIN
Requester AS r
ON
(
r.UserID = p.ContractorID
OR
r.UserID = p.EmployeeID
)
LEFT OUTER JOIN
(
SELECT
sr1.RequesterID,
sr1.ModifiedDate,
sa1.Attribute,
sa1.AttributeValue
FROM
SubRequester AS sr1
INNER JOIN
SubRequesterAttribute AS sa1
ON
sr1.SubRequesterID = sa1.SubRequesterID
AND
sa1.Attribute = 'Urgent'
) sra1
ON
sra1.RequesterID = r.RequesterID
LEFT OUTER JOIN
(
SELECT
sr2.RequesterID,
sr2.ModifiedDate,
sa2.Attribute,
sa2.AttributeValue
FROM
SubRequester AS sr2
INNER JOIN
SubRequesterAttribute AS sa2
ON
sr2.SubRequesterID = sa2.SubRequesterID
AND
sa2.Attribute = 'Closed'
) sra1
ON
sra2.RequesterID = r.RequesterID

SECOND EDIT: My last edit was that there were multiple SubRequesters as well as multiple Attribute, from your last comment you want to show all SubRequesters and the two relevent attributes? You can achieve this as follows.

SELECT
r.RequesterID,
p.FirstName + ' ' + p.LastName AS RequesterName,
sr.ModifiedDate,
sa1.AttributeValue as Urgent,
sa2.AttributeValue as Closed
FROM
Personnel AS p
INNER JOIN
Requester AS r
ON
(
r.UserID = p.ContractorID
OR
r.UserID = p.EmployeeID
)
INNER JOI N
SubRequester as sr
ON
sr.RequesterID = r.RequesterID
LEFT OUTER JOIN
SubRequesterAttribute AS sa1
ON
sa1.SubRequesterID = sr.SubRequesterID
AND
sa1.Attribute = 'Urgent'
LEFT OUTER JOIN
SubRequesterAttribute AS sa2
ON
sa2.SubRequesterID = sr.SubRequesterID
AND
sa2.Attribute = 'Closed'

Generally, if you have multiple rows and want to collapse them into one, GROUP BY is the basic tool to achieve that. It looks like you tried to go in that direction but didn't quite get there. What you want to do is group by the expressions that are duplicated between the rows, and apply group functions to the other expressions that will eliminate the NULL values. I used MIN in the example below but you could just as easily use MAX; the point is that since at most one of the rows will have a value for that expression, that value is both the minimum and the maximum.

SELECT
r.RequesterID,
sr.ModifiedDate,
p.FirstName + ' ' + p.LastName AS RequesterName,
MIN(
CASE
WHEN sa.Attribute = 'Urgent' THEN sa.AttributeValue
ELSE NULL
END
) AS Urgent,
MIN(
CASE
WHEN sa.Attribute = 'Closed' THEN sa.AttributeValue
ELSE NULL
END
) AS Closed
FROM
Requester AS r
INNER JOIN SubRequester AS sr
ON r.RequesterID = sr.RequesterID
INNER JOIN SubRequesterAttribute AS sa
ON sr.SubRequesterID = sa.SubRequesterID
CROSS JOIN Personnel AS p
WHERE
(r.UserID = p.ContractorID
OR r.UserID = p.EmployeeID)
AND
(sa.Attribute IN ('Urgent', 'Closed'))
GROUP BY r.RequesterID, sr.ModifiedDate, p.FirstName + ' ' + p.LastName

Category:sql Views:0 Time:2009-03-23
Tags: database sql join

Related post

  • Loop on all database query in sql server 2010-10-26

    I have multi dbs with same structure I want to loop on them to check this select statment select scan1,scan2,scan3 from customers where .............. the output would be database name if this select has records like that: db1 db2 db3 and so on ... -

  • How to update particular record using update query in SQL Server with vb.net? 2010-12-02

    I have the query to retrieve selected record in textbox ./.. How to use this query to update record : I want when I enter sumit in textbox1 and click submit button then the record with ID 1 will be updated to name sumit/// Dim SQLData As New System.D

  • How can I compare dates in related tables and use this to select the relevant record in a query? SQL / MS Access 2012-01-31

    The details of a course change over time. I need to be able to access the version of these details that was current when the course was run. My tables are setup as follows (only relevant fields are shown): Class Details ClassID (Primary key) CourseID

  • Custom database query with Sql for a specified user_id 2011-07-22

    i've this tables: Master id some-fields Detail detail_id (counter) master_id user_id (related to another tables) I want to show all single record of master only for a specified user_id. The only approach i know ... multiply line for each detail ... T

  • No current record error when excuting a SQL Query Access 2007 2014-10-29

    Hi , I am exeucting a query to generate the report from multiple tables. I am geting the 'No Current record" error whenever i include some fields , but i dont get those error when I remove those fields Query that works: ******************* SELECT Dis

  • PHP/SQL Database querying good practice and security 2009-05-05

    So I'm a slightly seasoned php developer and have been 'doin the damn thing' since 2007; however, I am still relatively n00bish when it comes to securing my applications. In the way that I don't really know everything I know I could and should. I hav

  • How to make only single database query when accessing Linq to SQL IEnumerable object? 2009-11-11

    My application constructs a Linq To Sql query and then populates a model which is then passed to the view. The view then accesses the IEnumerable object (from the Linq to Sql query) to display the user interface. The problem I am having is that each

  • List of databases in one table, querying tables from those databases in one SQL query? 2011-01-31

    I have many databases on an SQL 2005 server. There is a main database called 'siteadmin' which has a table 'PROJECTS' which has a column 'DB_NAME' which lists the names of other databases. I need to iterate through each of those names of databases pe

  • PHP SQL database query error message 2011-02-18

    Is there anything wrong with this SQL code? I got it from a tutorial, but it's returning the following error message Database query failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the r

  • Query two databases with Oracle SQL Developer 2011-11-16

    Is there a way to query two databases (in a single query) in Oracle SQL Developer? I'm not very familiar with Oracle -- aside from standard CRUD syntax at any rate. I'm trying to do an insert into an Oracle table from a SQL Server table. Would like t

  • Hibernate query cache not refreshed with latest record of database 2015-03-13

    I am having below issue with hibernate query cache. My select query with condition return no result as the data in database doesn't match with the query condition. In this case hibernate save the query cache with identifier as blank. I insert the rec

  • Display SQL database query result as link 2011-04-24

    I have a search form on a previous page where I allow users to search for $q. I then query the database for 'keys' LIKE $q. The while loop displays the 'name' and 'weblink' of each matching database entry. This all works correctly. However, I would l

  • database mirroring/replication, SQL Server 2005 2009-04-07

    I have two database servers running SQL Server 2005 Enterprise that i want to make one of them as mirror database server. What i need is, create exact copy a database from primary server on mirror server, so when the primary server was down, we could

  • How to change column order in a table using sql query in sql server 2005? 2009-10-22

    How to change column order in a table using SQL query in SQL Server 2005? I want to rearrange column order in a table using SQL query. --------------Solutions------------- You cannot. The column order is just a "cosmetic" thing we humans care about -

  • how can I add records through a query in access? 2010-01-18

    Before I remade this simple database, I was able to perform a query and update and insert new records from that query. I can no longer do this and can't figure out why. I'm not very knowledgeable with access so any help would be greatly appreciated.

  • Insert multiple records in one query 2010-02-02

    I am trying to insert about 100,000 in my SQL Server database. It is really slow when I create 100,000 separate queries, so I tried to insert all these records in one query, it worked for the first few thousands records but then it threw me a timeout

  • Zend: How to fetch record from Database without considering case sensitive? 2010-02-03

    I am using Zend Framework. I want to fetch record from database without considering case sensitive. This is my Person Table: Id|Name |Gender|Occupation ----------------------------------- 1 |Naveed|Male |Software Engineer ----------------------------

  • Simulate records in database without entering any 2010-03-02

    I've nearly finished the development of a project and would like to test its performance, especially the database query calls. I'm using Linq to SQL to search via usernames, but I've only got around 10 'users' in my database, so I can't really get a

  • Populate JOIN into a list in one database query 2010-05-13

    I am trying to get the records from the 'many' table of a one-to-many relationship and add them as a list to the relevant record from the 'one' table. I am also trying to do this in a single database request. Code derived from http://stackoverflow.co

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.165 (s). 11 q(s)