Pennywisdom
asked on
Select in Inner Join problem
Consider the following select:
SELECT *
FROM v_AnalogHistory
INNER JOIN (SELECT TOP 1 NaedChange.NaedID,NaedChan ge.Datetim e,RPMSetup Prod.dbo.N aed.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.
SELECT *
FROM v_AnalogHistory
INNER JOIN (SELECT TOP 1 NaedChange.NaedID,NaedChan
FROM RPMViewProd.dbo.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.
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,RPMSetup Prod.dbo.N aed.Name,M AX(NaedCha nge.Dateti me)
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,RPMSetup Prod.dbo.N aed.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())
SELECT *
FROM v_AnalogHistory
INNER JOIN (SELECT NaedChange.NaedID,RPMSetup
FROM RPMViewProd.dbo.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,RPMSetup
WHERE v_AnalogHistory.Tagname = 'L1_Sealer_TEMP_F'
AND v_AnalogHistory.Datetime >= DateAdd(n,-15,getDate())
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.
<= v_AnalogHistory.Datetime
You can't put that part in the sub query. It has to be part of the join syntax.
You can't put that part in the sub query. It has to be part of the join syntax.
SELECT *
FROM v_AnalogHistory
INNER JOIN ( SELECT NaedChange.NaedID,NaedChan ge.Datetim e,RPMSetup Prod.dbo.N aed.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())
FROM v_AnalogHistory
INNER JOIN ( SELECT NaedChange.NaedID,NaedChan
FROM RPMViewProd.dbo.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())
Sorry again. That should have read:
SELECT *
FROM v_AnalogHistory
INNER JOIN ( SELECT NaedChange.NaedID,NaedChan ge.Datetim e,RPMSetup Prod.dbo.N aed.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())
SELECT *
FROM v_AnalogHistory
INNER JOIN ( SELECT NaedChange.NaedID,NaedChan
FROM RPMViewProd.dbo.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
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
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
IT WORKS!
Thanks alot Lowfatspread!
Thanks alot Lowfatspread!