Solved

Select in Inner Join problem

Posted on 2004-03-26
11
949 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.
0
Comment
Question by:Pennywisdom
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 6

Expert Comment

by:billy21
Comment Utility
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
 
LVL 14

Expert Comment

by:adwiseman
Comment Utility
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
 
LVL 6

Expert Comment

by:billy21
Comment Utility
Sorry.  I didn't notice the <= in the join statement.  I've never seen anybody construct a join with a <= before.
0
 
LVL 14

Expert Comment

by:adwiseman
Comment Utility
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
 
LVL 6

Expert Comment

by:billy21
Comment Utility
<= v_AnalogHistory.Datetime

You can't put that part in the sub query.  It has to be part of the join syntax.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 6

Expert Comment

by:billy21
Comment Utility
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
 
LVL 6

Expert Comment

by:billy21
Comment Utility
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
 

Author Comment

by:Pennywisdom
Comment Utility
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
 

Author Comment

by:Pennywisdom
Comment Utility
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
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
Comment Utility
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
 

Author Comment

by:Pennywisdom
Comment Utility
IT WORKS!

Thanks alot Lowfatspread!
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now