Join SQL tables

Suppose that in table "ab" I have the names of the students that get along from class "a" and class "b", identically I have table "ac" and "bc". What SQL query should I use in order to get all the combinations possible of students who can form groups (i.e. "get along together")? And how can i extend this to n classes? For example: John from class a gets along with Jen from class b and Steff from class c, and Jen and Steff get along. Therefore John, Jen and Steff can form a group).开发者_如何学Go

---

For this I would create two tables, a student table (id, name, class) and a relationship table (student1, student2). You might also want to add a class table for the time, location etc of the class.

A friendship would have two relationships (2,3) and (3,2) to describe it as two way. One way might be a follower or fan of another student. This will scale up to a lot more than 3 classes.

Then you can use multiple joins to get friends of friends and so on to an arbitrary depth.

Here is a query to get friends of friends (fof):

SELECT fof_details.*
FROM relationships r
INNER JOIN relationships fof
ON r.student2 = fof.student1
INNER JOIN student fof_details
ON fof_details.id = fof.student2
WHERE r.student1 = '12';

There are also database engines made specifically for doing graph modeling like this.

http://openquery.com/blog/graph-engine-mkii

---

This query should return all students who can be in one group with John.

WITH ABC AS (SELECT AB.A, AB.B, AC.C FROM (SELECT * FROM AB
                        INNER JOIN BC 
                        ON AB.B=BC.B) 
         INNER JOIN AC 
         ON (AC.C=BC.C AND AB.A=AC.A))
SELECT STUDENT FROM (
    SELECT AB.B STUDENT FROM ABC WHERE AB.A='John'
    UNION
    SELECT AC.C STUDENT FROM ABC WHERE AB.A='John')
GROUP BY STUDENT

PS.: Written fast without any syntax check, hope you'll be able to bring this to work :)

---

The initial query can be satisfied by the code

select ab.a, ab.b, ac.c
from
ab inner join
bc on ab.b = bc.b inner join
ac on ac.a = ab.a and bc.c = ac.c

Stepping up to n classes will get progressively more complex as n=4 would be the same query with the additional three joins

inner join ad on ab.a = ad.a 
inner join bd on bd.b = ab.b and ad.d = bd.d
inner join cd on cd.c = ac.c and ad.d = cd.d 

2 classes requires 1 table and no joins,
3 classes requires 3 tables and 2 joins,
4 classes requires 6 tables and 5 joins

So we can see it getting progressively more complex as we proceed

---

First you don't want to have a table for each class. You are capturing the same type of information in multiple tables and this is generally considered a bad practice. You want to "normalize" your data so that the same data exists in one place.

Second, name your tables appropriately so that you understand what you are actually trying to build. Maybe you are generalizing to mask what your intentions for the actual implementations are by using "ab" in the question, but if you are doing this in your actual code it will hurt you in the long run.

It appears you need a people table with names and a friends table where you track who is friends with who:

create table people ( id int, name char(128) );
create table friends ( id int, person_id int, friend_id int );

Then you just need to have the query to get the groups:

SELECT person.* FROM friends
INNER JOIN friends grp
        ON friends.friend_id = grp.person_id
INNER JOIN people person
        ON person.id = grp.friend_id
WHERE friends.person_id = 42;