Trouble with Join and Correlated Subquery

Databases
1

I’m not sure if this is possible but I’m trying to right a compound query as seen below. I’m running in trouble in trying to include the second select in the from clause.

SELECT * FROM Tickets t JOIN Computers ON t.cid=Computers.cid JOIN Sites ON Computers.sid = Sites.sid, (SELECT COUNT(a.tid) FROM Actions a WHERE a.tid = t.tid GROUP BY a.tid) As ActionCount WHERE t.pid = 15 ORDER BY t.submitDate DESC;

In this example I get an error saying “Unknown column ‘t.tid’ in ‘where clause’”.

I’m not quite sure how to handle the various alias when dealing with multiple joins and a correlated sub query.

Thanks for any suggestions you might offer.

2

Looks like your intention is to use t as an alias for the tickets table, you just forgot to specify that in the query:

SELECT * FROM Tickets AS t JOIN Computers ON t.cid=Computers.cid JOIN Sites ON Computers.sid = Sites.sid, (SELECT COUNT(a.tid) FROM Actions a WHERE a.tid = t.tid GROUP BY a.tid) As ActionCount WHERE t.pid = 15 ORDER BY t.submitDate DESC

The syntax for defining table aliases is:

table_name AS alias_name

3 
SELECT something
     , anything
     , just_not_the_dreaded_evil_select_star  
  FROM Tickets AS t 
INNER
  JOIN Computers 
    ON Computers.cid = t.cid
INNER 
  JOIN Sites 
    ON Sites.sid = Computers.sid
[COLOR="Blue"]INNER
  JOIN ( SELECT tid
              , COUNT(*) AS ActionCount
           FROM Actions 
         GROUP
             BY tid ) AS aaa
    ON aaa.tid = t.tid[/COLOR]
 WHERE t.pid = 15 
ORDER 
    BY t.submitDate DESC

you asked for suggestions, so my main suggestion is to learn how not to write a complex query on one humoungously long line, there’s no way you will ever be able to understand it

4

Just a little thing to note bar338, the dreaded, evil SELECT * does have a place initially whilst you are making the joins, to verify that the joins are working as expected but once you have confirmed that the joins are ok, you should replace it by specifying the fields that you want only.


SELECT
     table_name.field_name AS field_alias
     ....the other fields being selected

The use of an alias makes it easier when referring to that field in other clauses in the query.

5

this is ~awesome~ :slight_smile:

not always – you can reference the alias only in the GROUP BY, HAVING, and ORDER BY clauses

not being able to reference a column alias in the WHERE clause is a major nuisance

6

Thanks for your help everyone. I eventually got it working and hopefully using proper technique :cool:

                SELECT t.tid,submitDate,closedDate,tDesc,model,OS,RAM,name,location,count
FROM Tickets AS t
JOIN Computers AS c
    ON t.cid=c.cid
JOIN Sites AS s
    ON c.sid = s.sid
JOIN (SELECT COUNT(tid) AS count, tid 
             FROM Actions 
             GROUP BY tid) 
             AS ac
    ON ac.tid=t.tid
WHERE t.pid = ? 
ORDER
    BY t.submitDate DESC;
7

I’ve actually realized one problem which I don’t quite understand. The problem occurs with the third join that has a sub query:

JOIN (SELECT COUNT(tid) AS count, tid 
             FROM Actions 
             GROUP BY tid) 
             AS ac
    ON ac.tid=t.tid

This select looks to see if there are any actions associated with the ticket. Sometimes there are no actions with a ticket. In this instance it should simply return a count of 0 to list with the row. However, what is actually happening is that the row as a whole is not returned if there are 0 actions associated with it.
I’m assuming this has something to do with the nature of joins. However, I’m kind of new to the concept of joins so I may be missing something.

8

stick the words LEFT OUTER in front of the JOIN to the ac subquery :slight_smile:

and in the SELECT clause for the outer query, change count to COALESCE(ac.count,0) AS count

9

This is what I did:

LEFT OUTER JOIN (SELECT COALESCE(ac.count,0) AS count, tid FROM Actions GROUP BY tid) AS ac

However this part does not make logical sense because ac.count hasn’t been created at the point it is referenced in coalesce.

 COALESCE(ac.count,0) AS count

I looked up the coalesce function and think I understand its purpose. I tried replacing the above with.

 COALESCE(COUNT(tid),0) AS count

However, this does not work either.

The LEFT OUTER JOIN fixed it so that it correctly displays all rows which have a count of 0 actions. However, in this instance when I try to display count nothing is displayed rather than 0. In the above use of COALESCE if the count is null it should display the second value. However, this does not seem to be working.

10

the COALESCE should be on the outer query –

SELECT t.tid
     , submitDate
     , closedDate
     , tDesc
     , model
     , OS
     , RAM
     , name
     , location
     , [COLOR="Blue"]COALESCE(ac.count,0) AS count[/COLOR]
  FROM Tickets AS t
INNER
  JOIN Computers AS c
    ON t.cid=c.cid
INNER
  JOIN Sites AS s
    ON c.sid = s.sid
LEFT OUTER
  JOIN ( SELECT COUNT(tid) AS count
              , tid 
           FROM Actions 
        GROUP 
             BY tid ) AS ac
    ON ac.tid=t.tid
 WHERE t.pid = ? 
ORDER
    BY t.submitDate DESC;
11

Oh ok… That makes complete sense now. Thanks for your help, I appreciate it.