* Justin French
> I have four (relevant) tables:
>
> category
> partner(pid,pname,etc)
> service(sid,sname,cid)
> sid2pid(sid,pid)
>
>
> This works fine, listing all services available within a category:
>
> SELECT service.sid,service.sname
> FROM service
> WHERE cid='3'
> ORDER BY service.sname ASC
>
> eg:
> Adventure
> Group
> Guided Tours
> Singles
> Skiing
> Under 35's
>
> But I want to get a count of how many times service.sid is found in the
> table pid2sid as well...
>
> eg:
> Adventure (0)
> Group (4)
> Guided Tours (2)
> Singles (45)
> Skiing (0)
> Under 35's (12)
>
>
> I know I need to include a count(), and select from both service and
> pid2sid, but other than that, I'm lost :)
pid2sid or sid2pid...? ;)
You need a JOIN, and because the count could be 0 (zero), you need a LEFT
JOIN:
SELECT service.sid,service.sname,count(*)
FROM service
LEFT JOIN pid2sid ON
pid2sid.sid = service.sid
WHERE cid='3'
GROUP BY service.sid,service.sname
ORDER BY service.sname ASC
... or with the USING clause:
SELECT service.sid,service.sname,count(*)
FROM service
LEFT JOIN pid2sid USING(sid)
WHERE cid='3'
GROUP BY service.sid,service.sname
ORDER BY service.sname ASC
... and in this case you could even use NATURAL JOIN:
SELECT service.sid,service.sname,count(*)
FROM service
NATURAL LEFT JOIN pid2sid
WHERE cid='3'
GROUP BY service.sid,service.sname
ORDER BY service.sname ASC
Note that compared to your original query, I have only added "count(*)" to
the field list, the join, and "GROUP BY " + [all fields in the field list
except group functions, like count()]. All three variants should give the
same result, it's the same join, only with different syntax.
<URL: http://www.mysql.com/doc/en/JOIN.html >
HTH,
--
Roger
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
