about left outer join sql

So here's the scenario, I have two tables say payment and receipt with fields

• payment_date, payment_amount, payment_party_code

and 开发者_如何学Gosimilarly

• receipt_date, receipt_amount, receipt_party_code.

I have 4 rows in payment table, and 11 rows in receipt table.

What I want to do is select all rows from payment as well as from receipt table where the party_code is same. I did by using left outer join but failed because it is doing cartesian product of data.

Please help me out with this

Thanks

If you want ONLY the records that match on the payment_party_code, you should use an "INNER JOIN". But as marc_s said, you need to make sure that you specify your join criteria in your "ON" clause.

You need to use a LEFT OUTER JOIN and define a JOIN condition on it:

SELECT 
  p.payment_date, p.payment_amount, p.payment_party_code,
  r.receipt_date, r.receipt_amount, r.receipt_party_code
FROM
  dbo.Payment p
LEFT OUTER JOIN
  dbo.Receipt r ON p.payment_party_code = r.receipt_party_code

This is for SQL Server (T-SQL). If you leave out the JOIN condition (the ON p.payment_party_code = r.receipt_party_code part), you'll get a cartesian product.

This will list all rows from the Payment table, and if they have info in the Receipt table, that'll be displayed as well.

If you want the opposite (everything from Receipt, even if there's no corresponding row in Payment for it), you need to either switch around the tables in the FROM ... LEFT OUTER JOIN (select from Receipt first), or you need to use a RIGHT OUTER JOIN so that you'll get everything from Receipt.

You might want to look at the Venn diagrams of SQL JOINs posted by Jeff Atwood - makes it quite clear what the different JOIN types really do...

Left Outer Join

The Left Outer Join logical operator returns each row that satisfies the join of the first (top) input with the second (bottom) input. It also returns any rows from the first input that had no matching rows in the second input. The nonmatching rows in the second input are returned as null values. If no join predicate exists in the Argument column, each row is a matching row

SELECT 
  pay.payment_date, pay.payment_amount, pay.payment_party_code,
  rec.receipt_date, rec.receipt_amount, rec.receipt_party_code
FROM
  payment pay
LEFT OUTER JOIN
  receipt rec ON pay.payment_party_code = rec.receipt_party_code

select * 
from party p
left outer join receipt r on p.party_code = r.party_code