(Complex) MySQL query with two join tables

[kartul]

Hello all.

I have 5 tables: posts, users, users_to_posts, anonymous, anonymous_to_posts.

Now what I try to accomplice here is when post is submitted, it's content is entered into posts table, if it's submitted by anonymous the username and email is entered into anonymous table and anonymous_id and post_id are inserted into anonymous_to_posts table. And if user is logged in, basically same data will be entered into users and users_to_posts table.

Now, I want to display the posts ordered by added time and show user/anonymous information below it. Now, I don't know how do make the query. I've tried left joins and natural joins. Now, natural joins get the data but only for one - anonymous or user.

 

I've attached database design below.

 

So, how to do it? I don't expect working query here cause it's quite hard but some link that explains multible joins or anything.

 

[attachment deleted by admin]

[vicodin]

Your making that alot harder than it needs to be. You don't need so many tables. I would get rid of the entire anonymous tables and put a field into the user section named "is_anon" and I would assign either a 1 or a 0 to indicate True or False.

 

[kartul]

Your making that alot harder than it needs to be. You don't need so many tables. I would get rid of the entire anonymous tables and put a field into the user section named "is_anon" and I would assign either a 1 or a 0 to indicate True or False.

 

Maybe ...

Anyway, I just went with natural join and left the anonymous data empty. Right now i just display anonymous instead of the name that anonymous person wrote.

The reason I use anonymous table is when one user posts many times under same details, I'll give him a change to register with the username he used if it's not already in use. Also to keep track on visitors on page maybe.

 

Thanks for the reply!

[kickstart]

Hi

 

If you must use 2 sets of tables (and I would agree it is best to avoid it), then use UNION.

 

Something like this (not tested so excuse any typos)

 

SELECT post_id, content, added, username, email, RealAnonymous
FROM
(SELECT post_id, content, added, username, email, 'Anonymous' AS RealAnonymous 
FROM posts a
INNER JOIN anonymous_to_posts b ON a.post_id = b.post_id
INNER JOIN anonymous c ON b.anonymous_id = c.anonymous_id
UNION
SELECT post_id, content, added, username, email, 'Real' AS RealAnonymous 
FROM posts z
INNER JOIN users_to_posts y ON z.post_id = y.post_id
INNER JOIN users x ON y.user_id = x.user_id) SomeAliasName
ORDER BY added

 

All the best

 

Keith