MySQL Forums :: Newbie :: Help with query...


Advanced Search

Help with query...
Posted by: Steve Whitlow ()
Date: March 14, 2012 05:59PM

Hi, very first project that I'm doing to learn MySQL and PHP. I've got a pretty simple logical problem that I just can't work my way thru.

Simplifying it for this discussion these are the tables I have...

customer
have
want
product

Each with its associated id field named idcustomer, idhave, etc.

A CUSTOMER lists an product that he has in the HAVE table. The HAVE table includes; idhave, idcustomer, idproduct.

For each HAVE item, a customer will list a number of WANT products that he will trade for his HAVE product. The WANT table includes; idwant, idhave, idproduct.

I am trying to build a query that is constrained to a customer and his/her want item, the result is any matching items in the HAVE/WANT tables from other customers.

Because of my limited experience with the flavors of MySQL (I can barely join strings together), I ended up cheating and creating additional fields (so much for normalization) in the WANT table to concatenate the idproduct from the HAVE and WANT records with a "-" so that, for example, a customer has product 1 and wants 2, 3, or 4 and each record in the WANT table has the following two fields; havewant and wanthave; i.e. "1-2" and "2-1", "1-3" and "3-1", "1-4" and "4-1". What I was then able to do was something along the lines of;

select * FROM Want as w
join Want as ww on ww.havewant = w.wanthave
where customerid <> 1;

It's not quite giving the intended results. So, the question, how can I construct a query where I can find other customer's matching requests for customer id=1's HAVE/WANT requests?

Thanks!

Options: ReplyQuote


Subject Written By Posted
Help with query... Steve Whitlow 03/14/2012 05:59PM
Re: Help with query... Rick James 03/16/2012 09:13AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.