PDA

View Full Version : Anyone an expert on SQL Joins please?



bluedalmatian
November 6th, 2008, 04:33 PM
Ive got two tables A & B linked with a join table like this:

----------------
A
----------------
id | field2|
----------------




----------------
B
----------------
id | field2|
----------------







----------------
A_B
----------------
a_id | b_id
----------------


I want a query which will return all B records, which have an entry in the join table for a particular a_id

Which type of join is the best for this please?

Thanks

Paul41
November 6th, 2008, 04:58 PM
I'm not sure if I understand what you are trying to do but if I do you need a left outer join and then restrict the values to what you want for a_id in the where clause.

bluedalmatian
November 6th, 2008, 05:13 PM
I want all records from B which have a link to a particular A record

Paul41
November 6th, 2008, 05:16 PM
Then yes, this will work.

bcarvalho
November 6th, 2008, 05:29 PM
Try this statement and tell us if it worked out for ya:

SQL> SELECT a.id, a.field2, b.id, b.field2
2 FROM A a LEFT OUTER JOIN B b
3 ON a.field2 = b.field2 OR a.id = b.id;

Best regards,
Bruno Carvalho

bluedalmatian
November 6th, 2008, 05:38 PM
Thanks Paul

I tried doing

select * from B left outer join a_b on a_id =12

but it didnt work. Can you enlighten me as to what I should be typing.

Thanks again

Andrew

bluedalmatian
November 6th, 2008, 05:45 PM
bcarvalho i want to search for only a particular A id (e.g all B's which are assocated with A record number 12

koenn
November 6th, 2008, 05:51 PM
select * from A_B inner join B on A_B.b_id = B.id
where a_id = 42 ;

check for syntax errors, my sql is a bit rusty.

Delever
November 6th, 2008, 05:54 PM
I tried to expand it to make it more clear:



select B.id, B.field // this is what fields from b you want to take
from
B
inner join // this is the way you want to join tables B and A_B
A_B
on A_B.b_id = B.id // how to join A_B and B
where
A_B.a_id = whatever


This is if you want to include field from A table too:



select B.id, B.field, A.field // this is what fields from b you want to take
from
B
inner join // this is the way you want to join tables B and A_B
A_B
inner join // join A_B and A
A
on A.id = A_B.a_id // how to join A_B and A
on A_B.b_id = B.id // how to join A_B and B
where
a.id = whatever

bluedalmatian
November 6th, 2008, 06:23 PM
thanks koenn that works perfectly.

koenn
November 6th, 2008, 06:40 PM
thanks koenn that works perfectly.

cool.
Was it home work ?

bluedalmatian
November 9th, 2008, 05:39 PM
lol no its for this program Im writing.

what about doing something similar in reverse...like this http://www.simple.org/sqlexample.png

koenn
November 9th, 2008, 06:11 PM
lol no its for this program Im writing.

what about doing something similar in reverse...like this http://www.simple.org/sqlexample.png

same as before, except you include table A in the join, and you'd say "where B.fiels2 = 'xyz' "

something like

select A.id, A.field2,A.field3
from A inner join A_B on A.id = A_B.a_id
inner join B on A_B.b_id = B.id

where B.fiels2 = 'xyz'
;


It would be harder if there wasn't a row (5,3) in table A_B, then you would exclude id 5 from table A because there's no corresponding key in A_B and thus no record that can have B.fiels2 = 'xyz'


You should probably get a beginners tuorial on SQL if tou want to write programs with a database backend :)