Combine 3 MySQL queries into one?

[petteyg359]

I've got three tables, all with different numbers of columns. They all have a column 'cidname' to relate the groups between tables, and 'name' to relate individuals between tables. I'm currently using three "SELECT * FROM tablename WHERE cidname = 'blah'" to get my data, and having to pick out elements from the different result sets. I've been looking at JOIN and UNION stuff, but can't find an example I understand.


table1
cidname = abcd cidname = abcd cidname = dontselectme
name = Goober name = Noob name = GMNE
data2 = blah123 data2 = blah321 data2 = 35215

table2
cidname = abcd cidname = abcd cidname = dontselectme
name = Goober name = Noob name = GMNE
dataa = blahz dataa = Foo dataa = 1
datab = blahy datab = Ret datab = 2
datac = blahx datac = Voo datac = 3

table3
cidname = abcd cidname = abcd cidname = dontselectme
name = Goober name = Noob name = GMNE
data = foo data = moo data = hngo3

Will "SELECT * FROM table1, table2, table3 WHERE cidname = 'abcd'" get me rows
cidname = abcd, name = Goober, data2 = blah123, dataa = blahz, datab = blahy, datac = blahx, data = foo
cidname = abcd, name = Noob, data2 = blah321, dataa = Foo, datab = Ret, datac = Voo, data = moo

?

[lyndonb]

select a.*, b.*, c.* from table1 a join table2 b on a.cidname = b.cidname join table3 c on a.cidname = c.cidname;

This (I think the syntax is correct) will work.

To separate out the results, do something like:

select a.name first_name, a.data2 somedata ..............

This will rename the columns returned for the purpose of displaying / using the data.