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 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%' 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.
Try
SELECT `users`.`id`, `users`.`username`,`users`.`clan`, `clans`.`crest` FROM `users` LEFT JOIN `clans` ON `users`.`username` LIKE '%gu%' 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%' 