On 2014/05/22 17:25, Humblebee wrote:
Oops back sooner than I thought.
I would like to order the grouping of the Persons in a particular
order. So the sequence of the result is based on the TeamPersonTable's
order field.
First a word of warning - Please do not use column names that are the same as SQL keywords, such as "Order"... I took the liberty of
renaming the new column to "orderId" because of this.
Next, introducing a great new feature of SQLite (and most other SQL Engines) called CTE or Common Table Expression, which lets you
define one kind of table and then extract a query from it, all in the same query. (This can also be achieved with Views if the SQL
Engine doesn't support CTE, such as older SQLites, but this is usually a 2-step process which may or may not be more useful
depending if you use the same base query for other purposes too).
Here it is simply used to make up a sorted table, and then from it do the
grouping and concat that is needed.
Same set of Queries as before, but with ordering added - which again is made
possible by that 1NF layout.
Per Team Query:
WITH WTP(teamId,personIDs,personNames) AS (
SELECT TP.teamId, P.id, P.name
FROM TeamPersonTable TP
LEFT JOIN PersonTable P ON P.id=TP.personId
WHERE TP.teamid=1
ORDER BY TP.orderId
)
SELECT group_concat(personIDs,','), group_concat(personNames,',')
FROM WTP
GROUP BY WTP.teamId;
Per person Query:
WITH WTP(personId,teamIDs,teamNames) AS (
SELECT P.id, T.id, T.name
FROM TeamPersonTable TP
LEFT JOIN TeamTable T ON T.id=TP.teamId
LEFT JOIN PersonTable P ON P.id=TP.personId
WHERE P.id=5
ORDER BY TP.orderId
)
SELECT group_concat(teamIDs,','), group_concat(teamNames,',')
FROM WTP
GROUP BY WTP.personId;
Again, you can just remove all the grouping words to reveal normal Query
listings.
TeamTable
+-------------------+
| id | name |
+-------------------+
| 1 | blue |
| 2 | green |
+-------------------|
PersonTable
+------------------+
| id | name |
+------------------+
| 4 | john |
| 5 | bill |
+---------+-------+
TeamPersonTable
+-----------------------------------+
| teamId | personId | orderId
+----------------------------------+
| 1 | 4 | 1
| 1 | 5 | 0
| 2 | 4 | 0
| 2 | 5 | 1
+---------+------------+----------+
Query Input: teamId = 1
Result:
personNames = "bill,john"
personIds = "5,4"
-------------
Query Input: teamId = 2
Result:
personNames = "john,bill"
personIds = "4,5"
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
