skinknet

Search this site

SQL Inner Join Example

Say you have two tables.  One is a regular table which maps ID's to NAME's and the other is a table of three ID's.  

Table: Highschooler
 ID     Name Grade
 001 Joe 10
 002 Moe 9
 003 Jack 11
 004 Mary 12
 005 Duke 12
 006 Sal 10
 
Table:TheThree
 ID1  ID2 ID3
 006     004 002
 001002 003 

Say you want to generate a new table based upon these two such that it looks like this:

Table: TheResult
 Sal          Mary Moe
 Joe Moe Jack

This is how it is formed.

select Y1.name, Y2.name, Y3.name,  from TheThree as X inner join Highschooler as Y1 on Y1.id=X.ID1 inner join Highschooler as Y2 on Y2.ID=X.id2 inner join Highschooler as Y3 on Y3.ID=X.ID3

Now, if the table TheThree is actually the result of a query you can do this:

sqlite3 social.db "select Y1.name, Y1.grade, Y2.name, Y2.grade, Y3.name, Y3.grade from (select A.ID1 ID1, A.ID2 ID2, B.ID2 ID3 from Likes A join Friend B on A.ID1 <> B.ID2 and A.ID2 = B.ID1 where (A.ID1 not in (select ID1 from Friend where A.ID1=ID1 and A.ID2=ID2) and B.ID2 in (select ID1 from Friend where A.ID1=ID2))) as X inner join Highschooler as Y1 on Y1.id=X.ID1 inner join Highschooler as Y2 on Y2.ID=X.id2 inner join Highschooler as Y3 on Y3.ID=X.ID3"
Comments