Help with determine JOIN type

[jgillin]

Hi,
I have the following query which pulls information from 3 tables:
SELECT b.*, p.amount_paid FROM tblBusinesses b tblPayments p WHERE p.business_id = b.business_id AND p.account_id = 1

The tblPayment table contains a combined primary key (business_id and account_id), and an amount_paid column. There will only ever be 1 entry in the tblPayment table for any business/account combination (this is for a learning program, not for actual business payments)

The result I'm trying to achieve is to output all businesses whether or not a payment has been made (ie, even if no entry exists in the tblPayment table for this business/account combination).
Can someone help me figure out this query (do I need an outer join for this?)
Thanks for any help

 

[donutman]

The syntax is of the form:

[Blue]SELECT[/Blue] A.[Gray]*[/Gray][Gray],[/Gray]B.Field [Blue]FROM[/Blue] TableA A [Fuchsia]Left[/Fuchsia] [Gray]JOIN[/Gray] TableB B
   [Blue]ON[/Blue] A.ID[Gray]=[/Gray]B.ID
   [Blue]WHERE[/Blue] B.Acct[Gray]=[/Gray]1

The table on "left" side is the table that has all the rows and the table on the right is the one that may not have the associated rows. Give it a wack and report back if you don't get it to work.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SQLSister]

OK I'm confused. YOu say that you need to join 3 tables but your eample only shows 2 tables.

Yes you need a left outer join, but your problem is also the condition p.account_id= 1.

Try something like:

SELECT b.*, p.amount_paid FROM tblBusinesses b left outer join tblPayments p
on p.business_id = b.business_id 
Where p.account_id = 1 or p.account_id is null

BTW, don't use select b.* unless you absolutely need all the fields in b. Never return more columns than you need.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[donutman]

Oops, good catch on the =1.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[gjsaturday]

Would the following work?

SELECT b.*, p.amount_paid FROM tblBusinesses b left outer join tblPayments p
on p.business_id = b.business_id and p.account_id = 1

 

[jgillin]

THanks a million for all your responses. It's been a while since I've done these types of joins. It's starting to come back now.
BTW, I was originally starting out with 3 tables, but then realized I only needed 2, that's why I only have 2 in my query.
THanks