The column prefix does not match with a tab

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

The column prefix does not match with a tab

Author

Topic

makinha
Starting Member

2 Posts

Posted - 2007-05-10 : 07:27:16

I have a table with 5 fields named Employee, in the begining I use the following statement to get the record set and pass it to the program

select * from Employee where REC_DATE < '2/4/2007 00:00:00.000' and
UPD_STMT_DATE > '31/3/2007 23:59:59.000'
ORDER BY USER_ID, LUP_DATE DESC

However, I found that the result set is too big and contain too much un-necessary entries,
so I would like to drop it down into each particular location only, if I want to select all employee records who
live in location 'ON' plus the entries contains their old address in different location, so I am thinking to select
1)all record in the particular date with the desired location,
2)plus the old records of the employe who changed to the desired location.
I can solve the first part 1) by using

select *
FROM Employee EMP
INNER JOIN USR_TB USR ON USR.USER_ID=EMP.USER_ID
where LOCATION='ON' and REC_DATE < '2/4/2007 00:00:00.000' and REC_DATE > '31/3/2007 23:59:59.000'
ORDER BY USER_ID, LUP_DATE

and the second part 2) by using

SELECT DISTINCT EMP1.*
FROM Employee EMP1, Employee EMP2
INNER JOIN USR_TB USR ON USR.USER_ID=EMP1.USER_ID
WHERE EMP1.UPD_USERID = EMP2.UPD_USERID and
EMP1.LOCATION<>'ON' and EMP2.LOCATION='ON' and
(EMP1.REC_TYP='A' or EMP1.REC_TYP='B') and
(EMP2.REC_TYP='A' or EMP2.REC_TYP='B') and
EMP1.REC_DATE < '2/4/2007 00:00:00.000' and EMP1.REC_DATE > '31/3/2007 23:59:59.000'
ORDER By EMP1.USER_ID, EMP2.LUP_DATE

and combine it all into one result set? I tried

select EMP.*, USR.USER_ID, USR.USER_NAME
FROM Employee EMP
INNER JOIN USR_TB USR ON USR.USER_ID=EMP.USER_ID
where LOCATION='ON' and REC_DATE < '2/4/2007 00:00:00.000' and REC_DATE > '31/3/2007 23:59:59.000'
UNION ALL
SELECT DISTINCT EMP1.*, USR1.USER_ID, USR1.USER_NAME
FROM Employee EMP1, Employee EMP2
INNER JOIN USR_TB USR ON USR1.USER_ID=EMP1.USER_ID
WHERE EMP1.UPD_USERID = EMP2.UPD_USERID and
EMP1.LOCATION<>'ON' and EMP2.LOCATION='ON' and
(EMP1.REC_TYP='A' or EMP1.REC_TYP='B') and
(EMP2.REC_TYP='A' or EMP2.REC_TYP='B') and
EMP1.REC_DATE < '2/4/2007 00:00:00.000' and EMP1.REC_DATE > '31/3/2007 23:59:59.000'
ORDER By EMP1.USER_ID, EMP2.LUP_DATE

and I got the following error:
"The column prefix 'EMP1' does not match with a table name or alias name used in the query."

I found that the error comes from the second part 2), after I insert the statement "INNER JOIN USR_TB USR ON USR.USER_ID=EMP1.USER_ID", is there anyone know how to solve it, thanks!!!!!

-----------------------------------------------------------------------------------------------------------------
(Table USR_TB)
-----------------------------------------------------------------------------------------------------------------
USER_ID USER_NAME LUP_DATE
0001 USER1 1/1/2007 01:00:00
0002 USER2 1/1/2007 01:15:00
0003 USER3 1/1/2007 01:30:00
0004 USER4 1/1/2007 02:15:00
0005 USER5 1/1/2007 02:00:00
0006 USER6 1/1/2007 03:15:00

-----------------------------------------------------------------------------------------------------------------
(Table Employee)
REC_TYP
A - pre record
B - New record
N - no chanage
-----------------------------------------------------------------------------------------------------------------
REC_TYP REC_DATE USER_ID LOCATION LUP_DATE
A 1/4/2007 01:00:00 0001 BC 1/1/2007 01:00:00
B 1/4/2007 01:00:00 0001 BC 1/4/2007 01:15:00
A 1/4/2007 02:00:00 0001 BC 1/4/2007 01:00:00
B 1/4/2007 02:00:00 0001 ON 1/4/2007 01:00:00
A 1/4/2007 03:00:00 0002 AB 7/2/2007 01:00:00
B 1/4/2007 03:00:00 0002 ON 1/4/2007 01:15:00
N 1/4/2007 04:00:00 0003 ON 1/4/2007 01:15:00
N 1/4/2007 04:00:00 0004 ON 1/4/2007 01:15:00
N 1/4/2007 04:00:00 0005 ON 1/4/2007 01:15:00
A 1/4/2007 04:00:00 0006 QB 2/2/2007 01:00:00
B 1/4/2007 04:00:00 0006 ON 1/4/2007 01:15:00
......
-----------------------------------------------------------------------------------------------------------------

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-10 : 07:37:34

[code]
select Emp.*
FROM Employee EMP
INNER JOIN USR_TB USR ON USR.USER_ID=EMP.USER_ID
where LOCATION='ON' and REC_DATE < '2/4/2007 00:00:00.000' and REC_DATE > '31/3/2007 23:59:59.000'
ORDER BY USER_ID, LUP_DATE
UNION ALL
SELECT DISTINCT EMP1.*
FROM Employee EMP1, Employee EMP2
WHERE EMP1.UPD_USERID = EMP2.UPD_USERID and
EMP1.LOCATION<>'ON' and EMP2.LOCATION='ON' and
(EMP1.REC_TYP='A' or EMP1.REC_TYP='B') and
(EMP2.REC_TYP='A' or EMP2.REC_TYP='B') and
EMP1.REC_DATE < '2/4/2007 00:00:00.000' and EMP1.REC_DATE > '31/3/2007 23:59:59.000'
ORDER By EMP1.USER_ID, EMP2.LUP_DATE
[/code]

Chirag

http://chirikworld.blogspot.com/

makinha
Starting Member

2 Posts

Posted - 2007-05-10 : 22:57:42

Hello chiragkhabaria,

Thanks for your reply, and I make a mistake on my last post, I need to select the USER_ID and the USER_NAME as well, so I change the statement as Select Emp.*, USR.USER_ID, USR.USER_NAME, and it works fine with the first part alone. So I guess the problem comes from the second part, and the error prompt after I add the "INNER JOIN USR_TB USR1 ON USR1.USER_ID=EMP1.USER_ID" statement in the second part, is there a problem to use INNER JOIN statement when I selecting 2 tables(EMP1 and EMP2)?? Thanks.

select EMP.*, USR.USER_ID, USR.USER_NAME
FROM Employee EMP
INNER JOIN USR_TB USR ON USR.USER_ID=EMP.USER_ID
where LOCATION='ON' and REC_DATE < '2/4/2007 00:00:00.000' and REC_DATE > '31/3/2007 23:59:59.000'
UNION ALL
SELECT DISTINCT EMP1.*, USR1.USER_ID, USR1.USER_NAME
FROM Employee EMP1, Employee EMP2
INNER JOIN USR_TB USR ON USR1.USER_ID=EMP1.USER_ID
WHERE EMP1.UPD_USERID = EMP2.UPD_USERID and
EMP1.LOCATION<>'ON' and EMP2.LOCATION='ON' and
(EMP1.REC_TYP='A' or EMP1.REC_TYP='B') and
(EMP2.REC_TYP='A' or EMP2.REC_TYP='B') and
EMP1.REC_DATE < '2/4/2007 00:00:00.000' and EMP1.REC_DATE > '31/3/2007 23:59:59.000'
ORDER By EMP1.USER_ID, EMP2.LUP_DATE

quote:

Originally posted by chiragkhabaria


select Emp.*
FROM Employee EMP
INNER JOIN USR_TB USR ON USR.USER_ID=EMP.USER_ID
where LOCATION='ON' and REC_DATE < '2/4/2007 00:00:00.000' and REC_DATE > '31/3/2007 23:59:59.000'
ORDER BY USER_ID, LUP_DATE
UNION ALL
SELECT DISTINCT EMP1.*, USR1.USER_ID, USR1.USER_NAME
FROM Employee EMP1, Employee EMP2
INNER JOIN USR_TB USR1 ON USR1.USER_ID=EMP1.USER_ID
WHERE EMP1.UPD_USERID = EMP2.UPD_USERID and
EMP1.LOCATION<>'ON' and EMP2.LOCATION='ON' and
(EMP1.REC_TYP='A' or EMP1.REC_TYP='B') and
(EMP2.REC_TYP='A' or EMP2.REC_TYP='B') and
EMP1.REC_DATE < '2/4/2007 00:00:00.000' and EMP1.REC_DATE > '31/3/2007 23:59:59.000'
ORDER By EMP1.USER_ID, EMP2.LUP_DATE

Chirag

http://chirikworld.blogspot.com/

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-11 : 13:57:16

Well, in your second query.. there is no table with the alias USR1.. thatz the reason you are getting error..

Chirag

http://chirikworld.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources