4 Table query 1

[colin81]

Hi all

I need help with a query involving 4 tables. The sql needs to be able to be executed from an asp page. I have the following tables :

tblPollutant

pol_code pol_desc
-------- --------
1 co2
2 sulphur

tblPolLevels

lev_code lev_desc
-------- --------
1 Low
2 Med
3 High

tblOverallPolRating

lev_code (fk tblPolLevels) pol_code(fk tblPollutant) val
------------------------- ------------------------- ----
1 1 10
2 1 15
3 1 20
1 2 15
2 2 20
3 2 25

tblPOL_POLL_AMT

pol_code pol_value
-------- ----------
1 11
2 16


I need to be able to execute a query to get the following data for each record held in the tblPol_POLL_AMT table

pol_value, pol_desc (from tblPollutant), lev_desc (from tblPolLevels)

The pol_value in tblPOL_POLL_AMT needs to be checked against the val field in tblOverallPolRating and assigned the lev_code held in tblPolLevels if it is equal to or lower val and not higher.

Hope this explained well enough it is quite difficult to explain.

Many Thanks for reading

Colin

 

[PHV]

Something like this ?
SELECT A.pol_value, P.pol_desc, L.lev_desc
FROM tblPOL_POLL_AMT A, tblPollutant P, tblOverallPolRating R, tblPolLevels L
WHERE A.pol_code=P.pol_code AND A.pol_code=R.pol_code AND A.pol_value<=R.val AND R.lev_code=L.lev_code

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[colin81]

Hi PHV Thanks for reply

The query you submitted to me works in part in the way that it will match a lev_code with a record in the tblPOL_POLL_AMT table but it creates three entries for each record like below

pol_value pol_desc lev_desc
--------- -------- --------
2 sulphur med
2 sulphur high

I think this is because the query is comparing a.pol_value with something that is lower than r.val so if a.pol_value is 1 it will be lower than all other values held in the tblOverallPolRating table and display the above criteria. I would need the following output as :

pol_value pol_desc lev_desc
--------- -------- --------
2 sulphur high

Because the pol_value for pol_code 2 is 16 in tblPOL_POLL_AMT which would mean it is a high lev_code

Once again thanks for your previous reply and apologies for asking this question, unfortunately I am not very good at sql yet and have been dumped with this task .

Thanks
Colin

 

[PHV]

Replace this:
AND A.pol_value<=R.val
By this:
AND R.val=(SELECT Min(O.val) FROM tblOverallPolRating O WHERE O.pol_code=A.pol_code AND O.val>=A.pol_value)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[colin81]

Thanks for all your help thats worked !

Colin