Trying to create MySQL queries can be a daunting task for the beginner and before understanding the logic it can be really confusing. A common task is to select related data from two tables to generate for example a list of blog posts, where you might want to also display the number of comments for each post.
This is best done by using a JOIN query. How to construct the query will be explained here, step by step.
First of all, we will put the fields we want to get in the SELECT-statement, like this:
SELECT T1.C1, T1.C2, T2.C1, COUNT( T2.C3 ) AS alias1
T1 denotes table 1 and T2 table 2. C1, C2…etc denotes columns, or fields. Writing T1.C1 simply means we want to select column 1 in table one. Now the COUNT () AS statement means basically we will count the number of lines in table 2 (which we assume contains the comments) and call them alias1
FROM T1
LEFT JOIN T2 ON T1.C1 = T2.C1
Here’s the trick. Thise statment tells us which tables we are using. T1 is the main table, and then we (left) join table T2 for rows where the condition T1.C1 = T2.C1 is met. Left join is a construct that basically tells MySQL to return also NULL values (fields lacking values) .
WHERE T1.C2 = 'condition'
GROUP BY T1.C1, T1.C2, T2.C1
ORDER BY T1.C3 DESC
The rest of the statement is straight forward, stating wich results to select from table T1, how the results will be grouped and in what order they should be returned.
Here’s the full statement:
SELECT T1.C1, T1.C2, T2.C1, COUNT( T2.C3 ) AS alias1
FROM T1
LEFT JOIN T2 ON T1.C1 = T2.C1
WHERE T1.C2 = 'condition'
GROUP BY T1.C1, T1.C2, T2.C1
ORDER BY T1.C3 DESC
Popularity: 5% [?]