Self Join

[abs]

Hi peepsFinding it abit difficult to understand SELF Join if someone could explain it or direct me to a site it would be very appreciated.

[abs]

:) why hasnt W3 schools covered SELF JOIN

[abs]

not alot of good tutorials online, just realised how good W3 schools is.any way went through my old Lecture notes and worked on the examples, can you guys let me know if my understanding is correctSELF JOINFind any buses which are the same model as bus H259IJKSELECT b2. *FROM bus b1, bus b2WHERE b1.reg_no = ‘H259IJK’ --Selects 1 field with the matching reg_no in table b1AND b1.model = b2.mode -- The model number is matched with the models in the b2 and displays details of similar modelsSELECT b2.* FROM bus AS b1 INNER JOIN bus AS b2 -- does it produce same result by using Inner Join statement? and is it recommended to ON b1.reg_no = ‘H259IJK’ -- type AS because I know you dont have to use but its more readableAND b1.model = b2.modelTo list the names of cleaners who are responsible for any bus types that cleaner Betty is responsible for:select c2.cnamefrom bus b1, bus b2, cleaner c1, cleaner c2where c1.cname = 'Betty'and c1.cno =b1.cnoand b1.tno=b2.tnoand b2.cno=c2.cnoand c2.cname<>'Betty';SELECT c2.cnameFROM bus b1, bus b2, cleaner c1, cleaner c2WHERE c1.cname = 'Betty' -- Selects Betty details which includes her cnoAND c1.cno =b1.cno -- her cno is linked to b1.cno in the bus table, the bus type number (tno) is found.AND b1.tno=b2.tno -- the b1.tno tells us the buses type Betty is responsible for, the b1.tno is linked with b2.tno to retrieve all the same bus types as Betty’s. b2 know has a list of buses that match Betty’s bus typeAND b2.cno=c2.cno -- b2 now contains all cleaners’ numbers responsible for cleaning the same buses as Betty. To find the names of all the cleaners the b2.cno is matched with the cleaner table c2.cno which has all the cleaners’ details.AND c2.cname<>'Betty'; -- Betty’s details are removed

[justsomeguy]

An inner join is the same as specifying the tables in a list. Using AS creates an alias, if you don't use AS it creates a copy.