Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132

    Unanswered: union 2 tables wrong result

    hi,
    can anyone please tell me my error here.

    Code:
    ( SELECT
      Branch
      ,'Pawnshop' as 'Business Name'
      , DateRedeem AS 'Date'
      , principal * interest AS 'Net Profit'
      , 'Redeem' as 'Type'
    FROM pawnshop_tbl WHERE ServiceType = 'Redeem'|| ServiceType = 'Renew' )
    
    
    UNION
    
    ( SELECT
      Branch
      ,'Pawnshop' as 'Business Name'
      , DateSold AS 'Date'
      , AuctionPrice - Principal AS 'Net Profit'
      , 'Sold' as 'Type'
      FROM sold_tbl );
    it outputs wrong count of records, 27 records only, and what i want is 60 records.

    what i need is joining the results of two tables.

    please.

    thanks in advance
    Last edited by homer.favenir; 01-20-09 at 06:18.
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  2. #2
    Join Date
    Dec 2008
    Posts
    135
    check once there are duplicate records are there in ur tables
    keep union all and try that statement

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    bklr, nice guess, but there cannot possibly be duplicates resulting from the union

    reason: all the rows from the first SELECT have 'Redeem' as 'Type', while all the rows from the second SELECT have 'Sold' as 'Type'

    nevertheless, there could conceivably be plenty of dupes originating in each SELECT separately

    you're right about UNION ALL

    homer, use UNION ALL instead of UNION and see what happens
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    Thanks! it now gives me correct results.
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  5. #5
    Join Date
    Dec 2008
    Posts
    135
    Quote Originally Posted by homer.favenir
    Thanks! it now gives me correct results.
    ur welcome

Posting Permissions

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