#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    2
    Rep Power
    0

    Thumbs down 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
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    2
    Rep Power
    0
    nevermind, got it figured out. join on one field, and i didn't even have the right field in there.

    appears to be working now.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    3
    Rep Power
    0
    no comment

IMN logo majestic logo threadwatch logo seochat tools logo