A LEFT OUTER JOIN question.
I have two tables, 'items' and 'stuff'. Items has a primary key of itemid. and looksmlike this ItemID other fields... 1 ... 2 3 4 5 6 Stuff contains something like this: ItemID type info 1 0blah blah 2 0something 2 1... 3 1... I want a query which returns all the items, and if it exists, the info field from related 'stuff' of type 0. So far I've got: SELECT Items.ItemID, Stuff.info FROM Items LEFT OUT JOIN Stuff ON Items.ItemID = Stuff.ItemID WHERE (type = 0 OR type = NULL); This works if there are no Stuff records with a type other than 0, eg, the last one in the example. The Query returns items 1,2,4,5, 6, no 3. How can I make a query which returns all items and type 0's info (if it exists)? ian - 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: A LEFT OUTER JOIN question.
Try SELECT Items.ItemID, Stuff.info FROM Items LEFT JOIN Stuff ON (Items.ItemID = Stuff.ItemID (type=0 || type IS NULL)); Peter Normann -Original Message- From: mySQL list [mailto:[EMAIL PROTECTED]] Sent: 16. juni 2002 17:59 To: [EMAIL PROTECTED] Subject: A LEFT OUTER JOIN question. I have two tables, 'items' and 'stuff'. Items has a primary key of itemid. and looksmlike this ItemID other fields... 1 ... 2 3 4 5 6 Stuff contains something like this: ItemID type info 1 0blah blah 2 0something 2 1... 3 1... I want a query which returns all the items, and if it exists, the info field from related 'stuff' of type 0. So far I've got: SELECT Items.ItemID, Stuff.info FROM Items LEFT OUT JOIN Stuff ON Items.ItemID = Stuff.ItemID WHERE (type = 0 OR type = NULL); This works if there are no Stuff records with a type other than 0, eg, the last one in the example. The Query returns items 1,2,4,5, 6, no 3. How can I make a query which returns all items and type 0's info (if it exists)? ian - 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: A LEFT OUTER JOIN question.
Hi Peter, Thanks a lot - that does the trick! Incredibly quick response :) Ian -Original Message- From: Peter Normann [mailto:[EMAIL PROTECTED]] Sent: 16 June 2002 17:06 To: 'mySQL list'; [EMAIL PROTECTED] Subject: RE: A LEFT OUTER JOIN question. Try SELECT Items.ItemID, Stuff.info FROM Items LEFT JOIN Stuff ON (Items.ItemID = Stuff.ItemID (type=0 || type IS NULL)); Peter Normann -Original Message- From: mySQL list [mailto:[EMAIL PROTECTED]] Sent: 16. juni 2002 17:59 To: [EMAIL PROTECTED] Subject: A LEFT OUTER JOIN question. I have two tables, 'items' and 'stuff'. Items has a primary key of itemid. and looksmlike this ItemID other fields... 1 ... 2 3 4 5 6 Stuff contains something like this: ItemID type info 1 0blah blah 2 0something 2 1... 3 1... I want a query which returns all the items, and if it exists, the info field from related 'stuff' of type 0. So far I've got: SELECT Items.ItemID, Stuff.info FROM Items LEFT OUT JOIN Stuff ON Items.ItemID = Stuff.ItemID WHERE (type = 0 OR type = NULL); This works if there are no Stuff records with a type other than 0, eg, the last one in the example. The Query returns items 1,2,4,5, 6, no 3. How can I make a query which returns all items and type 0's info (if it exists)? ian - 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: A LEFT OUTER JOIN question.
You're welcome, Ian. Being new to this list, I'm just trying to gather enough credit to get people to look at my recent post ;-) Peter Normann -Original Message- From: mySQL list [mailto:[EMAIL PROTECTED]] Sent: 16. juni 2002 18:29 To: Peter Normann; [EMAIL PROTECTED] Subject: RE: A LEFT OUTER JOIN question. Hi Peter, Thanks a lot - that does the trick! Incredibly quick response :) Ian -Original Message- From: Peter Normann [mailto:[EMAIL PROTECTED]] Sent: 16 June 2002 17:06 To: 'mySQL list'; [EMAIL PROTECTED] Subject: RE: A LEFT OUTER JOIN question. Try SELECT Items.ItemID, Stuff.info FROM Items LEFT JOIN Stuff ON (Items.ItemID = Stuff.ItemID (type=0 || type IS NULL)); Peter Normann -Original Message- From: mySQL list [mailto:[EMAIL PROTECTED]] Sent: 16. juni 2002 17:59 To: [EMAIL PROTECTED] Subject: A LEFT OUTER JOIN question. I have two tables, 'items' and 'stuff'. Items has a primary key of itemid. and looksmlike this ItemID other fields... 1 ... 2 3 4 5 6 Stuff contains something like this: ItemID type info 1 0blah blah 2 0something 2 1... 3 1... I want a query which returns all the items, and if it exists, the info field from related 'stuff' of type 0. So far I've got: SELECT Items.ItemID, Stuff.info FROM Items LEFT OUT JOIN Stuff ON Items.ItemID = Stuff.ItemID WHERE (type = 0 OR type = NULL); This works if there are no Stuff records with a type other than 0, eg, the last one in the example. The Query returns items 1,2,4,5, 6, no 3. How can I make a query which returns all items and type 0's info (if it exists)? ian - 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
