Solved

A question about building SQL Query statement

Posted on 2001-08-15
6
260 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
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.

 

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how to solve "recursive" database tables 2 59
SQL Lag Function DateDiff 2 26
SQL Server Database Inventory 7 30
VMware PVSCSI SQL Server 2016 AlwaysOn 2 37
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

751 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