Select in Inner Join problem

Consider the following select:

SELECT            *
FROM            v_AnalogHistory
INNER JOIN      (SELECT TOP 1 NaedChange.NaedID,NaedChange.Datetime,RPMSetupProd.dbo.Naed.Name
            FROM RPMViewProd.dbo.NaedChange AS NaedChange
            LEFT OUTER JOIN RPMSetupProd.dbo.Naed ON RPMSetupProd.dbo.Naed.ID = NaedChange.NaedID
            WHERE NaedChange.Line = 'Ligne 3' AND NaedChange.Datetime <= '2004/03/26 07:45'
            ORDER BY NaedChange.Datetime DESC) AS Naed ON Naed.Datetime <= v_AnalogHistory.Datetime
WHERE             v_AnalogHistory.Tagname = 'L1_Sealer_TEMP_F'
            AND v_AnalogHistory.Datetime >= DateAdd(n,-15,getDate())


In the WHERE section of the "SELECT TOP 1" I need to change NaedChange.Datetime <= '2004/03/26 07:45' to NaedChange.Datetime <= v_AnalogHistory.Datetime but if make taht change it gives me the error : "The column prefix 'v_AnalogHistory' does not match with a table name or alias name used in the query."

How can use v_AnalogHistory.Datetime inside my other select?

I tried using an alias for v_AnalogHistory and it did the same error.
PennywisdomAsked:
Who is Participating?
 
LowfatspreadCommented:
then i believe this is what you want

based on you description of the 3 tables....


Select V.Val as Value
      ,N.*
  From (  
Select Va.[Value] as Val,Max(NC.[DateTime]) as MaxNCtime
  from v_analogHistory as Va
 Inner Join NaedChange as Nc  
    on Va.[DateTime] >= NC.[DateTime]
 group by Va.[Value]
       ) as V
 Inner Join NaedChange as NC
    on V.MaxNCTime = NC.[DateTime]
 Inner Join Naed as N
    on NC.NaedID=N.NaedID
--Where  N.ID = 1 or N.LampType = 'Regulier'
 Order By 1

0
 
billy21Commented:
Do you realise your sub query (if it worked) would return only one record?  Being an inner join then your entire query would only return 1 record.  Is this the desired result?
0
 
adwisemanCommented:
Try something like this.  I'm not understanding your query, but this may help you understand what I mean.

SELECT          *
FROM          v_AnalogHistory
INNER JOIN     (SELECT NaedChange.NaedID,RPMSetupProd.dbo.Naed.Name,MAX(NaedChange.Datetime)
          FROM RPMViewProd.dbo.NaedChange AS NaedChange
          LEFT OUTER JOIN RPMSetupProd.dbo.Naed ON RPMSetupProd.dbo.Naed.ID = NaedChange.NaedID
          INNER JOIN v_AnalogHistory a ON Naed.Datetime <= a.Datetime
          WHERE NaedChange.Line = 'Ligne 3' GROUP BY NaedChange.NaedID,RPMSetupProd.dbo.Naed.Name) AS Naed ON Naed.Datetime <= v_AnalogHistory.Datetime
WHERE           v_AnalogHistory.Tagname = 'L1_Sealer_TEMP_F'
          AND v_AnalogHistory.Datetime >= DateAdd(n,-15,getDate())

0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
billy21Commented:
Sorry.  I didn't notice the <= in the join statement.  I've never seen anybody construct a join with a <= before.
0
 
adwisemanCommented:
My subquery that I used, may give you the results that you where looking for.  Using the group by and max(date), you may be able to do this without using a sub query.
0
 
billy21Commented:
<= v_AnalogHistory.Datetime

You can't put that part in the sub query.  It has to be part of the join syntax.
0
 
billy21Commented:
SELECT *
FROM v_AnalogHistory
INNER JOIN     (  SELECT NaedChange.NaedID,NaedChange.Datetime,RPMSetupProd.dbo.Naed.Name
                FROM RPMViewProd.dbo.NaedChange AS NaedChange
                LEFT OUTER JOIN RPMSetupProd.dbo.Naed ON RPMSetupProd.dbo.Naed.ID = NaedChange.NaedID
                WHERE NaedChange.Line = 'Ligne 3'
                ORDER BY NaedChange.Datetime DESC) AS Naed ON Naed.Datetime <= v_AnalogHistory.Datetime And <= v_AnalogHistory.Datetime
WHERE           v_AnalogHistory.Tagname = 'L1_Sealer_TEMP_F'
          AND v_AnalogHistory.Datetime >= DateAdd(n,-15,getDate())
0
 
billy21Commented:
Sorry again.  That should have read:

SELECT *
FROM v_AnalogHistory
INNER JOIN     (  SELECT NaedChange.NaedID,NaedChange.Datetime,RPMSetupProd.dbo.Naed.Name
                FROM RPMViewProd.dbo.NaedChange AS NaedChange
                LEFT OUTER JOIN RPMSetupProd.dbo.Naed ON RPMSetupProd.dbo.Naed.ID = NaedChange.NaedID
                WHERE NaedChange.Line = 'Ligne 3'
                ORDER BY NaedChange.Datetime DESC) AS Naed ON Naed.Datetime <= v_AnalogHistory.Datetime And NaedChange.Datetime <= v_AnalogHistory.Datetime
WHERE           v_AnalogHistory.Tagname = 'L1_Sealer_TEMP_F'
          AND v_AnalogHistory.Datetime >= DateAdd(n,-15,getDate())

0
 
PennywisdomAuthor Commented:
I am now studying and trying to make your suggested queries work, this may take a while but don't worry I will come back with the results.
0
 
PennywisdomAuthor Commented:
I'll try to explain the purpose of my query to clear things up, this will be complicated to explain in text so please bare with me.

I have a view called v_AnalogHistory witch returns something that looks like this:

Datetime            Value
----------------      -----
2004/03/26 07:45      987
2004/03/26 07:46      800
2004/03/26 07:53      958
2004/03/26 08:21      902
2004/03/26 08:34      905

I a have a table called NaedChange wich looks like this:

Datetime            NaedID
----------------      -----
2004/03/23 14:23      1
2004/03/26 08:00      2

I have a table called Naed that looks like this:

ID      Name      LampType
--      -----      -----------
1      21345      Regulier
2      21346      Suntan Full


I want to do a query that would return this:

Value      Naed.ID            Naed.Name      Naed.LampType
-----      -------             ---------            -------------
987      1            21345            Regulier
800      1            21345            Regulier
958      1            21345            Regulier
902      2            21346            Suntan Full
905      2            21346            Suntan Full


In ohter words I want all the values each associated with the naed that was active at that time, the last one entered before the date of the value.
After that I want to be able to add conditions like Naed.ID = 1 or Naed.LampType = 'Regulier' so that only the values with a correct naed or correct lampType would be return by the query.

This is what my query that I posted in my question was suppose to do but I can't find a syntax to fit my logic.
0
 
PennywisdomAuthor Commented:
IT WORKS!

Thanks alot Lowfatspread!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.