a sql question about linking tables

Question

I wonder why these statements are not the same (the first one worked for me)

AND user_thread_map.user_id = $user_id
AND user_thread_map.thread_id = threads.id

AND user_thread_map.user_id = users.id
AND user_thread_map.thread_id = threads.id
AND users.id = $user_id

Shouldn't they be the same? In the 2nd one I linked all the tables in the first 2 lines, then I tell it to select where users.id = $user_id.

Can someone explain why the 2nd statement doesn't work? Because I thought it would.

Answer 1

Assuming you're getting no rows returned (you don't really say what the problem is, so I'm guessing a bit here), my first thought is that there are no rows in users where id is equal to $user_id.

That's the basic difference between those two SQL segments, the second is a cross-join of the user_thread_map, threads and users tables. The first does not join with users at all, so that's where I'd be looking for the problem.

It appears that your user_thread_map table is a many-to-many relationship between users and threads. If that is true, are you sure you have a foreign key constraint between the ID fields in that table to both corresponding other tables, something like:

users:
    id integer primary key
    name varchar(50)
threads:
    id integer primary key
    thread_text varchar(100)
user_thread_map:
    user_id integer references users(id)
    thread_id integer references threads(id)

If you have those foreign key constraints, it should be impossible to end up with a user_thread_map(user_id) value that doesn't have a corresponding users(id) value.

If those constraints aren't there, a query can tell you which values need to be fixed before immediately adding the constraints (this is important to prevent the problem from re-occurring), something like:

select user_thread_map.user_id
from user_thread_map
left join users
on user_thread_map.user_id = users.id
where users.id is null

Answer 2

The first one would select records from table user_thread_map with user_id = $user_id, irrespective of whether a record in table user existed with that id. The second query would only return something if the related record in user is found.