Still struggling with the concept of sub query/joins

Databases
1

I’m trying to reference the same table twice in a MySQL query to bring back a single row. For whatever reason I’m really struggling to get my head around the sub query/join concept. To ensure I could actually get the data I need I started with this basic MySQL query:

SELECT aCR
            , COUNT(CASE WHEN aSID = 30 THEN 1 ELSE NULL END) AS wtng
            , CASE WHEN aSID = 30 THEN DATE_FORMAT(aAT, '%d %b %H:%i') ELSE NULL END AS wAT
            , COUNT(CASE WHEN DATEDIFF(aC, CURDATE()) <=30 AND aSID != 30 THEN 1 ELSE NULL END) AS rpt
            , CASE WHEN DATEDIFF(aC, CURDATE()) <=30 AND asID != 30 THEN DATE_FORMAT(aAT, '%d %b %H:%i') ELSE NULL END AS rAT
    FROM a
    WHERE aCR = 1111111111 AND aHID = 44 GROUP BY aID

which gave me this

This is the right data but I need it on a single row thus:

Their may be times when there is no match against aCR and therefore no row returned but I need a row returning if wtng is 1, rpt is 1 or both are 1… And this is where I’m really struggling… AGAIN!! :cry:

I’ve tried many subquery combinations but just can’t seem to get it…
Apologies in advance if this is a really dumb question or if I’m missing the obvious - again… What is it I’m missing…?

2

This isn’t quite right (I don’t think…doing it quick and dirty), but should give you a starting point

SELECT aCR
     , CASE WHEN W.aSID = 30 THEN 1 ELSE 0 END AS wtng
     , DATE_FORMAT(aAT, '%d %b %H:%i') AS wAT
     , CASE WHEN R.aSID NOT NULL THEN 1 ELSE NULL END) AS rpt
     , DATE_FORMAT(aAT, '%d %b %H:%i') AS rAT
  FROM a AS A
  LEFT OUTER JOIN a AS W ON A.aCR = W.aCR AND A.aHID = W.aHID AND W.aSID = 30
  LEFT OUTER JOIN a AS R ON A.aCR = R.aCR AND A.aHID = R.aHID AND W.aSID != 30 AND DATEDIFF(R.aC, CURDATE()) <=30
 WHERE A.aCR = 1111111111 
   AND A.aHID = 44 
 GROUP BY aID
3

@DaveMaxwell Thanks for your support. Although your query does take a slightly different approach than those I’ve already tries, it still leaves me with the the original issue but again thanks for taking the time to respond… I’m still in th dark but I’ll keep plugigng away at it.

4

Can you do a data dump of that table? Is there a number you’re always going to have if there’s a record for a specific aCR?

5

SELECT aCR , MAX(wtng) AS wtng , MAX(wAT) AS wAT , MAX(rpt) AS rpt , MAX(rAT) ASrAT FROM ( /* your query here */ ) AS yourquery GROUP BY aCR

6

@DaveMaxwell Thanks for your continued support. There are variables in the form of aCR and aHID but I wanted to keep things straightforward wrt my question… That said I beleive, yet again, that r937 has read my mind… I’m just testing it now to confirm but his reply looks promising.

@r937 - Again I owe you gratitude… I’m still baffled and somewhat midly annoyed that you can do in minutes what it has taken me days to continally get wrong… My thanks go out to you once again… I would never have thought of the approach you took… ever. I seek solace in that fact that it wasn’t another one of those ‘Doh’ moments…

7

Give yourself some credit. At least you weren’t doing a

SELECT * FROM my_table

<?php /* a whole mess of code to pick out the bits you want and not use all the stuff you returned needlessly */ I don't know, but I'd hazard a bet neither Dave nor Rudy were born with an innate knack for complex queries.
8

confirmed

learned everything by actually doing it (been doing SQL since 1987)

experience is a harsh teacher, especially when the lights in the building go dim because you submitted a query that eats up all available machine cycles

1 Like
9

@r937 Ah yes, SELECT * is indeed a cruel and hungry mistess …
I’ve been invloved with mySQL for the grand total (nee SUM()) of 12 months now and the learning curve is steeper than I’d prefer. That said, people like your good self take the edge off of something that is inherently sharp… Every snippet you share adds to my knowledge… appreciate your input and continued support. Thanks again

10

when i started with SQL, the internet was still almost a decade away from catching on… it was really difficult to find help, but we had IT magazines, conferences, and an informal network of i-know-a-guy-who-knows-a-guy…

the first time i got help for something via the internet (in a coldfusion discussion group, around 1998) it really saved my ass at work, and ever since then, i’ve been trying to “pay it forward”

nothing would please me more than to see you pick up the habit of helping others online when you can

11

@r937 Well I can’t see it happening with SQL but thankfully I have a plethora of other skills with which to share. I’ve been ‘paying it forward’ for many years and fully intend on coninuing… Lest we forget - we all had to start somewhere!

12

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.