Help – TSQL Query | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Help – TSQL Query

I was given the below query to get the results of 3 tables into one result set. It doesn’t seem to return ALL the data. I am definately open to ideas on how to re-write this or modify. Thx Result look like: NAME, Tab3.ColA, Tab2.ColA, Tab1.ColA What the best way to do this?? Please help.
SELECT DISTINCT COALESCE (Tab1.ColB, Tab2.ColB, Tab3.ColB)
AS Name, Tab3.ColA, Tab2.ColA, Tab1.ColA
FROM (SELECT * FROM Tab3 WHERE Date = ’10/14/2006′) Tab3
RIGHT OUTER JOIN dbo.Tab4
LEFT OUTER JOIN (SELECT * FROM dbo.Tab2 WHERE Date = ’10/14/2006′) Tab2 ON dbo.Tab4.ColfF = Tab2.ColB
LEFT OUTER JOIN (SELECT * FROM dbo.Tab1 WHERE Date = ’10/14/2006′) Tab1 ON dbo.Tab4.ColfF = Tab1.ColB
ON Tab3.ColB = dbo.Tab4.ColfF

DISTINCT removes duplicates. Is this why you it isnt showing all data? If not, then its very difficult to just guess what the problem is. What data is your query not returning ? Could it be related to the date? Its usually better to send dates to SQL Server in this format ‘YYYY/MM/DD’ to prevcent any chance of MM and DD being misinterpreted
The WHERE clauses are filtering the data, so unless all rows have ’10/14/2006′ on the Date column you won’t see all rows. If your Date column has the time of day, you have to use WHERE CONVERT(VARCHAR(10), Date, 120) = CAST(’10/14/2006′ AS DATETIME)
thx for quick replies. Basically, don’t think it has to do w/DISTINCT. Each table has the same definition Tab1,Tab2,Tab3,(for different individuals) but may have different data for NAME, a value may exist based on Name, or may not, but I want to see all the ‘NAME’s (below Companies) in each table, and want to combine them to look like (Oh and of course its limited to the 10/14/2006 date – which is stored as datetime) I want for example (say 3 partners in a company who own other companies or percentage of each, or not at all)
Companies Owner1 Owner2 Owner3
———————————-
Company1 10% 10% 80%
Company2 25% Null 75%
Company3 50% 50% Null Does this make sense?
http://sql-server-performance.com/fk_datetime.asp Madhivanan Failing to plan is Planning to fail
Read about Cross-tab Reports in sql server help file Madhivanan Failing to plan is Planning to fail
Could you give me a sample query how this would work based on what you said, please? Also, give that it is from multiple tables. Many thanks!
Ah well spotted Adriaan. sql_jr, using
WHERE Date = ’10/14/2006′
is actually saying
WHERE Date = ’10/14/2006 00:00′ Use Adriaans suggestion of
WHERE CONVERT(VARCHAR(10), Date, 120) = CAST(’10/14/2006′ AS DATETIME) This will elimate the time aspect of your date times
How about this? WHERE Dateadd(day,Datediff(day,0,Date),0) = CAST(’10/14/2006′ AS DATETIME)
Madhivanan Failing to plan is Planning to fail
Ok, Guys….I think the point is being missed. I used the conversion, but the same results are being returned. Could it be the COALESCE (see above) is messing it up? If I do a straight select the tables, i see a value returned for Owner1 as Owner 2, but when I run the whole query, it doesn’t return the value there. ie
select * from Owner1_Tab
Company Owner
Company1 10% select * from Owner2_Tab
Company Owner
Company1 10% Above query returns:
Company Owner1 Owner2 Owner 3
Company1 10% NULL NULL (Owner2 should have 10%) Sorry if this is confusing. Please advise and thanks again!
The NULLs can have two reasons: (1) There is a match on the outer table, but the column is NULL. (2) There is no match on the outer table. I’ll go with option #2. *** Perhaps you are confusing a UNION query with JOINed derived tables? If you’re expecting only one of the three tables to return a row, but you don’t know which one, try SELECT * FROM table1 WHERE …
UNION
SELECT * FROM table2 WHERE …
UNION
SELECT * FROM table3 WHERE … Another thing is that the model may not be ideal – why do you have to look in three tables?
Ok, I did try a UNION query, but it seems I can’t get if the same COMPANY exists, to get the value on the same line. I need only two cols of each table. I since deleted it, let me try to rebuild it, and come back w/results..Thx
The UNION that I try brings all the data together, but the col values from each table shows in one column, where I need it in a col of its own: COMPANY OWNER1 OWNER2 OWNER3
Ok, Problem Solved……..going back to what Adriaan Option #2…missing data in the join table….actually, the query was being joined on the wrong column, which was a different representation of the right column……Thanks for all your help!
]]>