Maxim K. Magnes Maxim K. Magnes - 3 days ago 5
PHP Question

SELECT from MYSQL when only one single line present

I have this mysql query:

SELECT a.full_address
FROM games_server AS a
JOIN orders_order AS b
ON b.server_id = a.id
WHERE service_type = 'color'
AND status = 'running' ORDER BY RAND()"


It works perfectly fine and selects a value only if service_type is 'color'.
But what I need is that it would select 'full_address' only when there's just one line with service_type = color and no other lines with different service_type values. Because for one full_address = a there might be multiple lines. E.g. full_address = a with service_type = color and the same full_address = a with service_type = top. In that particular instance, when there are two of them - it mustn't be selected. Only when there is one single line with service_type = color.

I hope any one understood what I said. Sorry if I couldn't make it any clearer.

Answer

try this one (suppose games_server have id):

SELECT a.full_address FROM games_server AS a 
JOIN orders_order AS b ON b.server_id = a.id 
WHERE status = 'running' 
GROUP BY a.id
HAVING group_concat(distinct service_type) = 'color'
ORDER BY RAND()
Comments