?
Solved

SQL Syntax

Posted on 2013-10-29
18
Medium Priority
?
270 Views
Last Modified: 2013-10-29
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
Comment
Question by:hdcowboyaz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 4
  • 3
  • +1
18 Comments
 
LVL 8

Expert Comment

by:Barry62
ID: 39608707
It would help to see the relevant data from all of the tables
0
 

Author Comment

by:hdcowboyaz
ID: 39608811
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
 
LVL 8

Expert Comment

by:Barry62
ID: 39608881
I don't think you need the group by clause, just the having clause.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:hdcowboyaz
ID: 39608947
No.  I still get (NULL)
0
 

Author Comment

by:hdcowboyaz
ID: 39609357
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
 
LVL 1

Expert Comment

by:leason22
ID: 39609413
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
 

Author Comment

by:hdcowboyaz
ID: 39609460
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
 

Author Comment

by:hdcowboyaz
ID: 39609478
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
 
LVL 8

Expert Comment

by:Barry62
ID: 39609553
There is an extra FROM in the subquery
0
 

Author Comment

by:hdcowboyaz
ID: 39609583
Neither INNER or LEFT OUTER gives any results.
0
 

Expert Comment

by:leason2
ID: 39609609
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
 

Expert Comment

by:leason2
ID: 39609628
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
 

Author Comment

by:hdcowboyaz
ID: 39609682
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
 

Author Comment

by:hdcowboyaz
ID: 39609690
leason

Neither queries returned anything. Please see above
0
 

Expert Comment

by:leason2
ID: 39609723
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
 

Author Comment

by:hdcowboyaz
ID: 39609764
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
 

Accepted Solution

by:
leason2 earned 800 total points
ID: 39609772
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
 

Author Closing Comment

by:hdcowboyaz
ID: 39609815
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question