Mysql SQL Query across 2 tables - not sure how to do this correctly
I have two tables created this way (irrelevant rows omitted):
> product
- id
- user_id
> thread
- id
- prod_id
- user_id
- due_date
now I want to create a query that selects all rows from the thread table that:
- has thread.user_id (for example) "5"
- have a prod_id where product.user_id is "5"
I am guessing 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 for this, but I would really like to raise my SQL knowledge to the mark and get it working through SQL if possible.
Thanks -
a source to share
You can use outer join for 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
The problem you may find is that you can get duplicate streams if the stream has a user id of 5 and a product with a user id of 5. Not sure if this would be a problem in your case. You can use SELECT DISTINCT
to remove them (thanks to Alex Martelli)
a source to share
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 uses the SQL 92 standard. If you want to stick to a mix of the SQL-89 style 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 MySQL Beginning - it has good information on two standards.
a source to share