SQL help: sub-queries from same table

Dev Shed Forums Sponsor:

February 16th, 2012, 09:34 AM

jrobb

Registered User

Join Date: Feb 2012

Posts: 2

Time spent in forums: 27 m 5 sec
Reputation Power: 0

SQL help: sub-queries from same table

Hi,
I used to be a member on this site, but could not remember my username. oh well.

I've written these queries a long time ago, and now I am trying to do something new for a different calculation.

My database is setup like this:

Code:

TI
TIID,SN,Date,Temp

Data
TIID,Freq,Voltage1,Voltage2,Gain, ...

The queries I have setup that run currently just go through and select some data based on defined conditions:

ie.

Code:

SELECT ti.TIID, TI.SN, Data.Freq, Data.Voltage1, Data.Voltage2, Data.Gain
FROM TI,Data
WHERE
TI.TIID= 2 AND
TI.TIID=Data.TIID and
Data.Freq= 6000 and
Data.Voltage1 = 10 and
Data.Voltage2 = 15;

That could probably be done quite a bit better as-is (and I welcome suggestions).

What I am trying to do now would be the equivalent of having 2 sets of conditions, and subtracting one from another.

maybe like this, but my SQL skills are very weak and out of practice:

Code:

SELECT (a.Gain - b.Gain) as newGain, a.TIID, a.SN, a.Freq, a.Voltage1, a.Voltage2 FROM
(
//copy in first query
  SELECT ti.TIID, TI.SN, Data.Freq, Data.Voltage1, Data.Voltage2, Data.Gain
  FROM TI,Data
  WHERE
  TI.TIID= 2 AND
  TI.TIID=Data.TIID and
  Data.Freq= 6000 and
  Data.Voltage1 = 10 and
  Data.Voltage2 = 15
) AS A JOIN
(
//same again as first query, with altered conditions
  SELECT ti.TIID, TI.SN, Data.Freq, Data.Voltage1, Data.Voltage2, Data.Gain
  FROM TI,Data
  WHERE
  TI.TIID= 2 AND
  TI.TIID=Data.TIID and
  Data.Freq= 6000 and
  Data.Voltage1 = 10 and
  Data.Voltage2 = 0
) AS B ON
(
//stuff that's the same
  A.TIID= B.TIID and 
  A.SN = B.SN
  A.Temp = B.Temp
  A.Freq = B.Freq and
  A.Voltage1 = B.Voltage1
)
;

I know that's probably terrible, but I'm struggling.
I need to return the newGain field, which is a subtraction of the gain fields from two separate sets of conditions. Only one or two fields will likely be different to differentiate them.

Can anyone offer assistance or suggestions?
Am I even on the right track here?

Thanks