On 2014/05/22 13:39, Humblebee wrote:
TeamTable
+-------------------+
| id | name |
+-------------------+
| 1 | blue |
| 2 | green |
+-------------------|
PersonTable
+------------------+
| id | name |
+------------------+
| 4 | john |
| 5 | bill |
+---------+-------+
TeamPersonTable
+-----------------------+
| teamId | personId |
+-----------------------+
| 1 | 4 |
| 1 | 5 |
| 2 | 4 |
| 2 | 5 |
+---------+----------+
So a person can belong to any teams.
Query Input: teamId = 1
Result:
personNames = "john,bill"
personIds = "4,5"
Ok that one is easy I think - Joins and Grouping are magic - try this:
SELECT group_concat(P.id,','), group_concat(P.name,',')
FROM TeamPersonTable TP
LEFT JOIN PersonTable P ON P.id=TP.personId
WHERE TP.teamid=1
GROUP BY TP.teamid;
Similarly, if you fancy to pick teams by names, something like this would work:
SELECT group_concat(P.id,','), group_concat(P.name,',')
FROM TeamPersonTable TP
LEFT JOIN PersonTable P ON P.id=TP.personId
LEFT JOIN TeamTable T ON T.id=TP.teamId
WHERE T.name LIKE 'blu%'
GROUP BY TP.teamid;
Or to see the teams to which a person belongs:
SELECT group_concat(T.id,','), group_concat(T.name,',')
FROM TeamPersonTable TP
LEFT JOIN PersonTable P ON P.id=TP.personId
LEFT JOIN TeamTable T ON T.id=TP.teamId
WHERE P.name LIKE 'john%'
GROUP BY TP.personId;
And if you drop all the words with "group" in them, it will look like a
standard listed query:
SELECT T.id AS TeamID, T.name AS TeamName, P.id AS PersonID, P.name AS
PersonName
FROM TeamPersonTable TP
LEFT JOIN PersonTable P ON P.id=TP.personId
LEFT JOIN TeamTable T ON T.id=TP.teamId
WHERE P.id=5;
Let us know if anything is unclear or you need it to work differently.
(Btw, WHERE P.id=5 will be much faster than WHERE P.name LIKE 'bill%' or some
such)
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
