CODEWITHSUNDEEP
mysqlsql
ASD
ASD

Reputation: 4967

mysql - combining 2 tables with different condition

Here i have 2 tables user and userStore

user Table
╔════╦══════╦════════╦═══════════╗
║ ID ║ NAME ║  ROLE  ║ STORECODE ║
╠════╬══════╬════════╬═══════════╣
║  1 ║ A    ║ Admin  ║           ║
║  2 ║ B    ║ Store  ║         1 ║
║  3 ║ C    ║ Store  ║           ║
║  4 ║ D    ║ Client ║           ║
║  5 ║ E    ║ Staff  ║           ║
╚════╩══════╩════════╩═══════════╝

userStore Table
╔════╦══════════╗
║ ID ║ CATEGORY ║
╠════╬══════════╣
║  1 ║ X        ║
║  2 ║ X        ║
╚════╩══════════╝

Output
╔════╦══════╦════════╦═══════════╦══════════╗
║ ID ║ NAME ║  ROLE  ║ STORECODE ║ CATEGORY ║
╠════╬══════╬════════╬═══════════╬══════════╣
║  1 ║ A    ║ Admin  ║           ║          ║
║  2 ║ B    ║ Store  ║         1 ║ X        ║
║  4 ║ D    ║ Client ║           ║          ║
║  5 ║ E    ║ Staff  ║           ║          ║
╚════╩══════╩════════╩═══════════╩══════════╝

I want to fetch all the rows from user table with the role other than store. And wanted to include the store role only if it have match in the userstore table. In the output you can see that the id=3 is not available since it doesn't have match from user store.

Upvotes: 1

Views: 66

Answers (2)

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

SELECT u.id, u.name, u.role, u.StoreCode, IFNULL(us.id, '') id, IFNULL(us.Category, '') Category
FROM USER u 
LEFT JOIN userStore us ON u.StoreCode = us.id 
WHERE IF(LOWER(u.role) = 'store', u.StoreCode IS NOT NULL AND u.StoreCode != '', TRUE);

Upvotes: 1

John Woo
John Woo

Reputation: 263813

This should be straight forward, use LEFT JOIN

SELECT  a.*, b.*
FROM    user a
        LEFT JOIN userStore b
            ON a.StoreCode = b.ID
WHERE   (a.role <> 'Store') OR 
        (a.role = 'Store' AND NOT b.ID IS NULL )

Other Source

Upvotes: 2

Related Questions