Hi all, first project with MySQL, I've used FileMaker extensively so that mean's I have limited knowledge! I'm doing this project to learn php and mysql. I know there is a smarter way to do this but I'm trying to get it to work the way I could in Filemaker and then learn from it and do it 'properly'.

The gist of this is as follows. A customer has a have item and wants to trade it for a want item. The blue are the table names. The customer can have multiple want items for a single have item. When another customer comes in and lists his have item and its related want items, the query I'm trying to construct would list matches for said customer when he is looking at his have item. He could then find other customers that have exactly what he wants for his have item.

The bad part of this is that I made two fields in the want table where I concatenate the productid of the have item with a "-" and the productid of the want item, I also, on each record, flip that around so that it is the productid of the want item and the "-" and the productid of the have item. The idea is to self join the table on the havewant and wanthave fields, I know this is not taking advantage of how mysql can and should do it, but again, that's how I'm trying transition from what I know I can do to doing it in mysql. Here is a sample data set from the want table...


wid cid haveid productid havewant wanthave
2.....7.....1.....37.....9-37.....37-9
4.....7.....1.....35.....9-35.....35-9
5.....6.....5.....33.....32-33.....33-32
17.....7.....1.....36.....9-36.....36-9
18.....7.....6.....5.....4-5.....5-4
19.....7.....6.....35.....4-35.....35-4
20.....7.....6.....37.....4-37.....37-4
21.....7.....8.....35.....24-35.....35-24
22.....7.....8.....37.....24-37.....37-24
23.....14.....9.....24.....37-24.....24-37
24.....14.....9.....35.....37-35.....35-37


The question is; how can you self join this table to itself and make the concatenated fields on the fly as part of the query? It works simply by doing this...

select *
from Want as w
Join Want as ww on w.havewant = ww.wanthave;

This results in two records (hid 22 and 23).