CODEWITHSUNDEEP
sqlinner-join
Isis
Isis

Reputation: 4666

SQL JOIN INNER or OUTER

I have two tables: users: id username clan clans: id clan crest

I trying to do the search by username with output the clan of username.

Query:

SELECT `users`.`id`, `users`.`username`,`users`.`clan`, `clans`.`crest` FROM `users` JOIN `clans` ON `users`.`username` LIKE '%gu%'

Output:

id  username    clan       crest       
2   gusar                  26_760.gif 
3   gusar11     Tong       26_760.gif

If clan is empty crest will be empty.. But ... (

I want following result:

id  username    clan       crest       
2   gusar                  
3   gusar11     Tong       26_760.gif

Upvotes: 0

Views: 215

Answers (4)

Kasaku
Kasaku

Reputation: 2192

You're joining from one table to another using a LIKE, typically there is little reason why you would do this. Instead you likely want that in a WHERE clause, and you should be joining between the table using a foreign key.

You want to use a LEFT OUTER JOIN (or LEFT JOIN for short) to attempt to match all of the rows in the second table, but to show NULL values if there was no match:

SELECT `users`.`id`, `users`.`username`,`users`.`clan`, `clans`.`crest`
FROM `users` 
LEFT JOIN `clans` ON `users`.`clan` = `clans`.`clan`
WHERE `users`.`username` LIKE '%gu%'

Mind you, in your users table, ideally you should hold the clan id and not the clan title. You would then use this query to perform your search:

SELECT `users`.`id`, `users`.`username`,`users`.`clan`, `clans`.`crest`
FROM `users` 
LEFT JOIN `clans` ON `users`.`clanid` = `clans`.`id`
WHERE `users`.`username` LIKE '%gu%'

Upvotes: 2

Michael
Michael

Reputation: 12802

You need to join the table using some condition. You're using an ON where you should use a WHERE. I assume users.clan relates to clans.clans? Try this:

SELECT `users`.`id`, `users`.`username`,`users`.`clan`, `clans`.`crest` FROM `users` LEFT JOIN `clans` ON `users`.`clan` = `clans`.`clan` WHERE `users`.`username` LIKE '%gu%'

Upvotes: 0

Xavjer
Xavjer

Reputation: 9226

Try

SELECT `users`.`id`, `users`.`username`,`users`.`clan`, `clans`.`crest` FROM `users` LEFT JOIN `clans` ON `users`.`username` LIKE '%gu%'

Upvotes: 0

Anthony Grist
Anthony Grist

Reputation: 38345

If you want results from the first table, even when there are NO matches in the second table, you want to use a LEFT OUTER JOIN.

Upvotes: 2

Related Questions