I have one sellers table where sellerid column is sno
and I have products table where sellerid is sellerid
, I want to get those sellers who are not blocked (there is column named flag
in sellers table) and have created at least one product so I write this(or maybe copy this)
JavaScript
x
SELECT e.*, count(*) AS count
FROM sellers AS e
left join products AS r ON e.sno = r.sellerid
where NOT e.flag='1'
GROUP BY e.sno
Now I do my logic through:
JavaScript
if($row["count"] == 1){
continue;
}
It is producing incorrect result
Advertisement
Answer
JavaScript-- fake table data
CREATE TABLE sellers SELECT 1 sno, 0 flag UNION SELECT 2, 0 UNION SELECT 3, 0;
CREATE TABLE products SELECT 1 sellerid UNION ALL SELECT 1 UNION ALL SELECT 2;
SET sql_mode := '';
JavaScript-- initial query
SELECT e.*, count(*) AS `count`
FROM sellers AS e
left join products AS r ON e.sno = r.sellerid
where NOT e.flag='1'
GROUP BY e.sno
sno flag count 1 0 2 2 0 1 3 0 1
JavaScript-- counting joined values, not rows (NULLs are ignored)
SELECT e.*, count(r.sellerid) AS `count`
FROM sellers AS e
left join products AS r ON e.sno = r.sellerid
where NOT e.flag='1'
GROUP BY e.sno
-- further filtering by count>0 needed
sno flag count 1 0 2 2 0 1 3 0 0
JavaScript-- join only rows which have matched rows in second table
SELECT e.*, count(*) AS `count`
FROM sellers AS e
inner join products AS r ON e.sno = r.sellerid
where NOT e.flag='1'
GROUP BY e.sno
-- no further filtering needed
sno flag count 1 0 2 2 0 1
db<>fiddle here