0

I want to extract information from three tables which are linked by certain ids to each other. Can you please suggest me how can i do that. I tried but three tables is bit too complex. Here is what i am trying to do:

Here are the three tables and resulting table

So basically from above three tables i want to match USER against respective PID showing the count of only specific SPEC type, e.g ROUND as shown in the example. (It can also happen that same PID is assinged to two different users like PID=1 for user AAA & BBB).

Can you please tell me how to go about it?

Improve this question

3 Answers 3

Reset to default
1 
SELECT 
  t1.USER, 
  t1.PID, 
  COUNT(T3.SPEC) AS CountRound
FROM Table1 t1
  LEFT JOIN Table2 t2 ON t1.PID = t2.PID 
  LEFT JOIN Table3 t3 ON t2.LID = t3.LID AND t3.SPEC = 'ROUND'
GROUP BY
  t1.USER,
  t1.PID
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you so much @Andriy M It works like a treat. You solved my problem. Thanks a lot ;)))
0 
    select user,pid,count(*) from (

    select table1.user,table1.pid,table3.spec 
    from 
      table1,
      table2,
      table3 
    where 
      table1.pid=table2.pid(+) 
    and table2.lid=table3.lie(+) 
    and table3.spec='Round' 

) 
group by user,pid
Improve this answer

5 Comments

what is this (+), i get an error saying my syntax is incorrent
the (+) works as outer join indicator, I use it a lot in Oracle, but let me try to find the equivalent for mysql
Is it something similar to left join??
yes, but in Oracle, you can specify the join type in the WHERE clause or the FROM clause, however in MySQL you may only specify joining inside the FROM clause
It's confusing. Is there anything that sql supports like using output of one SQL query in another query
0

Something to start with?

SELECT 
 T1.USER, 
 T1.PID, 
 IFNULL(COUNT(T3.SPEC), 0) 
FROM 
 Table1 T1 LEFT JOIN Table2 T2 
  ON T1.PID = T2.PID 
 JOIN Table3 T3 
  ON T2.LID = T3.LID 
WHERE T3.SPEC = 'ROUND'
Improve this answer

6 Comments

This might not show the first row of USER AAA because you have not done outer join, and thus AAA row will be met with a null value from table 2, and thus removed from the result
It only returns single user entry? i don't know why?
@Johnydep: Because @josh.trow forgot to add GROUP BY.
Apart from the missing GROUP BY there's another issue: this solution won't show users with zero counts.
@Andriy M not really, the result is only a single entry. Although i want to retrieve all the users sorted by user column.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.