Join three mysql tables

Question

If I have three tables:

posts ------ postid date_posted title content views ------ viewid post_id user_id users ------ userid username 

• What is the correct way to list all posts that have not been viewed with user_id ? Is this query correct?
  SELECT * FROM posts LEFT JOIN views ON postid=post_id WHERE user_id=123 AND postid=NULL;

• How to list all posts and their "viewing status" on user_id ?

• Can you also recommend a great (user friendly, non-geeky) resource to improve my understanding of LEFT JOIN, CROSS JOIN and (regular connections? Select * from messages, views)?

Thanks!

Answer 1

1.

 SELECT * FROM posts WHERE postid NOT IN ( SELECT post_id FROM views WHERE user_id = 123 ) 

OR

 SELECT * FROM posts LEFT JOIN views ON views.post_id = posts.postid AND views.user_id = 123 WHERE viewid IS NOT NULL 

Answer 2

 1.select * from posts where post_id in(select post_id from views where user_id<>123) or select * from posts p join views v on p.post_id=v.post_id and v.user_id<>123 2.select p.*,CASE when isnull(v.viewid,0)=0 then 0 else 1 end as viewstatus from posts p left join views v on p.post_id=v.post_id 

Answer 3

In answer to your third question, check out the link from visual-explain-of-sql-join from codinghorror.com . This will help you increase your understanding of connections.

Answer 4

1, you will also need to include the users table, but this is approximately the correct yes request

2, SELECT * FROM posts LEFT JOIN views ON postid = post_id should show that it will show all messages

3, http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

Answer 5

1) See the answer "scoota269"

2) See your own SQL, but remove AND postid=NULL

3) http://www.w3schools.com/sql/sql_join.asp