how to join table by itself

Question

for project purposes only. please help me

Answer 1

Annabhel Jane Melca
To join an table with itself is called as self join
Self join == cross product + where condition
For example you have an example with table as study and study table contains S_id and C_id and table contains

Study table:-
---------------------------
S_id | C_id
S1 | C1
S2 | C2
S1 | C2
----------------------------
Now let this table as T1 and make an copy of this table and name as T2 and then do cross product which will give
T1 T2
S1C1 S1C1
S1C1 S2C2
S1C1 S1C2
S2C2 S1C1
S2C2 S2C2
S2C2 S1C2
S1C2 S1C1
S1C2 S2C2
S1C2 S1C2

Now select those student who is enrolled in at least two courses
The query will be written as:-

Select T1.S_id from Study as T1,
Study as T2
Where T1.S_id = T2. S_id and T1.C_id <> T2.C_id

Where this sign <> is for not equal to

This above query is for self joining table.

Hopefully it will clear your some doubts😉