Solved

SQL Syntax

Posted on 2013-10-29
18
265 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

911 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

22 Experts available now in Live!

Get 1:1 Help Now