Solved

A question about building SQL Query statement

Posted on 2001-08-15
6
268 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

624 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