CODEWITHSUNDEEP
phpmysqljoin
Alan Doolan
Alan Doolan

Reputation: 175

sql join not displaying correctly

I'm currently trying to join two tables with a left join:

--portal--

--access--

'access' contains a number of ticket types per portal for each event. I need to combine these to get the sum total of the scan_access column for each portal but include the portals that have 'null' scan_access to come up with '0'. To achieve this I've used a left join:

SELECT portal.name_portal, SUM(access.scan_access) AS total_scan 
FROM portal LEFT JOIN access ON portal.id_portal = access.id_portal 
WHERE portal.id_venue = $venueId 
GROUP BY portal.id_portal 
ORDER BY portal.id_portal ASC

which means I get the following:

but I have an issue when I need to also get the above result when taking into account the event (id_event) because when I use the following:

SELECT portal.name_portal, SUM(access.scan_access) AS total_scan 
FROM portal LEFT JOIN access ON portal.id_portal = access.id_portal 
WHERE portal.id_venue = $venueId AND access.id_event = 20 
GROUP BY portal.id_portal 
ORDER BY portal.id_portal ASC

I get:

which makes sense as those are the only two rows that have an id_event value. But how can I take this col into account without losing the other portals? also, is there a way in sql to make the 'null' a zero when returning a result? (I can fix the null after with php but wanted to see if it was possible)

Upvotes: 0

Views: 59

Answers (2)

ThinkingStiff
ThinkingStiff

Reputation: 65391

By putting access.id_event = 20 in your WHERE clause, you turn your LEFT JOIN into an INNER JOIN. Move access.id_event = 20 into your join criteria to preserve your LEFT JOIN. As @echo_me mentioned, you can use COALESCE() to get rid of your zeroes. I'd put it around the SUM(), instead of inside.

SELECT portal.name_portal, COALESCE( SUM(access.scan_access), 0 ) AS total_scan 
FROM portal LEFT JOIN access ON portal.id_portal = access.id_portal AND access.id_event = 20 
WHERE portal.id_venue = $venueId
GROUP BY portal.id_portal 
ORDER BY portal.id_portal ASC

Upvotes: 1

echo_Me
echo_Me

Reputation: 37243

to convert NULL to 0 use this

 COALESCE(col, 0)

in your example it will be

     SUM(COALESCE(access.scan_access, 0)) AS total_scan

Upvotes: 1

Related Questions