Last time I explained the very basics of JOINs. Now I will go a bit more further and show how to build more advanced queries. (I will continue on the same database.)
First let’s remind the clauses order in the SELECT query:
SELECT
FROM table_references
WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
WHERE indicates the condition or conditions that rows must satisfy to be selected. GROUP BY must be used with any aggregating functions to group the resultset by one or more columns. HAVING works only with GROUP BY and should be used only with aggregation functions (while the WHERE clause cannot). ORDER BY is used to order the result set by a desired column or columns.
Many-To-Many
Let’s choose all pictures with a list of people tagged on each of them. This is a many-to-many relation that requires two joins.
select filename, name from people_pictures join pictures on (people_pictures.picture_id = pictures.id) join people on (people_pictures.people_id=people.id);
| picture1.jpg | Jack |
| picture1.jpg | Ben |
| picture1.jpg | Claire |
| picture2.jpg | John |
| picture2.jpg | Jack |
| picture2.jpg | Ben |
| picture3.jpg | Jin |
| picture3.jpg | Jack |
| picture5.jpg | John |
| picture5.jpg | Sun |
The result set has many rows with the same filenames. Sometimes this can be a disadvantage. There is an easy way to show every picture with coma-separated list of tagged characters. For that we can use group_concat function with GROUP BY clause:
select filename, group_concat(name) from people_pictures join pictures on (people_pictures.picture_id = pictures.id) join people on (people_pictures.people_id=people.id) group by filename;
| picture1.jpg | Jack,Claire,Ben |
| picture2.jpg | Ben,Jack,John |
| picture3.jpg | Jack,Jin |
| picture5.jpg | Sun,John |
If we need to exclude some results on some conditions we can use HAVING clause. Let’s choose only the pictures with at least three people tagged on them.
select filename from people_pictures join pictures on (people_pictures.picture_id = pictures.id) join people on (people_pictures.people_id=people.id) group by filename having count(name)>2;
| picture1.jpg |
| picture2.jpg |
We can also find all pictures with Jack.
select filename from people_pictures join people on (people_pictures.people_id = people.id) join pictures on (people_pictures.picture_id=pictures.id) where name = 'Jack';
| picture1.jpg |
| picture2.jpg |
| picture3.jpg |
At the end let’s find the most tagged character.
select people.name from people_pictures join people on (people_pictures.people_id = people.id) group by people_id order by count(people_id) desc limit 1;
| Jack |
All above examples work just fine for small database. However the loading time grows with the number of records. Next time I will show how to optimize such queries to keep the loading time reasonable .
No comments:
Post a Comment