Need help writing a query - HTML Forums

Sawtooth500 · 09-19-2009, 10:37 PM

Alright so I'm dealing with two mysql tables here:

This is property_table

This is table dupe_relationships

Now, in property_table, each entry has a unique propid, which is auto-increment. Table dupe_relationships has 2 columns. We start our problem here with a known propid. Now, that propid may exist in dupe_relationships either in the propid1 column or propid2 column (FYI the same propid cannot exist in the same row in both the propid1 and propid2 columns). Whether our specified propid is in propid1 or propid2, the other column in the same row will have a different correlating propid. In some cases there may be multiple maches, in others none.

So what I need is a query in which you specify a propid, then it searches for that propid in both columns propid1 and propid2 within dupe_relationships, pulls out the correlating propid whenever a match is found, and the returns certain values from property_table in rows where that correlating propid is the primary key.

I hope that I've been clear enough in what I'm trying to do here. I know if I can get a query to do this it will be way simpler than having php do all the sorting manually. I just have no clue about how to write a query that complex! Thank you for the help!

Vege · 09-20-2009, 03:30 PM

Code:

select * from property_table where propid in(select propid1 from dupe_relationships where propid2='searchstring' union select propid2 from dupe_relationships where propid1 = 'searchstring')

Will that do?

Sawtooth500 · 09-20-2009, 04:07 PM

Vege, thanks a bunch, you just saved me a ton of time of trying to do the sorting in PHP with multiple queries, actually however one thing that I forgot to specify in my original request, the query returns a set that matches those certain conditions. I actually need a set returned of all the values in property_tables EXCEPT the value that match those conditions which were obtained from dupe_relationships. Thanks!

Sawtooth500 · 09-20-2009, 11:47 PM

Figured it out, I just discovered the "not" keyword

thank you again for the help vege!