SQL-Query - with 4 tables

[Marcel1993]

Hey guys,

 

I want to creat a SQL-Query and I don't know how to construct the part of "where".

 

There are four tables

 

1: users - important column ID (USER_ID)

2: profiles (each user is able to create more than one profile) - important column WORLD_ID, USER_ID

(3: comp_participants (each user is able to create more than one entry in the table, each comp_participant is connected to "comp"))

4: comp - important column WORLDS (e.g. "2, 4, 20")

 

$user_id is set (e.g. 44)

 

Now I want to show all entries of "comp" if the user got a profile on a world which is listed in WORLDS in the table "comp". I thing it is a very difficult kind of SQL-query and I look forward to a answer ;-)

 

Best regards ;-)

 

[AyKay47]

use a join

[Marcel1993]

Hey,

 

thank you for answering. I read a lot of examples on the internet for join but I don't know how to start in my case :-(

 

I will be very happy if you can help me 

[AyKay47]

what do you mean you don't know how to start?

did you look at the link i provided?

[chintansshah]

Hey Marcel,

 

try below query

 

SELECT c.* FROM users u 
LEFT JOIN profiles p ON p.USER_ID = u.USER_ID 
LEFT JOIN comp c ON c.WORLD_ID = p.WORLD_ID
LEFT JOIN comp_participants cp ON cp.COMP_ID = c.COMP_ID
WHERE u.USER_ID = 44

 

Let me know the result of the query.

[Marcel1993]

Hey ;-)

 

thank you for creating the query!

 

I tried it but there is a malfunction in the script. It shows as many results as available profiles, in reference to the USED_ID. If the user got 3 profiles, the result contains 3 rows, independent whether the WORLD_ID matches the column "WORLDS" in "comp". The rows that are not matching with "comp" are empty, the right ones are filled with the correct content.

 

Thanks ;-)

[fenway]

That's what LEFT JOIN does, by definition.