Solved

A question about building SQL Query statement

Posted on 2001-08-15
6
250 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

948 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

21 Experts available now in Live!

Get 1:1 Help Now