Conditional JOIN different tables

You could use an outer join:

select *  from USER u  left outer join EMPLOYEE e ON u.user_id = e.user_id  left outer join STUDENT s ON u.user_id = s.user_id where s.user_id is not null or e.user_id is not null

alternatively (if you're not interested in the data from the EMPLOYEE or STUDENT table)

select *  from USER u where exists (select 1 from EMPLOYEE e where e.user_id = u.user_id)    or exists (select 1 from STUDENT s  where s.user_id = u.user_id)

If you want to get all user data together You might have:

SELECT     user_id    ,'Employee' AS SourceFROM     employeeUNION SELECT     user_id    ,'Student' AS SourceFROM     student

http://sqlfiddle.com/#!3/90216/22

Which can also be done with a full join and a CASE statement:

SELECT     ISNULL(e.user_id,s.user_id) AS user_id    ,CASE WHEN e.user_id IS NULL THEN 'Student'         ELSE 'Employee'    END AS SOURCEFROM     employee AS e     FULL JOIN student AS s        ON s.user_id = e.user_id

http://sqlfiddle.com/#!3/90216/29

the latter will combine people who are both students adn employees into one row and call them and employee. compare:

http://sqlfiddle.com/#!3/2aa3e/1andhttp://sqlfiddle.com/#!3/2aa3e/2

where I have made user 1 a student and a employee

Such solution also can help you.

SELECT  S.*, P.*,CASE    WHEN S.ShipmentType = 'import' THEN SP.SupplierName    WHEN S.ShipmentType = 'export' THEN C.CustomerNameEND AS ShipmentDesinationFROM            tblShippments   S INNER JOIN      tblProducts     P   ON S.productId = P.productID  LEFT OUTER JOIN tblCustomers    C   ON S.companyId = C.customerId AND S.ShipmentType = 'export'LEFT OUTER JOIN tblSuppliers    SP  ON S.companyId = SP.supplierId AND S.ShipmentType = 'import'