Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2004
    Location
    Toronto
    Posts
    5

    Red face Unanswered: Trouble With Union Queries

    I've run into some trouble with an apparently simple UNION query:

    SELECT [Date] AS D, C7, NULL AS L72A, NULL AS P02, PAV
    FROM [Process Variables Level 1]
    UNION
    SELECT [Date] as D, NULL AS C7, L72A, P02, NULL AS PAV
    FROM [Process Variables Level 2]
    ORDER BY D

    Records in the 2 tables may or may not have the same Date/Time values.

    The product of the above UNION query appears to be using dates from the [Process Variables Level 2] table only. I'm getting values returned for field C7 with Date/Time values from table [Process Variables Level 2]??

    Any suggestions would be welcome!!

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Have you tried UNION ALL?

  3. #3
    Join Date
    Feb 2004
    Location
    Toronto
    Posts
    5
    OK, one problem down. It wasn't the UNION ALL as suggested, but somehow the record order was messed up in the table. This brings me to an extension of the same problem.

    The first element of my UNION query is meant to catch any entries in the two tables with the same Date/Time value. The subsequent UNIONS catch the table entries that aren't found in the first query section.

    My problem (now) is that the INNER JOIN portion of the query (the first part) is not capturing all the records from the two tables that have the same Date/Time value. Any suggestions?

    SELECT [P1].[Date] AS D,[P1].C7,[P2].L72A,[P1].PAV
    FROM [P1] INNER JOIN [P2] ON [P1].Date = [P2].Date
    UNION
    SELECT [Date] AS D,C7,NULL AS L72A,PAV
    FROM [P1] WHERE [Date] NOT IN (SELECT [1].Date FROM [P1] INNER JOIN [P2] ON [P1].Date = [P2].Date)
    UNION
    SELECT [Date] AS D,NULL AS C7,L72A,NULL AS PAV
    FROM [P2] WHERE [Date] NOT IN (SELECT [P1].Date FROM [P1] INNER JOIN [P2] ON [P1].Date = [P2].Date)
    ORDER BY D

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    dates include a time portion, right?

    how have you populated the date fields? with Now()?

    by the way, have you considered a LEFT OUTER to get the rows you want, instead of those UNIONs?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What do you get when you run this...
    Code:
          SELECT 'MATCH' AS SOURCE, [P1].[Date] AS D,[P1].C7,[P2].L72A,[P1].PAV
            FROM [P1] INNER JOIN [P2] ON [P1].[Date] = [P2].[Date]
       UNION ALL 
          SELECT 'P1' AS SOURCE, [Date] AS D,C7,NULL AS L72A,PAV
            FROM [P1] WHERE [Date] NOT IN (SELECT [P1].[Date] FROM [P1] INNER JOIN [P2] ON [P1].[Date] = [P2].[Date])
       UNION ALL
          SELECT 'P1' AS SOURCE, [Date] AS D,NULL AS C7,L72A,NULL AS PAV
            FROM [P2] WHERE [Date] NOT IN (SELECT [P1].[Date] FROM [P1] INNER JOIN [P2] ON [P1].[Date] = [P2].[Date])
        ORDER BY D
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by r937
    dates include a time portion, right?

    how have you populated the date fields? with Now()?

    by the way, have you considered a LEFT OUTER to get the rows you want, instead of those UNIONs?
    So true...

    Are you looking for a specific day?

    Look up CONVERT
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Feb 2004
    Location
    Toronto
    Posts
    5
    quote:
    --------------------------------------------------------------------------------
    Originally posted by r937
    dates include a time portion, right?

    how have you populated the date fields? with Now()?

    by the way, have you considered a LEFT OUTER to get the rows you want, instead of those UNIONs?
    --------------------------------------------------------------------------------
    Absolutely right, the date/time includes a time portion... which is crucial in my app but only down to the seconds! I believe that it's the milliseconds that are messing me up! (I used the GETDATE() function as the default value for my date/time field).

    Yes, I changed the first portion of the query to a LEFT OUTER join and removed the first UNION. The second union remains (modified) to capture any 'unmatched' records from the second table.


    quote:
    --------------------------------------------------------------------------------
    Originally posted by Brett Kaiser
    So true...
    Are you looking for a specific day?
    Look up CONVERT
    --------------------------------------------------------------------------------
    What I want to do, is ignore the millisecond portion of the records and match up the records if they date/time match down to the second level.

    Currently I have the following query.....

    SELECT [P1].[Date] AS D,[P1].C7,[P2].L72A,[P2].P02,[P1].PAV FROM [P1] LEFT OUTER JOIN [P2] ON [P1].Date = [P2].Date

    UNION

    SELECT [Date] AS D,NULL AS C7,L72A,P02,NULL AS PAV FROM [P2] WHERE [Date] NOT IN (SELECT [P1].[Date] AS D FROM [P1] LEFT OUTER JOIN [P2] ON [P1].Date = [P2].Date)

    ORDER BY D


    If I change the JOIN to.... ON CONVERT(datetime, [P1].Date,120) = CONVERT(datetime,[P2].Date,120) then I still get the duplication!

    Thanks for the responses so far, the help has been appreciated and I know I'll get to where I want to be despite my incompetence!

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well...


    If you have 2 rows that have the same datetime value, fine.

    If you have more, then you'll end up with a cartesian product..

    Do you get anything back from this?

    Code:
    SELECT 'P1' AS SOURCE, [Date], COUNT(*) FROM [P1] GROUP BY [Date] HAVING COUNT(*) > 1
    UNION ALL
    SELECT 'P2' AS SOURCE, [Date], COUNT(*) FROM [P2] GROUP BY [Date] HAVING COUNT(*) > 1
    You should get zero rows back...if you don't, then you have a problem.

    PS Not to mention it could be a problem waiting for you in your future.

    What's the PKs of the table...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Feb 2004
    Location
    Toronto
    Posts
    5
    Brett,

    Your code results in ZERO rows.

    The primary keys for the tables are the Date/Time fields. There will be no identical date/time entries within a single table, but there will be some across separate tables. Each table is stand-alone with no relations.

    The only way these tables are related are through the query that I'm developing so that data from separate tables can be displayed together on a chart.

    I want to eliminate the duplicate rows for the chart because it's an ASP.NET application and performance is a consideration.

  10. #10
    Join Date
    Feb 2004
    Location
    Toronto
    Posts
    5
    Hey guys, I think I just figured it out. When I modified the second queries' (the UNIONed portion) WHERE clause to CONVERT the [Date] field before the NOT IN clause, then the duplicate values were gone! I forgot to consider the millisecond issue in the second query section!!

    Thanks for all the help!!!!


    This is what I ended up with.......

    SELECT [P1].[Date] AS D, [P1].C7, [P2].L72A, [P2].P02, [P1].PAV
    FROM [P1] LEFT OUTER JOIN [P2] ON CONVERT(varchar,[P1].Date,120) = CONVERT(varchar,[P2].Date,120)

    UNION

    SELECT [Date] AS D, NULL AS C7, L72A, P02, NULL AS PAV
    FROM [P2] WHERE CONVERT(varchar,[Date],120) NOT IN (SELECT CONVERT(varchar,[P1].Date,120) AS D FROM [P1] LEFT OUTER JOIN
    [P2] ON CONVERT(varchar,[P1].Date,120) = CONVERT(varchar,[P2].Date,120))

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well if you're using UNION, there will be no duplicates...unless we need define what you mean by duplicates....

    And zero in this case is good...


    Also your key is down to the microseconds, but you want to lope thos off...

    check out what this does

    SELECT CONVERT(datetime,GetDate(),120)
    SELECT CONVERT(varchar(25),GetDate(),120)


    And what does this return:

    Code:
       SELECT 'P1' AS SOURCE, CONVERT(varchar(25),[Date],120), COUNT(*) 
         FROM [P1] GROUP BY CONVERT(varchar(25),[Date],120) HAVING COUNT(*) > 1
    UNION ALL
       SELECT 'P2' AS SOURCE, CONVERT(varchar(25),[Date],120), COUNT(*) 
         FROM [P2] GROUP BY CONVERT(varchar(25),[Date],120) HAVING COUNT(*) > 1
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •