List users from two tables

Hi i have user_table1 and user_table2 also i have table jobs and employers

My request works开发者_StackOverflow社区 when i get users from user_table1 and pull jobs and employers info for them, but when i join user table2 its only doubles output from user_table1 rather than showing users from second table.

SELECT user_table1.age, user_table1.name,  user_table1.lname, jobs.position, jobs.wage, employers.name employers.phone 
from user_table1 
LEFT JOIN jobs ON jobs.position_id = user_table1.position_id
LEFT JOIN employers ON employers.position_id = jobs.position_id 
WHERE user_table1.age < 30
ORDER BY user_table1.age ASC

Now i also tried to add

SELECT user_table1.age, user_table1.name,  user_table1.lname, jobs.position, jobs.wage, employers.name employers.phone 
from user_table1 
LEFT JOIN jobs ON jobs.position_id = user_table1.position_id
LEFT JOIN employers ON employers.position_id = jobs.position_id 
LEFT JOIN user_table2 ON jobs.position = user_table2.position
WHERE user_table1.age < 30
user_table2.status = 4
ORDER BY user_table1.age ASC

On other words i need to check where user_table2.status=4 and then combine this records with all records from user_table1 where age less than 30 and then pull fields from jobs and employers for this records.

Try UNIONing your two tables. You'll need to specify your ORDER BY clause outside the UNION. UNION ALL allows duplicates. If you don't want dupes, remove the ALL and use UNION only.

SELECT * FROM 
(
    SELECT u.age, u.name,  u.lname, jobs.position, jobs.wage, employers.name employers.phone 
    from user_table1 as u
    LEFT JOIN jobs ON jobs.position_id = u.position_id
    LEFT JOIN employers ON employers.position_id = jobs.position_id 
    WHERE u.age < 30

    UNION ALL 

    SELECT u.age, user_table1.name,  user_table1.lname, jobs.position, jobs.wage, employers.name employers.phone 
    from user_table2 u  
    LEFT JOIN jobs ON jobs.position_id = u.position_id
    LEFT JOIN employers ON employers.position_id = jobs.position_id 
    WHERE u.age < 30
    AND u.status = 4
) AS UU
ORDER BY  UU.Age ASC

I wasn't clear on your conditions for user_table1 and user_table2 with respect to whether both should get the age 30 filter, and whether table2 should be the only one with the status 4 filter. Modify as you need.