• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 282
  • Last Modified:

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);
0
hdcowboyaz
Asked:
hdcowboyaz
  • 10
  • 4
  • 3
  • +1
1 Solution
 
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
 
Barry62Commented:
I don't think you need the group by clause, just the having clause.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
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
 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 10
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now