problem with self join

[Bronte1226]

Hello. I am trying to query a table to figure out which fields have a matching projectID but not a matching projectName and return the objectID of each incident. I have been attempting to use a self join to accomplish this but I keep getting errors stating that my columns are invalid. Here is my query

USE EXISTING
SELECT W1.PROJECT_NAME AS A, w1.prikey, W1.PRIKEY, W2.PROJECT_NAME AS B, w1.project_number as P1,
w2.project_number as P2
FROM wWaterline W1 INNER JOIN
wWaterline W2 ON W1.P1 = W2.P2

Any guidance with this would be greatly appreciated.

 

[gmmastros]

You are trying to use a column alias in your ON clause. Instead, do this...

SELECT W1.PROJECT_NAME AS A, 
       w1.prikey, 
       W1.PRIKEY, 
       W2.PROJECT_NAME AS B, 
       w1.project_number as P1,
       w2.project_number as P2
FROM   wWaterline W1 
       INNER JOIN wWaterline W2 
         ON W1.[!]project_number[/!]  = W2.[!]project_number[/!]

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wbodger]

You can't use an alias to join on (W1.P1 = W2.P2), use the actual column name and it should work fine.

 

[wbodger]

Dang, too slow...

 

[SQLSister]

A where clause would help you here as well

where W1.PROJECT_NAME <> W2.PROJECT_NAME

"NOTHING is more important in a database than integrity." ESquared