CODEWITHSUNDEEP
mysqlsql
Caner
Caner

Reputation: 59178

MySQL Subquery(or UNION?)

I have 2 tables with columns like this:

Table1
    testID  username    topic   information     totalTime
Table2
    questionID  testID  question    choices     answers

I would like to select all columns for a test with partiular testID from Table1 and #number of questions with same testID from Table2, so the resulting table should be like this:

testID  username    topic   information     totalTime questionCount

testID & questionID are primary keys.

How can I form this query? Thanks.

Upvotes: 2

Views: 241

Answers (2)

Glenn
Glenn

Reputation: 9150

Maybe I'm missing something here, but aren't we talking about a straight-forward join?

select t1.testID, t1.username, t1.topic, t1.information, t1.totalTime
      ,count(t2.questionID) AS questionCount
from table1 t1
    ,table2 t2
where t1.testID = t2.testID
  and t1.testID = :myInputTestID
group by t1.testID, t1.username, t1.topic, t1.information, t1.totalTime

Upvotes: 1

aF.
aF.

Reputation: 66697

You can do it like this:

Select t1.testID, t1.username, t1.topic, t1.information, t1.totalTime, 
   (select count(questionID) from table2 t2 where t2.testID = t1.testID) as 'questionCount'
from table t1

Upvotes: 2

Related Questions