union 2 tables wrong result

homer.favenir · #1 (**permalink**) 01-20-09, 05:14

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

bklr · #2 (**permalink**) 01-20-09, 05:29

check once there are duplicate records are there in ur tables
keep union all and try that statement

r937 · #3 (**permalink**) 01-20-09, 05:59

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

homer.favenir · #4 (**permalink**) 01-20-09, 06:01

Thanks! it now gives me correct results.

bklr · #5 (**permalink**) 01-21-09, 01:28

Quote:

Originally Posted by homer.favenir

Thanks! it now gives me correct results.

ur welcome

Internet Services

Web Development

Digital Marketing

Consumer Tech