Solved

SQL Syntax

Posted on 2013-10-29
18
264 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
  • 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
 

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

'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 …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now