left outer join in 3 tables

Question

I hv 3 tables
shop floor and owner
shop is parent table and other 2 are child tables

SHOP    
s_id, floor_id,s_owner,s_remarks
1,2,1,big shop   
2,2,3,near bank   
3,1,2,corner   
4,7,7,FAKE FLOOR AND OWNER   

OWNERS   
o_id, o_name, contact
1,gale,009659999999   
2,smith,00447676767   
3,pathan,0088787878   

FLOORS   
f_id, f_name
1,FIRST FLOOR   
2,SECOND FLOOR  
3,THIRD FLOOR   


select shop.s_id, floors.f_name, owners.o_id, s_remarks  
from ? join ?

TO show all records of shop even no related record occure in owners or in floors

Is this an assignment? You know it needs to be a left join, are you having trouble with the syntax? — Michael Berkowski, Jul 14 '12 at 21:06
You just need two "LEFT JOIN (table) ON (condition)" clauses — lc., Jul 14 '12 at 21:07

score 3 · Accepted Answer · answered Jul 14 '12 at 21:07

3

As you suggest in the title, you need to use a LEFT JOIN. This should get you started:

SELECT shop.s_id, floors.f_name, owners.o_id, s_remarks
FROM ?
LEFT JOIN ? ON ? = ?
LEFT JOIN ? ON ? = ?

You just need to replace the question marks with the correct table or column names.

answered Jul 14 '12 at 21:07

Mark Byers

811,555
193
1,581
1,452

You might be right. I've removed some of the details from my answer just to be safe. – Mark Byers Jul 14 '12 at 21:10
i think it will be more easy to understand for others to have answer like u have posted, before editing – Fakhr Alam Jul 14 '12 at 21:23
@FakhrAlam: Yeah, I think so too. But the [guidelines](http://meta.stackexchange.com/questions/10811/how-to-ask-and-answer-homework-questions) say "You can use pseudo-code first, and, in the spirit of creating a programming resource, you may come back after a suitable amount of time and edit your response to include more complete code." It does not say what a suitable amount of time is though. – Mark Byers Jul 14 '12 at 21:25

left outer join in 3 tables

1 Answers1