We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Select in Inner Join problem

Pennywisdom
Pennywisdom asked
on
Medium Priority
1,000 Views
Last Modified: 2006-11-17
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.
Comment
Watch Question

Commented:
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?
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())

Commented:
Sorry.  I didn't notice the <= in the join statement.  I've never seen anybody construct a join with a <= before.
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.

Commented:
<= v_AnalogHistory.Datetime

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

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

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

Author

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.

Author

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.
CERTIFIED EXPERT
Top Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
IT WORKS!

Thanks alot Lowfatspread!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.