left/right join concept
I'm trying to understand the terminology a bit. A left or right join can only exist for an outer join. For an inner join, the terminology would be out of context because inner joins are symmetrical (whereas outer joins are asymmetrical). Would this be a correct understanding? Anything to add or correct, please? thanks, Thufir -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: left/right join concept
That sounds like the technical answer. I prefer an analogy a 5th grader could understand. If you have 2 overlapping circles, and inner join is the area that overlaps. A left/outer join is the all of the left circle plus the content of the right circle that overlaps. A right/outer join is just the opposite. An outer join doesn't filter the table, it just finds any matching content if it's present. Anything without matching content has a null where normal matched content would be. Brent Baisley PHP, MySQL, Linux, Mac I write code On Mar 1, 2008, at 4:16 AM, Thufir wrote: I'm trying to understand the terminology a bit. A left or right join can only exist for an outer join. For an inner join, the terminology would be out of context because inner joins are symmetrical (whereas outer joins are asymmetrical). Would this be a correct understanding? Anything to add or correct, please? thanks, Thufir -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED]
Re: left/right join concept
Somewhat tangential, but for whatever it is worth I agree with the view expressed at http://weblogs.sqlteam.com/jeffs/archive/2008/02/13/on-right-outer-joins.aspx: Right joins should be avoided (IMHO). Anything to add or correct, please? -- Rob Wultsch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
left right join?
I need to write a query that essentially does both a left and right join. I have a list of people and the rooms they occupy. Some rooms have no people. Some people have no room assigned. This gives me a list of rooms and how many people are in each room including any rooms with nobody in them: select r.room_num, count(p.peopid) from rooms r left join people p on r.roomid=p.roomid1 group by r.room_num All I need is one more row in the table with a null for the room num and a count of the number of people with no room assigned. If I change 'left' to 'right' in the query above, I get that row but then I don't get rooms with no people. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: left right join?
John, Union the 2 together. select r.room_num, count(p.peopid) from rooms r left join people p on r.roomid=p.roomid1 group by r.room_num UNION select r.room_num, count(p.peopid) from rooms r right join people p on r.roomid=p.roomid1 group by r.room_num or something like that. Should give you 2 rows with the data you are looking for. HTH, =C= | | Cal Evans | http://blog.calevans.com | | John Heim wrote: I need to write a query that essentially does both a left and right join. I have a list of people and the rooms they occupy. Some rooms have no people. Some people have no room assigned. This gives me a list of rooms and how many people are in each room including any rooms with nobody in them: select r.room_num, count(p.peopid) from rooms r left join people p on r.roomid=p.roomid1 group by r.room_num All I need is one more row in the table with a null for the room num and a count of the number of people with no room assigned. If I change 'left' to 'right' in the query above, I get that row but then I don't get rooms with no people. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LEFT/RIGHT Join?
Try: select t1.player,t1.game,t1.points,t1.ppg,t2.team as team_name from sc_scoreleaders as t1 left join sc_seasonstanding as t2 ON t1.team=t2.id You don't need to join sc_scoreleaders 2 times... Better: select t1.player,t1.game,t1.points,t1.ppg, ifnull(t2.team,No Team) as team_name from sc_scoreleaders as t1 left join sc_seasonstanding as t2 ON t1.team=t2.id On Mon, 2002-03-11 at 23:15, Johnny Withers wrote: So, it's been a while since I've actaully needed to do this and I can not for the life of me figure this out, maybe it's because I've been working for nine hours w/o getting up. Here it is: I have two tables, one table holds Team information, the other table holds top scorers. However, sometimes the top scorers are a tie between players on different teams, so I need a record returned with no team associated with it: ie: Top Scorers: Name TeamPoints John Doe Team A 423 Jane Doe Team B 233 Tie between 3 players (no team) 100 This is the left join I'm using: select t1.player,t1.game,t1.points,t1.ppg,t2.team as team_name from sc_scoreleaders as t1,sc_seasonstanding as t2 left outer join sc_scoreleaders on t1.id=t2.team; And I get these results: *** 1. row *** player: Johnny Withers game: 2 points: 200 ppg: 100 team_name: Mindbender's Fake Team *** 2. row *** player: No Team Asso game: 1 points: 1 ppg: 1 team_name: Mindbender's Fake Team Table desc and data follows: mysql desc sc_seasonstanding; ++--+--+-+-+ + | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+ + | id | int(10) unsigned | | PRI | NULL| auto_increment | | team | varchar(255) | | | | | | win| tinyint(1) unsigned | YES | | 0 | | | loss | tinyint(1) unsigned | YES | | 0 | | | pts_scored | smallint(3) unsigned | YES | | 0 | | | pts_versus | smallint(3) unsigned | YES | | 0 | | | pts_diff | smallint(3) unsigned | YES | | 0 | | ++--+--+-+-+ + 7 rows in set (0.01 sec) mysql desc sc_scoreleaders; ++--+--+-+-+ + | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+ + | id | int(10) unsigned | | PRI | NULL| auto_increment | | team | int(10) unsigned | YES | MUL | 0 | | | player | varchar(255) | YES | | NULL| | | game | varchar(255) | YES | | 0 | | | points | smallint(6) unsigned | YES | | 0 | | | ppg| smallint(6) unsigned | YES | | 0 | | ++--+--+-+-+ + data: mysql select * from sc_seasonstanding\G *** 1. row *** id: 2 team: Mindbender's Fake Team win: 32 loss: 0 pts_scored: 4052 pts_versus: 12 pts_diff: 4050 1 row in set (0.00 sec) mysql select * from sc_scoreleaders\G *** 1. row *** id: 1 team: 2 player: Johnny Withers game: 2 points: 200 ppg: 100 *** 2. row *** id: 2 team: 0 player: No Team Asso game: 1 points: 1 ppg: 1 2 rows in set (0.00 sec) Someone help me out please... Server version: 3.23.44-Max (mysql,query,table,sql,etc) - Johnny Withers [EMAIL PROTECTED] p. 601.853.0211 c. 601.209.4985 - 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 -- dsoares (sql) - 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
LEFT/RIGHT Join?
So, it's been a while since I've actaully needed to do this and I can not for the life of me figure this out, maybe it's because I've been working for nine hours w/o getting up. Here it is: I have two tables, one table holds Team information, the other table holds top scorers. However, sometimes the top scorers are a tie between players on different teams, so I need a record returned with no team associated with it: ie: Top Scorers: NameTeamPoints John DoeTeam A 423 Jane DoeTeam B 233 Tie between 3 players (no team) 100 This is the left join I'm using: select t1.player,t1.game,t1.points,t1.ppg,t2.team as team_name from sc_scoreleaders as t1,sc_seasonstanding as t2 left outer join sc_scoreleaders on t1.id=t2.team; And I get these results: *** 1. row *** player: Johnny Withers game: 2 points: 200 ppg: 100 team_name: Mindbender's Fake Team *** 2. row *** player: No Team Asso game: 1 points: 1 ppg: 1 team_name: Mindbender's Fake Team Table desc and data follows: mysql desc sc_seasonstanding; ++--+--+-+-+ + | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+ + | id | int(10) unsigned | | PRI | NULL| auto_increment | | team | varchar(255) | | | | | | win| tinyint(1) unsigned | YES | | 0 | | | loss | tinyint(1) unsigned | YES | | 0 | | | pts_scored | smallint(3) unsigned | YES | | 0 | | | pts_versus | smallint(3) unsigned | YES | | 0 | | | pts_diff | smallint(3) unsigned | YES | | 0 | | ++--+--+-+-+ + 7 rows in set (0.01 sec) mysql desc sc_scoreleaders; ++--+--+-+-+ + | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+ + | id | int(10) unsigned | | PRI | NULL| auto_increment | | team | int(10) unsigned | YES | MUL | 0 | | | player | varchar(255) | YES | | NULL| | | game | varchar(255) | YES | | 0 | | | points | smallint(6) unsigned | YES | | 0 | | | ppg| smallint(6) unsigned | YES | | 0 | | ++--+--+-+-+ + data: mysql select * from sc_seasonstanding\G *** 1. row *** id: 2 team: Mindbender's Fake Team win: 32 loss: 0 pts_scored: 4052 pts_versus: 12 pts_diff: 4050 1 row in set (0.00 sec) mysql select * from sc_scoreleaders\G *** 1. row *** id: 1 team: 2 player: Johnny Withers game: 2 points: 200 ppg: 100 *** 2. row *** id: 2 team: 0 player: No Team Asso game: 1 points: 1 ppg: 1 2 rows in set (0.00 sec) Someone help me out please... Server version: 3.23.44-Max (mysql,query,table,sql,etc) - Johnny Withers [EMAIL PROTECTED] p. 601.853.0211 c. 601.209.4985 - 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
