Create Alias Table and inner join it to itself

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

General SQL Server Forums

New to SQL Server Programming

Create Alias Table and inner join it to itself

Author

Topic

KyleFerence
Starting Member

4 Posts

Posted - 2014-12-18 : 19:06:37

Hello. I have a query from an ACCESS database that I want to move over to SISS, but it will not run and says the syntax is wrong. Does anyone know how to correct the syntax on the query below?

ACCESS Query:
UPDATE PROPERTYOWNERS INNER JOIN PROPERTYOWNERS AS PROPERTYOWNERS_1 ON PROPERTYOWNERS.gislink = PROPERTYOWNERS_1.gislink SET PROPERTYOWNERS.mixedownership = "MIXED"
WHERE (((PROPERTYOWNERS_1.secondaryclass)<>[PROPERTYOWNERS]![secondaryclass]));

Thanks for your help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-18 : 19:08:54

I don't know how to convert your WHERE clause, but here is the rest:

UPDATE PROPERTYOWNERS
SET mixedownership = 'MIXED'
FROM PROPERTYOWNERS
JOIN PROPERTYOWNERS AS PROPERTYOWNERS_1
ON PROPERTYOWNERS.gislink = PROPERTYOWNERS_1.gislink

What does this do: [PROPERTYOWNERS]![secondaryclass]

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

KyleFerence
Starting Member

4 Posts

Posted - 2014-12-19 : 11:17:54

The first part works :)
I think the WHERE clause was wrong in the ACCESS query. I think it should read (PROPERTYOWNERS_1.secondaryclass)<>(PROPERTYOWNERS.secondaryclass); but it still does not work.

The where clause is changing the MEMBERSHIP field to "MIXED" where the ownership is mixed in the SECONDARYCLASS. So if I have two owners for the same property I want it to read MIXED. So the WHERE clause is looking for two owners.

eg:
gislink | secondaryclass | mixdownership
121 | Private | MIXED
121 | Provincial | MIXED
488 | Private | NULL
488 | Private | NULL

So the WHERE clause is going to change the gislink to MIXED as it has two owners in the secondaryclass.

Thanks for all your help :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-19 : 12:13:35

Maybe this?

UPDATE PROPERTYOWNERS
SET mixedownership = 'MIXED'
FROM PROPERTYOWNERS
JOIN PROPERTYOWNERS AS PROPERTYOWNERS_1
ON PROPERTYOWNERS.gislink = PROPERTYOWNERS_1.gislink AND PROPERTYOWNERS_1.secondaryclass <> PROPERTYOWNERS.secondaryclass;

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

KyleFerence
Starting Member

4 Posts

Posted - 2014-12-19 : 12:37:56

I solved my problem.

UPDATE PROPERTYOWNERS
SET mixedownership = 'MIXED'
WHERE PROPERTYOWNERS.gislink IN
(SELECT PROPERTYOWNERS.gislink
FROM PROPERTYOWNERS
INNER JOIN PROPERTYOWNERS AS PROPERTYOWNERS_1 ON PROPERTYOWNERS.gislink = PROPERTYOWNERS_1.gislink
WHERE PROPERTYOWNERS_1.secondaryclass <> PROPERTYOWNERS.secondaryclass
)

KyleFerence
Starting Member

4 Posts

Posted - 2014-12-19 : 12:38:53

Thanks for your help :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-19 : 12:40:29

I don't understand why you switched it to a subquery. It is less efficient. At the very least, I'd switch your code to use WHERE EXISTS rather than IN.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources