Inner Joins and Table alias

[cobp]

Hi there,

I would like to use table alias(FROM <tbl> AS) in an SQL that has an INNERJOIN on another Table. Any tips for this are most welcome.
I want to do this because, i would like to have subquery in the where clause.


e.g.-

SELECT DISTINCT TABLE1.FIELD1, TABLE1.FIELD2,TABLE1.FIELD3
FROM TABLE1 AS T1 INNER JOIN TABLE2 ON TABLE1.FIELD1 = TABLE2.FIELDX
WHERE
NOT EXISTS
(SELECT * FROM TABLE1 AS T2 WHERE T1.FIELD2 = T2.FIELD1)

I want to use the alias for TABLE1 in the first part of the query. But it complains about hte invalid syntax.

 

[dodge20]

When making an alias, you don't use the keyword AS for the table. As is used for an alias on a specific field.

Select field1 as testfield from table1

When making an alias for a table you just have to eliminate the AS.

Select * from
Customer F, Customer S
where......

The rest of your query looks fine.

 

[cobp]

OK, Alias is optional, but the problem here is it doesn't work when used together with INNERJOIN

 

[dodge20]

I don't see the need to use an inner join. Most Sql versions will automatically pick the correct join for you. If I understand your query correctly this should give you the results you want.


SELECT DISTINCT TABLE1.FIELD1, TABLE1.FIELD2,TABLE1.FIELD3
FROM TABLE1 T1
WHERE TABLE1.FIELD1 = TABLE2.FIELDX
AND
NOT EXISTS
(SELECT * FROM TABLE1 T2
WHERE TABLE1.FIELD2 = T2.FIELD1)

 

[cobp]

You are right. Instead of InnerJoin I could use additional WHERE conditions for the table(ANDs). Then it works.

Thankx for answer