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: Mysql>select 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 : Mysql>select 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
