MySQL –– narrowing results by type
written by: admin
Date Written: 3/20/12
Last Updated: 4/10/12
The following pulls the results from a normalized table where the category = both 22 and 30.
SELECT art.title, art.ID, COUNT(art.title)
AS
count
FROM
art, art_cat_relationship
WHERE
art_cat_relationship.art_ID=art.ID
AND
(art_cat_relationship.cat_ID=22
OR
art_cat_relationship.cat_ID=30)
GROUP BY art.ID
HAVING count = 2
ORDER BY title
In the above normalized table
COUNT(article.title) counts the number of times a row is pulled from the database.
Note: Using
COUNT(article.ID) or
COUNT(*) would work just as well and do exactly the same thing.
AS count just renames COUNT(article.title) into something a little simpler.
GROUP BY article.ID groups the data where art_cat_relationships.cat_ID=22
OR
art_cat_relationships.cat_ID=30
by the article.ID so that the unique value in the pulled results is the article.ID.
Note: If I were to group it by the article.title instead then the unique value would be the article title. GROUP BY is used in conjunction with
aggregate values to display facts about a given group or groups of data. One example would be to find and display the lowest price for each brand of car.
GROUP BY
HAVING count = 2 This further narrows the GROUP BY clause, but can only be used with the item that is being grouped or aggregated (AVG, SUM, COUNT, etc.).
HAVING
The other aspects of the above query should be readily understood.
TAGS: mysql