SELECT stuff FROM tableX INNER JOIN tableY ON tableX.id = tableZ.column
This is basically what I have. I’d like to add AS postedThread, which would stand for amount of rows in tableY where tableX.id (current pointer, table in example above) in forum is equal to parentForum. There are multiple instances where tableX.id = tableZ.parentForum, and I’d like to grab the amount of rows and add to already ready query. Doable?
Wait, wait, wait… You’re getting information from tableX inner join with tableY but when you tell the database what’s the relationship between tableX and tableY… you use tableZ?
This is just wrong.
As for your question, I don’t have the structure of you database but I don’t see why you wouldn’t be able to do it… if you do it right.
I join data from tableX and tableY. But next to data from tableX, and tableY, I also need to corelate tableX and get amount of… see it this way, I need to fuse:
SELECT "num_rows" FROM tableZ WHERE tableX.forumId = tableZ.parentForum.
(This one gets amount of posts, posted under tableX.forumId)
Next to already done:
SELECT tableX.a, tableX.b, tableX.c, tableY.a, tableY.b, tableY.c INNER JOIN tableY on tableX.id = tableY.authorId.
(This one gets everything else)
SELECT tableX.a
, tableX.b
, tableX.c
, tableY.a
, tableY.b
, tableY.c
, ( SELECT COUNT(*)
FROM tableZ
WHERE tableZ.parentForum = tableX.forumId ) AS num_rows
FROM tableX
INNER
JOIN tableY
ON tableY.authorId = tableX.id
Exactly! One more question though. Can I “always” encapsulate queries in () and give it an alias and use it as result (as long as it’s single cell) ?
Yes, you can 
a single column, and also a single row
in other words, a single value – it’s called a scalar subquery
you can also use a subquery in the FROM clause, having multiple columns, multiple rows – in this case it’s called a derived table
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.