Re: question on selects in multiple tables
Hi, I faced the same problem, but the solution to this is you is you add as many qualifiers as you can (i'm sure there are more elegant ways to do it but it works for me:-))specfiy the qualifiers FOR BOTH the tables you are picking data from in the where clause : For Example : as you said lets consider two tables (made up the fields) : Table 1 : Inventory Fields are :Sno, PurchasedBy, Stock, Quantity Date Table2 :Customer Fields are : ID, Customer_Ref, Address, Phone,Name Here i assume PurchasedBy and Customer_Ref reflect the same data. So now the query to pick values would be like: Mysqlselect Inventory.*,Customer.Name from Inventory,Customer where Inventory.PurchasedBy=Customer.ID and Customer.ID='yourID'; The should work if you still find it all messed up..add one more qualifier like this : Mysqlselect Inventory.*,Customer.Name from Inventory,Customer where Inventory.PurchasedBy=Customer.ID and Customer.ID='yourID' and Inventory.PurchasedBy='youID'; I know it doesnt make a lot of sense to add another redundant Inventory.PurchasedBy since we've already said Inventory.PurchasedBy=Customer.ID but it does the job. Sundru, megasoft Ltd, Chennai, India. - Original Message - From: Cindy [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 02, 2002 12:42 PM Subject: question on selects in multiple tables OK... let's say I have two tables. Let's say one is an inventory table, a bunch of items. One of the fields is for the inventory items that have been sold, and are keys to the second table, which is a list of customers; names addresses. So let's say I want to pull out all inventory items acquired in a particular month. Some of them have been sold, some have not. I want to list them all, and if they've been sold, the names of the purchasers, if not, then just blank. Problem is, of course, when I construct something like SELECT Inventory.*, Customer.Name WHERE Inventory.PurchasedBy = Customer.ID (etc) I get an ungodly mess for those items with no Customer.Name becasue of the way the query is joined(I think that's the right terminology) across the tables. Is there a way around that? This has got to be a pretty common scenario. I've tried WHERE Inventory.PurchasedBy = Customer.ID OR Inventory.PurchasedBy = 0, but that does not help. Thanks... --Cindy -- [EMAIL PROTECTED] - 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: question on selects in multiple tables
Hi Cindy, SELECT Inventory.*, Customer.Name from Inventory Left Join Customer on Inventory.PurchasedBy = Customer.ID where Inventory.PurchasedBy = Custormer.ID; Your query is an equi-join query, which means that there should be data for only those rows for which the equality in the where clause hold. So it will not show any row in the result if there is no a corresponding ID in Customer table for the PurchasedBy column of the Inventory Table. Left Join clause forces the query to return a row even if there is no value in the second table. More information can be had from the fine manual or any book on sql. Anvar. At 11:12 PM 01/01/2002 -0800, you wrote: OK... let's say I have two tables. Let's say one is an inventory table, a bunch of items. One of the fields is for the inventory items that have been sold, and are keys to the second table, which is a list of customers; names addresses. So let's say I want to pull out all inventory items acquired in a particular month. Some of them have been sold, some have not. I want to list them all, and if they've been sold, the names of the purchasers, if not, then just blank. Problem is, of course, when I construct something like SELECT Inventory.*, Customer.Name WHERE Inventory.PurchasedBy = Customer.ID (etc) I get an ungodly mess for those items with no Customer.Name becasue of the way the query is joined(I think that's the right terminology) across the tables. Is there a way around that? This has got to be a pretty common scenario. I've tried WHERE Inventory.PurchasedBy = Customer.ID OR Inventory.PurchasedBy = 0, but that does not help. Thanks... --Cindy -- [EMAIL PROTECTED] - 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: question on selects in multiple tables
At 01.01.2002 23:12, you wrote: OK... let's say I have two tables. Let's say one is an inventory table, a bunch of items. One of the fields is for the inventory items that have been sold, and are keys to the second table, which is a list of customers; names addresses. So let's say I want to pull out all inventory items acquired in a particular month. Some of them have been sold, some have not. I want to list them all, and if they've been sold, the names of the purchasers, if not, then just blank. Problem is, of course, when I construct something like SELECT Inventory.*, Customer.Name WHERE Inventory.PurchasedBy = Customer.ID (etc) I get an ungodly mess for those items with no Customer.Name becasue of the way the query is joined(I think that's the right terminology) across the tables. Is there a way around that? This has got to be a pretty common scenario. I've tried WHERE Inventory.PurchasedBy = Customer.ID OR Inventory.PurchasedBy = 0, but that does not help. I think you should do more typing ;-) select a.name, a.partnr, b.name,b.adress from inventory a, customer b where b.purchasedby = a.id and a.purchasedby is NULL. (or a.purchased = ' ') depending how you set the default of purchasedby. HTH Oliver - 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
