CODEWITHSUNDEEP
mysqlsqldatabasejoin
Retro
Retro

Reputation: 113

SQL Record only returns if no zero values present

This is the signup table from my MySQL database.

SIGNUP
id (int)
customer (int)
helper (int)
eid (int)
..plus more fields

This query returns 0 results when I would like for it to return the record in the database. Situation is that the SIGNUP table has one record with customer = 11 and helper = 0 and approved = 0

If the helper field is populated with a number that isnt 0 then the record gets returned, but if it is 0 then there are 0 records returned. If I take out the second select line (containing the v.field1, v.field2, etc..) then it will also work. The issue seems to be that there is no data to pull from the helper columns so it doesnt allow anything else to be returned, but there is still data I would like to return for the other fields. Open to ideas and all help is appreciated! Thanks much..

SELECT 
    u.id cId, u.first cFirst, u.email cEmail, u.username cUsername,   
    v.id hId, v.first hFirst, v.email hEmail, v.username hUsername, 
    s.customer, s.helper, s.eid EID, s.approved, 
    e.name, DATE_FORMAT(e.date, '%W, %b %e %Y %l:%i %p') date, e.summary, e.street, e.city, e.state, e.zip eZip
FROM signup s 
INNER JOIN events e ON e.id = s.eid
INNER JOIN users u ON u.id = s.customer
INNER JOIN users v ON v.id = s.helper
WHERE 
    (s.customer = 11 OR s.helper = 11)
AND e.date > sysdate()
ORDER BY e.date asc

Upvotes: 0

Views: 34

Answers (2)

Anand
Anand

Reputation: 1123

Try this instead:

SELECT 
    u.id cId, u.first cFirst, u.email cEmail, u.username cUsername,   
    v.id hId, v.first hFirst, v.email hEmail, v.username hUsername, 
    s.customer, s.helper, s.eid EID, s.approved, 
    e.name, DATE_FORMAT(e.date, '%W, %b %e %Y %l:%i %p') date, e.summary, e.street, e.city, e.state, e.zip eZip
FROM signup s 
INNER JOIN events e ON e.id = s.eid
INNER JOIN users u ON u.id = s.customer
left JOIN users v ON v.id = s.helper
WHERE (s.customer = 11 OR s.helper = 11)
AND e.date > sysdate()
ORDER BY e.date asc

Upvotes: 1

SQLChao
SQLChao

Reputation: 7847

If I understand correctly you need a LEFT JOIN. This way you get all rows even if there is no matching helper. 

SELECT 
    u.id cId, u.first cFirst, u.email cEmail, u.username cUsername,   
    v.id hId, v.first hFirst, v.email hEmail, v.username hUsername, 
    s.customer, s.helper, s.eid EID, s.approved, 
    e.name, DATE_FORMAT(e.date, '%W, %b %e %Y %l:%i %p') date, e.summary, e.street, e.city, e.state, e.zip eZip
FROM signup s 
INNER JOIN events e ON e.id = s.eid
INNER JOIN users u ON u.id = s.customer
LEFT JOIN users v ON v.id = s.helper
WHERE 
    (s.customer = 11 OR s.helper = 11)
AND e.date > sysdate()
ORDER BY e.date asc

Upvotes: 1

Related Questions