A question about building SQL Query statement

I have a table(forecast), like below

value       element        description
--------------------------------------
26                    HI                 KM
20                    LO                 KM
SW                    D1                 KM
S                     D2                 KM
2                     F1                 KM
3                     F2                 KM
shower     WEATHER1       KM
shower     WEATHER2       KM
35                    HI                 GZ
25                    LO                 GZ
shower     WEATHER1       GZ
downfall   WEATHER2       GZ

I want to get the result below

CITY   WEATHER                      WIND       TEMPERATURE
-------------------------------------------------------------
KM     shower                     SW 2 to S 3     20-26
GZ     shower to downfall      N/A              25-35

(COMMENTS: If weather1=weather2 choose one, else join weather1 and weather2. The same as D1,F1 and D2, F2)

Could it be performed that only use the SQL Query statement?

And how to bulid SQL Query statement, if it can be done ?
JerryleoAsked:
Who is Participating?
 
GibsonMDCommented:
Ok, here is what I have so far...  This only works if you have all the variables populated in the table.  Still a couple of things to work out with the display of the weather1 and weather2 value.

*****

SELECT Distinct Forecast.Descript AS city, Forecast.Value + ' - ' + forecast1.Value AS Temperature, forecast2.value + ' - ' + forecast3.value as Weather,
forecast4.Value + forecast5.value + ' to ' + forecast6.value + forecast7.value as Wind
FROM Forecast, forecast forecast1, forecast forecast2, forecast forecast3, forecast forecast4, forecast forecast5, forecast forecast6, forecast forecast7
WHERE (((forecast1.element = 'hi') AND (Forecast.element = 'lo')) and ((forecast2.element = 'weather1') and (forecast3.element = 'weather2'))) and  (((forecast4.element = 'd1') and (forecast5.element = 'f1')) and ((forecast6.element = 'd2') and (forecast7.element = 'f2')))
and forecast.descript = forecast1.descript and forecast.descript = forecast2.descript and forecast3.descript = forecast.descript and forecast.descript = forecast4.descript and forecast.descript = forecast5.descript and forecast.descript = forecast6.descript and forecast.descript = forecast7.descript
0
 
drittichCommented:
I recommend you change your columns in table forecast to:

DESCRIPTION
WEATHER1
WEATHER2
D1
F1
D2
F2
LO
HI


Then your select becomes extremely simple.  Otherwise you end up having to join the same table to itself nine times.
0
 
nycynikCommented:
what db are you using?  This is not possible in Access.

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
drittichCommented:
I wouldn't say it is not possible, just difficult.
0
 
JerryleoAuthor Commented:
I use SQL Server 7
0
 
GibsonMDCommented:
Ok, here is what I have so far...  This only works if you have all the variables populated in the table.  Still a couple of things to work out with the display of the weather1 and weather2 value.

*****

SELECT Distinct Forecast.Descript AS city, Forecast.Value + ' - ' + forecast1.Value AS Temperature, forecast2.value + ' - ' + forecast3.value as Weather,
forecast4.Value + forecast5.value + ' to ' + forecast6.value + forecast7.value as Wind
FROM Forecast, forecast forecast1, forecast forecast2, forecast forecast3, forecast forecast4, forecast forecast5, forecast forecast6, forecast forecast7
WHERE (((forecast1.element = 'hi') AND (Forecast.element = 'lo')) and ((forecast2.element = 'weather1') and (forecast3.element = 'weather2'))) and  (((forecast4.element = 'd1') and (forecast5.element = 'f1')) and ((forecast6.element = 'd2') and (forecast7.element = 'f2')))
and forecast.descript = forecast1.descript and forecast.descript = forecast2.descript and forecast3.descript = forecast.descript and forecast.descript = forecast4.descript and forecast.descript = forecast5.descript and forecast.descript = forecast6.descript and forecast.descript = forecast7.descript
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.