[RESOLVED] help with query

tbuzz

I have this query set up

SELECT
p1.t1_id,
p1.c1_id,
c1.name
t1.t1_name
FROM p1
INNER JOIN t1 ON(t1.id=p1.t1_id)
INNER JOIN c1 ON(c1.id=p1.c1_id)
WHERE p1.pr_id = $prid
ORDER BY p1.id

I want to count the number of times t1_id appears in another table (r1) where r1.c1_id = p1.c1_id.
I think I need to put it in a subquery but I'm not sure. Any help will be greatly appreciated

bretticus

Well, I must say, your references to your table structure are so vague, I can only guess.

So...my suggestion is to JOIN your r1 table and GROUP BY p1.t1_id. In your SELECT fields portion of your query, include COUNT(r1.t1_id).

This is totally off the top of my head. For example, I'm not even sure that you even need to specify the table alias of p1 in the GROUP BY clause, nor if the table alias for r1 is requisite in the COUNT() function parameter. Hopefully, this will give you a boost in the right direction at least.

tbuzz

Thanks for that. I know the references are a bit vague but I'm translating a legacy db export for the web and the guy who built it gets a bit touchy if I say too much about it when I post on forums, so the names must be changed to protect the innocent (me) from getting a beating... not always helpful when your learning.
I'd got this far but it shows every instance of t1_id in the r1 table and I need only the instances where p1.c1_id=r1.c1_id as well, thats where I think I need to make a sub query. I tried using c1_id to join the p1 and r1 tables, I'm not sure what that does but it doesn't give the right number

bretticus

Without you being able to shed more light, the only advice I can offer is that an INNER JOIN will restrict results to matching tables only. When you need distinct values from a table with a count of the same distinct values, you GROUP BY the column for said values and use COUNT() to get your number. If your number is an integer quantity, you use SUM() to add each integer (probably not what you need.)

Good luck!

tomhath

GROUP it in the subselect and join back to the other tables. You might get NULL if t1_id or c1_id doesn't appear in r1.

Untested, but you get what you pay for: 😉

SELECT
  p1.t1_id,
  p1.c1_id,
  c1.name
  t1.t1_name,
  r1s.t1_rcount
FROM p1
INNER JOIN t1 ON(t1.id=p1.t1_id)
INNER JOIN c1 ON(c1.id=p1.c1_id)
LEFT JOIN (
  SELECT c1_id, t1_id, count(*) as t1_rcount
  FROM r1
  GROUP BY c1_id, t1_id
  ) as r1s ON r1s.c1_id = p1.c1_id and r1s.t1_id = p1.t1_id
WHERE p1.pr_id = $prid
ORDER BY p1.id

tbuzz

excellent, works like a charm in query browser. thanks a lot for that