SQL Syntax

Data

POPU               POEST
139384             24.64
0                               0
24519                25.24
0                                0
590839              24.01
0                                0
0                                0

I want to return the POEST value where POPU is MAX. The below query returns (NULL). It should return 24.01


SELECT c.POEST
FROM ccode c
INNER JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
INNER JOIN job j ON c.JOB_ID = j.JOB_ID
WHERE j.JOB_ID = 7398
GROUP BY j.JOB_ID
HAVING MAX(c.POPU);
hdcowboyazAsked:
Who is Participating?
 
leason2Commented:
maybe this then...

select a. POPU
from (

SELECT MAX(c.POPU) POPU, c.POEST
FROM ccode c
JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
JOIN job j ON j.JOB_ID = c.JOB_ID
WHERE j.JOB_ID = 7398
AND s.SCTYPE IN (1,2,3,4,5,6,7)
AND c.POPU > 0
GROUP BY j.JOB_ID, s.SCTYPE

)a
0
 
Barry62Commented:
It would help to see the relevant data from all of the tables
0
 
hdcowboyazAuthor Commented:
This query produces the Data above. I've also attahced the ERD

SELECT c.POPU, c.POEST
FROM ccode c
INNER JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
INNER JOIN job j ON c.JOB_ID = j.JOB_ID
WHERE j.JOB_ID = 7398
AND s.SCTYPE = 1
AND (j.DELETED != 'Y'OR j.DELETED IS NULL)
AND (c.DELETED != 'Y'OR c.DELETED IS NULL)
AND (s.DELETED != 'Y'OR s.DELETED IS NULL);
ERD.jpg
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Barry62Commented:
I don't think you need the group by clause, just the having clause.
0
 
hdcowboyazAuthor Commented:
No.  I still get (NULL)
0
 
hdcowboyazAuthor Commented:
THis works but I need a subquery to only pull out the POEST

SELECT MAX(c.POPU), c.POEST, s.SCTYPE
FROM ccode c
JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
JOIN job j ON j.JOB_ID = c.JOB_ID
WHERE j.JOB_ID = 7398
AND s.SCTYPE IN (1,2,3,4,5,6,7)
AND c.POPU > 0
GROUP BY j.JOB_ID, s.SCTYPE;
0
 
leason22Commented:
Try this...
if you're still getting nulls, possibly switch to LEFT OUTER JOIN

SELECT c.POEST
FROM ccode c
INNER JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
INNER JOIN job j ON c.JOB_ID = j.JOB_ID
WHERE j.JOB_ID = 7398
AND c.POPU IN (SELECT MAX(c.POPU) FROM FROM ccode c);
0
 
hdcowboyazAuthor Commented:
This works but I need a subquery to only pull out the POEST

SELECT MAX(c.POPU), c.POEST, s.SCTYPE
FROM ccode c
JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
JOIN job j ON j.JOB_ID = c.JOB_ID
WHERE j.JOB_ID = 7398
AND s.SCTYPE IN (1,2,3,4,5,6,7)
AND c.POPU > 0
GROUP BY j.JOB_ID, s.SCTYPE;
0
 
hdcowboyazAuthor Commented:
leason22

Query : SELECT c.POEST FROM ccode c inner JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID inner JOIN job j ON c.JOB_ID = j.JOB_ID WHERE j.JOB...
Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM ccode c) LIMIT 0, 1000' at line 6
0
 
Barry62Commented:
There is an extra FROM in the subquery
0
 
hdcowboyazAuthor Commented:
Neither INNER or LEFT OUTER gives any results.
0
 
leason2Commented:
You could do this.
SELECT a.POEST
FROM
(
SELECT MAX(c.POPU), c.POEST, s.SCTYPE
FROM ccode c
JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
JOIN job j ON j.JOB_ID = c.JOB_ID
WHERE j.JOB_ID = 7398
AND s.SCTYPE IN (1,2,3,4,5,6,7)
AND c.POPU > 0
GROUP BY j.JOB_ID, s.SCTYPE;
)a
0
 
leason2Commented:
or...

SELECT c.POEST, s.SCTYPE
FROM ccode c
JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
JOIN job j ON j.JOB_ID = c.JOB_ID

WHERE c.POPU in (
 SELECT MAX(c.POPU)
 FROM ccode c
)
AND j.JOB_ID = 7398
AND s.SCTYPE IN (1,2,3,4,5,6,7)
AND c.POPU > 0;
0
 
hdcowboyazAuthor Commented:
No one seems to be reading what I've wrote. Should I open another ticket? Do not read anything above as it is no longer applicable....

This querry works....

SELECT MAX(c.POPU) POPU, c.POEST
FROM ccode c
JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
JOIN job j ON j.JOB_ID = c.JOB_ID
WHERE j.JOB_ID = 7398
AND s.SCTYPE IN (1,2,3,4,5,6,7)
AND c.POPU > 0
GROUP BY j.JOB_ID, s.SCTYPE;

It  returns these values
POPU      POEST   SCTYPE
590839     24.64       1
814315     25.09       3

I need a subquery to only pull out the POEST
24.64 & 25.09
0
 
hdcowboyazAuthor Commented:
leason

Neither queries returned anything. Please see above
0
 
leason2Commented:
When you say, "only pull out", are you wanting to return ONLY the column POEST with those two rows of 24.64 & 25.09 ?
Then, that IS NOT a subquery.
It is an OUTER query.
You can WRAP this existing query that works, with another select calling only the columns inside that you want.
Think of it as a TEMP table,that you can now query.
0
 
hdcowboyazAuthor Commented:
Yes, I want to return only those values. Yes I was talking about wrapping it, so the original query I was thinking was a sub query but whatever it's called is fine.
0
 
hdcowboyazAuthor Commented:
Right idea...wrong column :)

SELECT a. POEST
FROM (SELECT MAX(c.POPU) POPU, c.POEST
FROM ccode c
JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
JOIN job j ON j.JOB_ID = c.JOB_ID
WHERE j.JOB_ID = 7398
AND s.SCTYPE IN (1,2,3,4,5,6,7)
AND c.POPU > 0
GROUP BY j.JOB_ID, s.SCTYPE)a;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.