Avatar of Pennywisdom
Pennywisdom asked on

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.
Microsoft SQL Server

Avatar of undefined
Last Comment
Pennywisdom

8/22/2022 - Mon
billy21

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?
adwiseman

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())

billy21

Sorry.  I didn't notice the <= in the join statement.  I've never seen anybody construct a join with a <= before.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
adwiseman

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.
billy21

<= v_AnalogHistory.Datetime

You can't put that part in the sub query.  It has to be part of the join syntax.
billy21

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())
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
billy21

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())

ASKER
Pennywisdom

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.
ASKER
Pennywisdom

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
Lowfatspread

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Pennywisdom

IT WORKS!

Thanks alot Lowfatspread!