?
Solved

A question about building SQL Query statement

Posted on 2001-08-15
6
Medium Priority
?
285 Views
Last Modified: 2006-11-17
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 ?
0
Comment
Question by:Jerryleo
6 Comments
 
LVL 8

Expert Comment

by:drittich
ID: 6391270
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
 
LVL 2

Expert Comment

by:nycynik
ID: 6397296
what db are you using?  This is not possible in Access.

0
 
LVL 8

Expert Comment

by:drittich
ID: 6399640
I wouldn't say it is not possible, just difficult.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:Jerryleo
ID: 6402840
I use SQL Server 7
0
 

Expert Comment

by:GibsonMD
ID: 6444945
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
 

Accepted Solution

by:
GibsonMD earned 300 total points
ID: 6444996
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

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

609 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