SELECT
racks.room_id,racks.title,racks.rack_id,c.cnt
FROM
racks
INNER JOIN
(SELECT room_id,count(room_id) as cnt FROM racks GROUP By room_id) c
ON
racks.room_id = c.room_id
Yes, it works if I just use 2 quieries.
I wanted the racks to be spit out in groups of rooms, I guess thats what the ORDER BY room_id clause did
those names of the racks, the rooms are only assigned by room_id which is 1-6)
Sounds like you’re wanting to GROUP_CONCAT the rack names; in which case your query is a single, aggregated, query on the rooms.
Change of query focus - or in english, a change of the subject of the sentence.
Your current query is “Tell me about the racks, but throw in the count of racks in it’s room on each row.”
What you’re suggesting sounds like “Tell me about the rooms, including the racks inside them.”