Solved

Select in Inner Join problem

Posted on 2004-03-26
11
955 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 6

Expert Comment

by:billy21
ID: 10688177
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
ID: 10688189
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
ID: 10688193
Sorry.  I didn't notice the <= in the join statement.  I've never seen anybody construct a join with a <= before.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 14

Expert Comment

by:adwiseman
ID: 10688217
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
ID: 10688227
<= v_AnalogHistory.Datetime

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

Expert Comment

by:billy21
ID: 10688241
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
ID: 10688249
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
ID: 10688584
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
ID: 10689699
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
ID: 10690275
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
ID: 10691150
IT WORKS!

Thanks alot Lowfatspread!
0

Featured Post

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Rebooting Witness SQL Server 2 25
SQL Server code help needed 14 32
Need to replicate a Log table 4 8
Need to find substring in SQL 3 7
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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…

749 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