referencing the table name twice in the query

[tamilselvan]

How and When to Use Self Join.

SQL> desc t1
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(20)
SUBJECTS VARCHAR2(20)

SQL> select * from t1;

NAME SUBJECTS
-------------------- --------------------
Tom History
Tom Math
Scott Math
Scott Physics
Mike Physics
Mike Math
William English
William Math

8 rows selected.


Now to find those students who are studying both Math and Physics,
we can write query in Oracle as:


select a.name, a.subjects, b.subjects from t1 a, t1 b
where a.name=b.name and a.subjects='Math' and b.subjects='Physics'
SQL> /

NAME SUBJECTS SUBJECTS
-------------------- -------------------- --------------------
Mike Math Physics
Scott Math Physics

2 rows selected.

The above is a perfect example for using Self-Join.

The same can be written in SQLServer as:

SELECT a.Name
FROM t1 AS a JOIN t2 AS b
ON a.subjects = 'Math' AND b.subjects = 'Physics'
AND a.name = b.name


Good Luck.

[slimdave]

How about ...

select name from t1
where subjects in ('Math','Physics')
group by name
having count(*) = 2

... with no join required.


You would rewrite ...

SELECT a.Name
FROM t1 AS a JOIN t2 AS b
ON a.subjects = 'Math' AND b.subjects = 'Physics'
AND a.name = b.name

... as ...

select a.name from t1 a, t2 b
where a.name = b.name
where a.subjects = 'Math' and b.subjects = 'Physics'

... for Oracle, although ANSI joins are supported in 9i