Results 1 to 2 of 2

Thread: UNION query

  1. #1
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183

    Unhappy Unanswered: UNION query

    Hi gusy, this is the first time I am trying to use "Union" query. I am trying to create a view(linking and taking data from 3 tables) so I can create a crosstab report out of it.

    Basically one table contains about 12 fields and I am trying to grab data from all of them is they are not null.So this is my query,but when it executes it only dispalys result from the first query,what am I doing wrong.

    SELECT dbo.RelocateeRemovalist.RelocateID, dbo.RelocateeRemovalist.RemovalistNumber, dbo.RelocateeRemovalist.SupplierID,
    dbo.RelocateeRemovalistAmounts.CarInsurance
    FROM dbo.RelocateeRemovalist INNER JOIN
    dbo.RelocateeRemovalistAmounts ON dbo.RelocateeRemovalist.RelocateID = dbo.RelocateeRemovalistAmounts.RelocateID
    WHERE (dbo.RelocateeRemovalistAmounts.CarInsurance IS NOT NULL)
    UNION
    SELECT dbo.RelocateeRemovalist.RelocateID, dbo.RelocateeRemovalist.RemovalistNumber, dbo.RelocateeRemovalist.SupplierID,
    dbo.RelocateeRemovalistAmounts.CarTransport
    FROM dbo.RelocateeRemovalist INNER JOIN
    dbo.RelocateeRemovalistAmounts ON dbo.RelocateeRemovalist.RelocateID = dbo.RelocateeRemovalistAmounts.RelocateID
    WHERE (dbo.RelocateeRemovalistAmounts.CarTransport IS NOT NULL)

    Thanks

  2. #2
    Join Date
    Aug 2004
    Posts
    7

    Cool

    I query seems to b fine.The UNION operator eliminates Duplicates.
    So if the result set of both queries r same then ,it may appear as though the First query alone is getting excuted.
    Try the following
    1) Find the row count of the first Query
    2) Find the row count of the Second Query
    3) Replace UNION by UNION ALL, n see if the row count returned is equal to Row count from First Query + RC from the Second Query
    IF its same then its only bcos of Duplicates getting returned

    - Karthik

Posting Permissions

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