[Pheonix] User-Furniture [SQL]

[Saia]

Some people found my other thread (http://forum.ragezone.com/f353/uber-...re-sql-742625/) very useful. They asked if I could make one of Pheonix as well. There are some slight database changes between Pheonix and Uber, so here is the revised working query for the Pheonix database.

Search a Username:

Replace the red with the username.

SELECT DISTINCT u.id AS 'User ID', u.username AS 'Username', u.rank AS 'Rank', i.base_item AS 'Furniture ID', (SELECT f.public_name FROM furniture f WHERE f.id = i.base_item LIMIT 0,1) AS 'Furniture Name', (SELECT COUNT(iq2.base_item) FROM items iq2 WHERE iq2.base_item = i.base_item) AS 'Count'
FROM items i
INNER JOIN users u
ON i.user_id = u.id
WHERE i.user_id = (SELECT uq2.id FROM users uq2 WHERE uq2.username = 'USERNAME' LIMIT 0,1)
ORDER BY u.id ASC

Display All:

SELECT DISTINCT u.id AS 'User ID', u.username AS 'Username', u.rank AS 'Rank', i.base_item AS 'Furniture ID', (SELECT f.public_name FROM furniture f WHERE f.id = i.base_item LIMIT 0,1) AS 'Furniture Name', (SELECT COUNT(iq2.base_item) FROM items iq2 WHERE iq2.base_item = i.base_item) AS 'Count'
FROM items i
INNER JOIN users u
ON i.user_id = u.id
ORDER BY u.id ASC

Credits:

Saia (saia.zurchmora@gmail.com).

[MentaL]

[Quackster]

What are your 'slight' changes made? :O

[Jonteh]

Do we really need things like this?

[FAYSALKHAN]

Some people found my other thread (http://forum.ragezone.com/f353/uber-...re-sql-742625/) very useful. They asked if I could make one of Pheonix as well. There are some slight database changes between Pheonix and Uber, so here is the revised working query for the Pheonix database.

Search a Username:

Replace the red with the username.

SELECT DISTINCT u.id AS 'User ID', u.username AS 'Username', u.rank AS 'Rank', i.base_item AS 'Furniture ID', (SELECT f.public_name FROM furniture f WHERE f.id = i.base_item LIMIT 0,1) AS 'Furniture Name', (SELECT COUNT(iq2.base_item) FROM items iq2 WHERE iq2.base_item = i.base_item) AS 'Count'
FROM items i
INNER JOIN users u
ON i.user_id = u.id
WHERE i.user_id = (SELECT uq2.id FROM users uq2 WHERE uq2.username = 'USERNAME' LIMIT 0,1)
ORDER BY u.id ASC

Display All:

SELECT DISTINCT u.id AS 'User ID', u.username AS 'Username', u.rank AS 'Rank', i.base_item AS 'Furniture ID', (SELECT f.public_name FROM furniture f WHERE f.id = i.base_item LIMIT 0,1) AS 'Furniture Name', (SELECT COUNT(iq2.base_item) FROM items iq2 WHERE iq2.base_item = i.base_item) AS 'Count'
FROM items i
INNER JOIN users u
ON i.user_id = u.id
ORDER BY u.id ASC

Credits:

Saia (saia.zurchmora@gmail.com).

Thanks or this, even though I don't need it.

[Subway]

it got delete :/