Re: three table join
Tab Alleman wrote: SELECT SomeStuff FROM Table1 AS T1 LEFT JOIN Table2 AS T2 ON T1.PK = T2.FK1 RIGHT JOIN Table3 AS T3 ON T2.FK2 = T3.PK WHERE T1.PK=999 Either right-join it or reverse the table order (because you're not asking for data from t1 that is like t2 that is like t3 that is like 999, you're asking for data from t3 which has data ... in t2 which has data ... in t1 which has pk 999 You have to think of the queries 'in order', left to right ... so that your LEFT join means what it should mean :). I rarely do right joins in small queries; it makes more sense to just reverse the query. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: three table join
I've wanted to post this query example a few times (and I hope I got it right; mornings aren't my best time) ... multiple JOINs: SELECT stuff FROM table1 LEFT JOIN table2 ON table1.fk = table2.pk LEFT JOIN table3 ON table2.fk = table3.pk WHERE other_conditions ... You can repeat that as many levels as you want (performance depends on indexing and the optimizer). You need to think in terms of what would be equal to what between tables in the correct result row. So if you would do a secondary sub-select of SELECT fk from table2 where ... then you end up with a left join like above. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: three table join
Let me give this a try. I've done 3 and 4 table joins, but I did a little trial and error before I got it right. To break it down, you want to get three things: 1) All meetings that fall under a particular event select * from meetings where eid=2 2) All people id's that are part of that selected meeting list select * from meetings, mpeople where mpeople.mid=meetings.id and meetings.eid=2 3) Link the people names with the people id select * from meetings, mpeople, people where mpeople.mid=meetings.id and people.id=mpeople.id and meetings.eid=2 I think that might do it. Try it out. You only need to do a left join (or right join) if you want to grab every record regardless of whether or not there are related records. Like finding out how many people are attending each meeting. You would want all meetings regardless of whether or not they have zero people attending them. Something like: select meetings.id,count(mpeople.mid) as pcount from mpeople right join meetings on mpeople.mid=meetings.id group by mpeople.mid If that doesn't work, let me know. I'm pretty sure it's close. sql,query,queries,smallint On Tuesday, January 14, 2003, at 07:37 PM, Josh L Bernardini wrote: ?How do I get the list of all the people in all the meetings given an event id? Naturally the following doesn't work in 3.23.54. select firstname, lastname from people left join mpeople on people.id = mpeople.id where mpeople.mid IN (select meetings.id from meetings where eid=2); -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: three table join
Nice that this came up when it did.. I'm currently struggling with a three-table join. Table1.PK = Table2.FK1 Table3.PK = Table2.FK2 My last effort looks something like: SELECT SomeStuff FROM Table1 AS T1 LEFT JOIN Table2 AS T2 ON T1.PK = T2.FK1 RIGHT JOIN Table3 AS T3 ON T2.FK2 = T3.PK WHERE T1.PK=999 I want it to return 1 row, but it's returning as many rows as there are in Table3. Where am I goofing? TIA, Tab mysql -Original Message- From: Michael T. Babcock [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 15, 2003 8:30 AM To: Josh L Bernardini Cc: [EMAIL PROTECTED] Subject: Re: three table join I've wanted to post this query example a few times (and I hope I got it right; mornings aren't my best time) ... multiple JOINs: SELECT stuff FROM table1 LEFT JOIN table2 ON table1.fk = table2.pk LEFT JOIN table3 ON table2.fk = table3.pk WHERE other_conditions ... You can repeat that as many levels as you want (performance depends on indexing and the optimizer). You need to think in terms of what would be equal to what between tables in the correct result row. So if you would do a secondary sub-select of SELECT fk from table2 where ... then you end up with a left join like above. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: three table join (9 table join example inside)
Thanks to everyone who answered my question. With some further reading in the searchable archives at http://www.listsearch.com/mysql.lasso, I managed to solve the problem on my own last night and grow up to a 9 table join. Tab, I think you just want two left joins instead of a left join and a right join. SELECT SomeStuff FROM Table1 AS T1 LEFT JOIN Table2 AS T2 ON T1.PK = T2.FK1 LEFT JOIN Table3 AS T3 ON T2.FK2 = T3.PK WHERE T1.PK=999 Now for those who understand a little better, what's the difference/when do you neeed a right join, straight join, etc? Any examples would be helpful. thanks, jb PS - heres a 9 table left join. I guess you can join any number of tables so long as there is a relationship between them. select people.id, concat(lastname, ', ', firstname) as name, title, organizations.org, ptype, groupname, meeting, room, location, moderator, addnotes, dtime, edtime from people left join organizations on people.org=organizations.id left join otype on organizations.otype=otype.id left join pgroups on people.id=pgroups.pid left join groups on pgroups.gid=groups.id left join mgroups on pgroups.gid=mgroups.gid left join meetings on mgroups.mid=meetings.id left join rooms on meetings.rid=rooms.id left join locations on rooms.lid=locations.id left join events on meetings.eid=events.id where events.id=2 order by meeting \G |-+ | | Tab Alleman| | | Tab.Alleman@Metr| | | oGuide.com | | || | | 01/15/2003 08:53 | | | AM | | || |-+ --| | | | To: [EMAIL PROTECTED] | | cc: | | Subject: RE: three table join | --| Nice that this came up when it did.. I'm currently struggling with a three-table join. Table1.PK = Table2.FK1 Table3.PK = Table2.FK2 My last effort looks something like: SELECT SomeStuff FROM Table1 AS T1 LEFT JOIN Table2 AS T2 ON T1.PK = T2.FK1 RIGHT JOIN Table3 AS T3 ON T2.FK2 = T3.PK WHERE T1.PK=999 I want it to return 1 row, but it's returning as many rows as there are in Table3. Where am I goofing? TIA, Tab mysql -Original Message- From: Michael T. Babcock [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 15, 2003 8:30 AM To: Josh L Bernardini Cc: [EMAIL PROTECTED] Subject: Re: three table join I've wanted to post this query example a few times (and I hope I got it right; mornings aren't my best time) ... multiple JOINs: SELECT stuff FROM table1 LEFT JOIN table2 ON table1.fk = table2.pk LEFT JOIN table3 ON table2.fk = table3.pk WHERE other_conditions ... You can repeat that as many levels as you want (performance depends on indexing and the optimizer). You need to think in terms of what would be equal to what between tables in the correct result row. So if you would do a secondary sub-select of SELECT fk from table2 where ... then you end up with a left join like above. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: three table join
Try adding a T2.PK IS NOT NULL or T2.FK1 IS NOT NULL: SELECT SomeStuff FROM Table1 AS T1 LEFT JOIN Table2 AS T2 ON T1.PK = T2.FK1 RIGHT JOIN Table3 AS T3 ON T2.FK2 = T3.PK WHERE T1.PK=999 AND T2.FK1 IS NOT NULL Example with old tables i have: mysql select * from t1; +++ | id | v | +++ | 1 | 23 | | 2 | 18 | | 3 | 6 | +++ 3 rows in set (0.00 sec) mysql select * from t2; ++---+---++ | id | id_t1 | id_t3 | v | ++---+---++ | 1 | 0 | 0 | 2 | | 2 | 1 | 1 | 89 | | 3 | 2 | 3 | 8 | ++---+---++ 3 rows in set (0.00 sec) mysql select * from t3; ++++ | id | v1 | v2 | ++++ | 1 | 8 | 6 | | 2 | 28 | 12 | | 3 | 56 | 23 | | 4 | 2 | 34 | ++++ 4 rows in set (0.00 sec) mysql select * from t1 as T1 LEFT JOIN t2 AS T2 ON (T1.id = T2.id_t1) RIGHT JOIN t3 AS T3 ON (T2.id_t3 = T3.id) WHERE T1.id=2 AND T2.id IS NOT NULL; +++--+---+---+--++++ | id | v | id | id_t1 | id_t3 | v| id | v1 | v2 | +++--+---+---+--++++ | 2 | 18 |3 | 2 | 3 |8 | 3 | 56 | 23 | +++--+---+---+--++++ 1 row in set (0.00 sec) On Wed, 2003-01-15 at 16:53, Tab Alleman wrote: Nice that this came up when it did.. I'm currently struggling with a three-table join. Table1.PK = Table2.FK1 Table3.PK = Table2.FK2 My last effort looks something like: SELECT SomeStuff FROM Table1 AS T1 LEFT JOIN Table2 AS T2 ON T1.PK = T2.FK1 RIGHT JOIN Table3 AS T3 ON T2.FK2 = T3.PK WHERE T1.PK=999 I want it to return 1 row, but it's returning as many rows as there are in Table3. Where am I goofing? TIA, Tab mysql -Original Message- From: Michael T. Babcock [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 15, 2003 8:30 AM To: Josh L Bernardini Cc: [EMAIL PROTECTED] Subject: Re: three table join I've wanted to post this query example a few times (and I hope I got it right; mornings aren't my best time) ... multiple JOINs: SELECT stuff FROM table1 LEFT JOIN table2 ON table1.fk = table2.pk LEFT JOIN table3 ON table2.fk = table3.pk WHERE other_conditions ... You can repeat that as many levels as you want (performance depends on indexing and the optimizer). You need to think in terms of what would be equal to what between tables in the correct result row. So if you would do a secondary sub-select of SELECT fk from table2 where ... then you end up with a left join like above. -- Diana Soares - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: three table join (RE brent baisley's post) BETTER join syntax?
same results and both varying between .01, .02 seconds to execute. 6 of one half dozen of another or is there an advantage to one? My guess is that the second syntax is preferred given the reduced row count for events in it's explain table. The first statement uses left joins, the second use's Brent suggestion. Also, is the second statement what they call a straight join? thanks, jb mysql explain select people.id, concat(lastname, ', ', firstname) as name, titl e, - organizations.org, ptype, groupname, meeting, room, location, - moderator,addnotes, dtime, edtime - from people - left join organizations on people.org=organizations.id - left join otype on organizations.otype=otype.id - left join pgroups on people.id=pgroups.pid - left join groups on pgroups.gid=groups.id - left join mgroups on pgroups.gid=mgroups.gid - left join meetings on mgroups.mid=meetings.id - left join rooms on meetings.rid=rooms.id - left join locations on rooms.lid=locations.id - left join events on meetings.eid=events.id - where events.id=2 order by type; +---++---+-+-+-- ---+--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+-+-+-- ---+--+-+ | people| ALL| NULL | NULL|NULL | NULL | 17 | Using temporary; Using filesort | | organizations | eq_ref | PRIMARY | PRIMARY | 2 | people.org |1 | | | otype | eq_ref | PRIMARY | PRIMARY | 2 | organizations.oty pe |1 | | | pgroups | ALL| NULL | NULL|NULL | NULL | 14 | | | groups| eq_ref | PRIMARY | PRIMARY | 2 | pgroups.gid |1 | | | mgroups | ALL| NULL | NULL|NULL | NULL |6 | | | meetings | eq_ref | PRIMARY | PRIMARY | 2 | mgroups.mid |1 | | | rooms | eq_ref | PRIMARY | PRIMARY | 2 | meetings.rid |1 | | | locations | eq_ref | PRIMARY | PRIMARY | 2 | rooms.lid |1 | | | events| eq_ref | PRIMARY | PRIMARY | 2 | meetings.eid |1 | where used; Using index | +---++---+-+-+-- ---+--+-+ 10 rows in set (0.00 sec) === === mysql explain select people.id, concat(lastname, ', ', firstname) as name, titl e, - organizations.org, ptype, groupname, meeting, room, location, - moderator, addnotes, dtime, edtime - from people, organizations, otype, pgroups, groups, mgroups, meeting s, rooms, locations, events - where people.org=organizations.id - and organizations.otype=otype.id - and people.id=pgroups.pid - and pgroups.gid=groups.id - and pgroups.gid=mgroups.gid - and mgroups.mid=meetings.id - and meetings.rid=rooms.id - and rooms.lid=locations.id - and meetings.eid=events.id - and events.id=2 order by type; +---++---+-+-+-- ---+--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+-+-+-- ---+--+-+ | events| const | PRIMARY | PRIMARY | 2 | const |1 | Using temporary; Using filesort | | pgroups | ALL| NULL | NULL|NULL | NULL | 14 | | | mgroups | ALL| NULL | NULL|NULL | NULL |6 | where used | | people| eq_ref | PRIMARY | PRIMARY | 2 | pgroups.pid |1 | | | groups| eq_ref | PRIMARY | PRIMARY | 2 | pgroups.gid |1 | | | organizations | eq_ref | PRIMARY | PRIMARY | 2 | people.org |1 | | | otype | eq_ref | PRIMARY | PRIMARY | 2 | organizations.oty pe |1 |
three table join
I want a list of all the people in all the meetings in a given event, for the purpose of printing all their schedules. I can get a list of meetings in an event given the event id with the following query: select meetings.id from meetings where eid=2; People are assigned meetings in the table mpeople which contains a person id and a meeting id, I can get a list of people in a meeting given a meeting id: select firstname, lastname from people left join mpeople on people.id = mpeople.id where mpeople.mid=1; ?How do I get the list of all the people in all the meetings given an event id? Naturally the following doesn't work in 3.23.54. select firstname, lastname from people left join mpeople on people.id = mpeople.id where mpeople.mid IN (select meetings.id from meetings where eid=2); couldn't follow the join three tables comment in the documentation. thanks in advance, jb - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
