[RESOLVED] Help with writing a query using a Join (I think)

TheDefender

mySQL v 4.0.27 (so a Subquery won't work)

OK, I have 3 tables:
One is like this:

TABLE 1
table1_id (PK)

TABLE 2
table2_id (PK)
table1_id
table3_id

TABLE 3
table3_id (PK)

Now, what I want is to pull all results from table 1, but only if the following conditions are met:
a) table1_id must be in Table 2
b) table3_id must NOT be in Table 2 where table1_id exists

I hope I'm explaining this easily enough. I've tried a LEFT JOIN from table 1 to table 2, but it pulls no results. I've also tried a subquery, and that worked great on my test machine, but I am running MySQL 5, and my host has 4.0.27 and subquerying is only available in 4.1+. Upgrading is not an option.

Any assistance is greatly appreciated.

sneakyimp

Your request is pretty unclear. Joining table 2 to table 1 and returning only records where there is a match is pretty easy:

SELECT t1.*, t2.table2_id FROM table_1 t1
LEFT JOIN table_2 t2
ON t2.table1_id=t1.table1_id
WHERE t2.table2_id IS NOT NULL

It's part b that is confusing to me. What do you mean when you say 'to be in Table 2'? table3_id is a field in table 2 and will always be 'in table 2'.

I'm not certain, but it sounds like you want to select all the records in table 1 that have some matching record in table (based on the table1_id field that you list as the 2nd field of table 2) BUT YOU WANT TO EXCLUDE certain records based on some quality of the third field in table 2 and perhaps whether or not it has a corresponding record in table 3.

If you want to get all records from table 1 that have a matching record in table 2 which, in turn, has no valid matching record in table 3, this might work:

SELECT t1.*, t2.table2_id, t2.table3_id, t3.table3_id
FROM table1 t1
LEFT JOIN table2 t2
ON t2.table1_id=t1.table1_id
LEFT JOIN table3 t3
ON t3.table3_id = t2.table3_id
WHERE t2.table2_id IS NOT NULL
AND t3.table3_id IS NULL

TheDefender

Hey, actually, I did already figure it out earlier using the LEFT JOIN (I'm not too good with JOINS). In my JOIN statement, I was actually using the wrong ID field in my real query. Once I caught myself, and corrected it, it worked fine. Thanks anyhow.