Dom
Member
Registered: 13th Sep 03
User status: Offline
|
I'm trying to whittle down the number of queries i'm using and my current implementation uses arrays and is pretty messy. But i'm having a complete brain-fart over how i can do this within one query.
Essentially i have a table called 'items' -
code:
+---------+-------------+-----------+
| item_id | item_name | item_type |
+---------+-------------+-----------+
| 0 | hammer | tool |
| 1 | toolbox | storage |
| 2 | screwdriver | tool |
| 3 | spanner | tool |
| 4 | shelves | storage |
+---------+-------------+-----------+
and a relationship table 'user_items' (which user has what items) -
code:
+---------+-----------+
| user_id | user_item |
+---------+-----------+
| 0 | 1 |
| 14 | 4 |
| 17 | 2 |
| 22 | 1 |
| 22 | 3 |
| 31 | 3 |
| 31 | 0 |
| 31 | 2 |
+---------+-----------+
And what i'm after is the complete output of the 'items' table but a boolean of whether or not a specific user owns the item; so for user 22 -
code:
+---------+-------------+-----------+---------------+
| item_id | item_name | item_type | user_has_item |
+---------+-------------+-----------+---------------+
| 0 | hammer | tool | false |
| 1 | toolbox | storage | true |
| 2 | screwdriver | tool | false |
| 3 | spanner | tool | true |
| 4 | shelves | storage | false |
+---------+-------------+-----------+---------------+
So a query alone the lines of -
code:
SELECT i.*, IF((SELECT `user_items`.`user_id` FROM `user_items` WHERE `user_items`.`user_id` = '22' AND `user_items`.`user_item` = i.`item_id`) IS NULL, false, true) AS `user_has_item` FROM `items` i WHERE 1
But i believe you can't have SELECT statements as part of IF conditions and i think i'm barking up the wrong tree anyway 
So, has anyone got any ideas how i can accomplish this?
[Edited on 28-05-2013 by Dom]
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
Nevermind, it appears you can have SELECT statements for IF conditions 
|
James
Member
Registered: 1st Jun 02
Location: Surrey
User status: Offline
|
Is this MySQL?
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
quote:
Originally posted by James
Is this MySQL?
Yup; will edit title to stop confusing 
|
James
Member
Registered: 1st Jun 02
Location: Surrey
User status: Offline
|
That's a really inefficient way of doing it. You would be better off using an outer join and then checking for NULL values.
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
Something like below (think this is correct)?
code:
SELECT i.*, IF(u.`user_item` IS NULL, false, true) AS `user_has_item` FROM `items` i LEFT OUTER JOIN `user_items` u ON u.`user_item` = i.`item_id` AND u.`user_id` = '22' WHERE 1
[Edited on 28-05-2013 by Dom]
|
James
Member
Registered: 1st Jun 02
Location: Surrey
User status: Offline
|
Yep that's it.
|
noshua
Member
Registered: 19th Nov 08
User status: Offline
|
Notepad++, if you don't already have it
|
