SQL Server Performance

Help - TSQL Query

Discussion in 'T-SQL Performance Tuning for Developers' started by sql_jr, Oct 20, 2006.

  1. sql_jr New Member

    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
  2. Chappy New Member

    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
  3. Adriaan New Member

    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)
  4. sql_jr New Member

    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?
  5. Madhivanan Moderator

    http://sql-server-performance.com/fk_datetime.asp

    Madhivanan

    Failing to plan is Planning to fail
  6. Madhivanan Moderator

    Read about Cross-tab Reports in sql server help file

    Madhivanan

    Failing to plan is Planning to fail
  7. sql_jr New Member

    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!
  8. Chappy New Member

    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
  9. Madhivanan Moderator

    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
  10. sql_jr New Member

    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!
  11. Adriaan New Member

    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?
  12. sql_jr New Member

    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
  13. sql_jr New Member

    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

  14. sql_jr New Member

    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!

Share This Page