CODEWITHSUNDEEP
sqlif-statementexists
MrCaira
MrCaira

Reputation: 23

SQL IF/EXISTS Statement

i have two tables that could look like this..

table_gb:

    ID:     Author:        Email:             Login:
    1.      John Doe       [email protected]     1
    2.      Jenn Smith     [email protected]     1
    3.      Michael        [email protected]   0


table_user:

    ID:     Username:      Email:
    1.      Jenstar        [email protected]
    2.      Knoxvile       [email protected]
    3.      JohnDoe        [email protected]`


Now what i wanted to do with this is that to make a SQL statement that pulls all fields from the table_gb and IF "Login" == 1 then i want it to go ahead and pull the username with the matching email from the table_user...

Returning something like this..

    ID:     Author:       Username:      Email:               Login:
    1.      John Doe      JohnDoe        [email protected]       1
    2.      Jenn Smith    Jenstar        [email protected]       1
    3.      Michael                      [email protected]     0


Where it basically only gets the username from the table_user but ONLY if login is set to 1 any idea? I have tried everything basically but nothing seemed to work!...

Upvotes: 2

Views: 781

Answers (2)

Taryn
Taryn

Reputation: 247720

You should be able to use a LEFT JOIN between the tables and then use a CASE expression to display the username:

select g.id,
  g.author,
  case when g.login = 1 then u.username else '' end username,
  g.email,
  g.login
from table_gb g
left join table_user u
  on g.email = u.email;

See SQL Fiddle with Demo

Upvotes: 5

Himanshu
Himanshu

Reputation: 32602

Simply use LEFT JOIN

   SELECT g.ID, g.Author, u.Username, g.Email, g.Login
     FROM table_gb g 
LEFT JOIN table_user u
       ON g.Email = u.EMail;

Output:

╔════╦════════════╦══════════╦══════════════════╦═══════╗
║ ID ║   AUTHOR   ║ USERNAME ║      EMAIL       ║ LOGIN ║
╠════╬════════════╬══════════╬══════════════════╬═══════╣
║  1 ║ John Doe   ║ JohnDoe  ║ [email protected]   ║     1 ║
║  2 ║ Jenn Smith ║ Jenstar  ║ [email protected]   ║     1 ║
║  3 ║ Michael    ║ (null)   ║ [email protected] ║     0 ║
╚════╩════════════╩══════════╩══════════════════╩═══════╝

See this SQLFiddle

Upvotes: 3

Related Questions