[MySQL] Help with query

[Redan]

I wonder if OtLand is a good place to ask for SQL support, but my request is related to my ots.
Ok, so here it is:

I've created 3 tables in my database

• players
• houses
• house_auctions

`players` table:

• id (values: 2 and 14)
• name (values: Kapman, Bongo)

`houses` table:

• id (value: 58)
• owner_id (value: 14)

`house_auctions` table:

• id (value: 2)
• house_id (value: 58)
• winner_id (value: 2)

Not every house has an auction record, so lets join this table:

SELECT `houses`.`id`, `ow`.`name` AS `owner_name`
FROM `houses`
LEFT JOIN `house_auctions` AS `auctions` ON `auctions`.`house_id` = `houses`.`id`
LEFT JOIN `players` AS `ow` ON `ow`.`id` = `houses`.`owner_id`
WHERE `houses`.`id` = '58';

The query above works fine but now, I need to get auction winner name. So I simply add another JOIN like this:

SELECT `houses`.`id`, `ow`.`name` AS `owner_name`, `po`.`name` AS `winner_name`
FROM `houses`
LEFT JOIN `house_auctions` AS `auctions` ON `auctions`.`house_id` = `houses`.`id`
LEFT JOIN `players` AS `ow` ON `ow`.`id` = `houses`.`owner_id`
LEFT JOIN `players` AS `po` ON `po`.`id` = `auctions`.`winner_id`
WHERE `houses`.`id` = '58';

In such case, the `winner_name` returns always NULL. What is the reason? Any idea how to make it work properly?

#Edit
I'm 100% sure every value is set correctly so the winner name should appear.

 

[kkk111]

Use SQL -> INNER JOIN

SELECT `p`.`name` AS "Player Name", `h`.`id` AS "House ID", `ha`.`id` AS "Winner ID" from `house_auctions` AS `ha`
INNER JOIN `players` AS `p` ON `p`.`id` = `ha`.`winner_id`
INNER JOIN `houses` AS `h` ON `h`.`owner_id` = `ha`.`house_id`
WHERE `ha`.`winner_id` = 2

 

[Elime]

I simply add another JOIN like this:

SELECT `houses`.`id`, `ow`.`name` AS `owner_name`, `po`.`name` AS `winner_name`
FROM `houses`
LEFT JOIN `house_auctions` AS `auctions` ON `auctions`.`house_id` = `houses`.`id`
LEFT JOIN `players` AS `ow` ON `ow`.`id` = `houses`.`owner_id`
LEFT JOIN `players` AS `po` ON `po`.`id` = `auctions`.`winner_id`
WHERE `houses`.`id` = '58';

In such case, the `winner_name` returns always NULL. What is the reason? Any idea how to make it work properly?

#Edit
I'm 100% sure every value is set correctly so the winner name should appear.

I created the tables and ran your code and it worked as expected. It returned:
id:58, owner_name:Bongo, winner_name:Kapman

 

[Redan]

Its mad - I reviewed the data and found nothing again. The queries I gave are samples I coded for thread purposes. I will rewrite my more complex query and see if it helps then.

#Edit:
Found the issue. Thread may be closed =)