CODEWITHSUNDEEP
sqljoin
user1022585
user1022585

Reputation: 13651

sql combine 2 queries with JOIN

I know it's not a good idea to loop SQL queries, so I would like to change these two queries into one.

Not being too great at JOINs, I'm struggling to figure how to merge these two, with it having that DISTINCT in there.

$sql_result2 = mysql_query("SELECT DISTINCT gang FROM turfs WHERE city='$city'", $db);  
while ($rs2 = mysql_fetch_array($sql_result2)) {

    $sql_result3 = mysql_query("SELECT (SELECT COUNT(*) FROM turfs WHERE city='$city' AND gang=g.id) as count, g.id, g.name, g.color FROM gangs g WHERE g.id='$rs2[gang]'", $db); 
    $rs3 = mysql_fetch_array($sql_result3);

Any suggestions?

Upvotes: 2

Views: 127

Answers (1)

John Doyle
John Doyle

Reputation: 7793

Join and group with something like:

select   count(*) as count, G.ID, G.NAME, G.COLOR
from     GANGS G join TURFS T on T.GANG = G.ID
where    CITY = '$city'
group by G.ID, G.NAME, G.COLOR

Upvotes: 2

Related Questions