How to get a row count when using relationships in mysql

Question

I'm a newbie with sql and I've managed to select rows with relationship to many tables with the sql below. I now have a forth table with items that are "children" to the model. There is a relation between "id" in the models table (526 rows) and "model_id" in the items table (1505 rows). I would now want to count the items in the items table that have the same model_id as the id in the models table and output them as Items_in_model.

SELECT models.id AS Id, categories.name AS Category, models.category_name 
AS CategoryName, manufacturers.name AS Manufacturer, models.model AS Model, 
models.day_price AS dayprice, models.week_price AS weekprice 
FROM
models, categories, manufacturers
WHERE models.category=categories.id AND 
models.manufacturer=manufacturers.id 
ORDER BY CategoryName ASC

I got the counting working "standalone" with this sql

SELECT COUNT(items.id) AS Count FROM items WHERE items.model_id 
LIKE 2

I've tried many different approaches but this is the only one that didn't give me an error but only outputs one row (of 526) and the count is (1505 as in all items)

SELECT models.id AS Id, categories.name AS Category, 
models.category_name AS CategoryName, COUNT(items.id) AS Count, 
manufacturers.name AS Manufacturer, models.model AS Model, 
COUNT(DISTINCT(items.id)) AS ModelCount, models.day_price AS dayprice, 
models.week_price AS weekprice 
FROM
models, categories, manufacturers, items
WHERE models.category=categories.id AND 
models.manufacturer=manufacturers.id AND models.id=items.model_id
ORDER BY CategoryName ASC

Thanks in advance!

Alex · Accepted Answer · 2019-02-15T15:13:03.193

0

Your goal is not 100% clear, but here is my guess:

SELECT m.id AS Id, 
    c.name AS Category,
    m.category_name AS CategoryName,
    manufacturers.name AS Manufacturer,
    m.model AS Model, 
    COUNT(i.id) AS ModelCount,
    m.day_price AS dayprice, 
    m.week_price AS weekprice 
FROM models m
LEFT JOIN categories c
ON m.category=c.id
LEFT JOIN manufacturers
ON m.manufacturer=manufacturers.id
LEFT JOIN items i
ON m.id=i.model_id
GROUP BY  m.id

UPDATE Not sure about your new question, But I doubt you need extra JOIN. Try this way:

SELECT m.id AS Id, 
    c.name AS Category,
    m.category_name AS CategoryName,
    manufacturers.name AS Manufacturer,
    m.model AS Model, 
    COUNT(i.id) AS ModelCount,
    SUM(IF(i.reservable = 1,1,0)) AS ModelReservableCount,
    m.day_price AS dayprice, 
    m.week_price AS weekprice 
FROM models m
LEFT JOIN categories c
ON m.category=c.id
LEFT JOIN manufacturers
ON m.manufacturer=manufacturers.id
LEFT JOIN items i
ON m.id=i.model_id
GROUP BY  m.id

edited Feb 15 '19 at 15:13

answered Feb 14 '19 at 20:00

Alex

16,739
1
28
51

Yes, exactly what I was looking for. Thanks! – nickehallgren Feb 15 '19 at 06:17
A follow-up question, if I besides the total item count want to count all items that have the item reservable column equal to 1, is that possible? If I add to the ON like this "ON m.id=i.model_id AND i.reservable=1" I get the result I want but then I do not get the total count at the same time... – nickehallgren Feb 15 '19 at 08:23
Exactly what I was looking for, but can you explain what the SUM line does, why the 1,1,0? And if you are interested to take a look I have another problem related to the same tables here. Based on what I've googled all day it might not be as easy to solve... https://stackoverflow.com/questions/54706596/sort-a-table-based-on-values-with-parent-child-from-another-table – nickehallgren Feb 15 '19 at 20:32
I found the perfect explanation to the SUM IF line here, really useful function... http://www.mysqltutorial.org/mysql-if-function.aspx – nickehallgren Feb 16 '19 at 10:52

How to get a row count when using relationships in mysql

1 Answers1