Mysql SQL Query Across 2 Tables - Not Sure How to Do It Properly

Question

I have 2 tables set up like this (irrelevant rows omitted):

> product
  - id
  - user_id

> thread
  - id
  - prod_id
  - user_id
  - due_date

now I want to create a query that picks all rows from thread table that either:

1. have a thread.user_id of (for example) "5"
2. have a prod_id where the product.user_id is "5"

I am assuming there is a way to do this, something like ...

SELECT 
    thread.due_date 
FROM
    product, thread 
WHERE 
    thread.user_id='5' 
OR 
    // not sure how to finish it

Up to this point, I would just use PHP to do this, but would really like to kick up my SQL knowledge a notch and get this working via SQL if possible.

Thanks -

Answer 1

You should be able to use an outer join to do this:

SELECT thread.* FROM thread 
LEFT OUTER JOIN product
ON thread.prod_id = product.id
WHERE thread.user_id = 5 OR product.user_id = 5

A problem you might find is that you could get duplicate threads if a thread has a user id 5 but also a product with user id 5. Not sure if that will be an issue in your case though. You may be able to use SELECT DISTINCT to remove these (thanks Alex Martelli)

Answer 2

I would do something like:

SELECT t.due_date
FROM thread t
INNER JOIN product p ON p.id = t.prod_id
WHERE t.user_id = 5 OR p.user_id = 5

Edit: My example is using the SQL 92 standard. If you'd like to stick with the SQL-89 style join from your original post you can do something like:

SELECT thread.due_date
FROM product, thread
WHERE product.id = thread.prod_id
AND (thread.user_id = 5 OR product.user_id = 5)

However, check out Beginning MySQL - it has some good information about the two standards.

Answer 3

SELECT thread.* LEFT OUTER JOIN product ON thread.prod_id=product.id WHERE thread.user_id=5 OR product.user_id=5;

I'm not sure about the SELECT thread.* but it makes sense since that table.* stuff works when GRANTing privileges.