CODEWITHSUNDEEP
phpmysqljoinconditional-statements
FLX
FLX

Reputation: 2679

Mysql : left join on variable table

I have a problem with a complex SQL query.

I have 3 tables : user, user_type1, user_type2

I'm trying to select the details of a users depending of his type. I tried to use CASE to select the table name dynamically for the LEFT JOIN, but I failed. I also tried with UNION but it seems that the columns must be the same.

The only way that worked is to join the two tables, but then the result also contains all the null values from the wrong table...

I'm looking for a way to get rid of these wrong fields, or to do only the LEFT JOIN for the good table.

Here is my current code :

SELECT
    user.*,
    user_type1.*,
    user_type2.*
FROM user
LEFT JOIN user_type1
    ON user_type1.user_id = user.id
LEFT JOIN user_type2
    ON user_type2.user_id = user.id
AND user.id = 1

Usually I only do really simple queries so i'm a bit confused. Thanks if you can help me.

EDIT : The main table is user, with only 2 fields : ID and Type.

The other tables contains details about the user. user_type1 is for people. The fields are name, age, ...

user_type2 is for companies. The fields are name, legal form, number of employees, ...

So I only know the ID from the user table and I need to get the fields for the good table.

If user.id is 1 and user.type is type1, I want mysql to get all the fields from user_type1 table...

Upvotes: 0

Views: 742

Answers (2)

Learner
Learner

Reputation: 421

You can try this :-

select * 
from user, user_type1, user_type2 
where user_type1.user_id = user.id and user_type2.user_id = user.id AND user.id = 1

Upvotes: 2

Vineet1982
Vineet1982

Reputation: 7918

then you must select columns that are not null as:

SELECT
    user.*,
    user_type1.*,
    user_type2.*
FROM user
LEFT JOIN user_type1
    ON user_type1_id = user.id
LEFT JOIN user_type2
    ON user_type2.user_id = user.id
AND user.id = 1
AND table.column_name IS NOT NULL

Upvotes: 0

Related Questions