Aliases - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Aliases

Author

Topic

nguyenl
Posting Yak Master

128 Posts

Posted - 2008-05-07 : 17:46:02

Please help...

I need help analyzing this query. I am not sure why the person that wrote this query used the same table for 3 different aliases.

FROM property,
address,
ppi,
code_table state,
code_table country,
code_table prop_role

I tried to get rid of :

code_table country,
code_table prop_role

and put "state" in any line that referenced:

code_table country,
code_table prop_role

to see if the query worked the same, but I different results. Why?

Please help...

--7 seconds to omplete.
--set explain on;
--UNLOAD TO '/export/home/permit/owneraddress.txt'
SELECT ppi.id,
ppi.property_id,
ppi.party_id,
ppi.address_id,
ppi.eff_from_date,
ppi.eff_to_date,
address.line_1,
address.line_2,
address.line_3,
address.city,
state.code_table_cd,
address.zip_postal_code,
country.code_description,
address.line_care_of,
prop_role.code_table_cd

FROM property,
address,
ppi,
code_table state,
code_table country,
code_table prop_role

WHERE property.id = ppi.property_id
AND ppi.address_id = address.id
AND (property.eff_to_date IS NULL OR property.eff_to_date >= getdate())
AND (ppi.eff_to_date IS NULL OR ppi.eff_to_date >= getdate())
AND ppi.eff_from_date <= getdate()
AND state.id = address.province_state_cd
AND country.id = address.country_cd
AND prop_role.id = ppi.prop_role_cd
AND prop_role.code_table_cd = 'OWNER'
AND property.pact_code <> 'PERS'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-07 : 18:23:39

I think they are different because of this:
AND state.id = address.province_state_cd
AND country.id = address.country_cd

Are you using MS SQL Server though? I ask because of your UNLOAD and set explain commands at the top. I realize they are commented out, but those aren't valid in SQL Server.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

nguyenl
Posting Yak Master

128 Posts

Posted - 2008-05-07 : 18:58:44

Hi thanks for the reply:

If I were to get rid of all the other aliases and just keep the "state alias. The script would look like this:

--set explain on;
--UNLOAD TO '/export/home/permit/owneraddress.txt'
SELECT ppi.id,
ppi.property_id,
ppi.party_id,
ppi.address_id,
ppi.eff_from_date,
ppi.eff_to_date,
address.line_1,
address.line_2,
address.line_3,
address.city,
state.code_table_cd,
address.zip_postal_code,
state.code_description,
address.line_care_of,
state.code_table_cd

FROM property,
address,
ppi,
code_table state

WHERE property.id = ppi.property_id
AND ppi.address_id = address.id
AND (property.eff_to_date IS NULL OR property.eff_to_date >= getdate())
AND (ppi.eff_to_date IS NULL OR ppi.eff_to_date >= getdate())
AND ppi.eff_from_date <= getdate(}
AND state.id = address.province_state_cd
AND state.id = address.country_cd
AND state.id = ppi.prop_role_cd
AND state.code_table_cd = 'OWNER'
AND property.pact_code <> 'PERS'

ORDER BY ppi.id

I get different results when I run this.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-07 : 19:00:08

Like I said, it's because of this part of the code:
AND state.id = address.province_state_cd
AND country.id = address.country_cd

You need two joins to code_table because of this. Whoever designed the system decided to use that table for multiple uses.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

nguyenl
Posting Yak Master

128 Posts

Posted - 2008-05-07 : 19:35:28

The original script was written in Informix SQL. It does contain to "outer" commands in the "From field. Do you know how to convert it into its SQL equivlent?

Thanks for all your help.

--set explain on;
--UNLOAD TO '/export/home/permit/owneraddress.txt'
SELECT ppi.id,
ppi.property_id,
ppi.party_id,
ppi.address_id,
ppi.eff_from_date,
ppi.eff_to_date,
address.line_1,
address.line_2,
address.line_3,
address.city,
state.code_table_cd,
address.zip_postal_code,
country.code_description,
address.line_care_of,
prop_role.code_table_cd

FROM property,
address,
ppi,
outer code_table state,
outer code_table country,
code_table prop_role
WHERE property.id = ppi.property_id
AND ppi.address_id = address.id
AND (property.eff_to_date IS NULL OR property.eff_to_date >= getdate())
AND (ppi.eff_to_date IS NULL OR ppi.eff_to_date >= getdate())
AND ppi.eff_from_date <= getdate()
AND state.id = address.province_state_cd
AND country.id = address.country_cd
AND prop_role.id = ppi.prop_role_cd
AND prop_role.code_table_cd = 'OWNER'
AND property.pact_code <> 'PERS'

ORDER BY ppi.id

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-07 : 19:46:15

Check out OUTER JOINs in SQL Server Books Online. In T-SQL, you can do it either way though but the JOIN syntax is preferred.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources