SQL IF/EXISTS Statement

Question

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!...

Taryn · Accepted Answer · 2013-05-15 10:54:29Z

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

Himanshu · Accepted Answer · 2013-05-15 10:54:17Z

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

Thanks alot, i don't know why i havent thought of this. However you are right, this is the simplest method :)

Collectives™ on Stack Overflow

SQL IF/EXISTS Statement

2 Answers 2

Comments

See this SQLFiddle

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related