Querying calendar events even if they do not have any for the day

Question

Hey everyone, I am trying to figure out a way to query my mysql server so that even if a company does not have anything posted for the day and the user clicks on their logo, it still adds them to the list.

That sounds a little confusing so let me try to explain it another way.

Say I have 3 companies in my database:

Comp1
Comp2
Comp3

And Comp1 & Comp3 have something for today on the calendar but Comp2 does not. I still need it to populate and place that company on the page but have something along the lines of "nothing on the calendar for today". The other 2 companies (Comp1 & Comp3) would show the calendar posting for that day.

This is the code I have right now:

 SELECT clientinfo.id, clientinfo.theCompName, clientinfo.theURL, clientinfo.picURL,
 clientinfo.idNumber, clientoffers.idNumber, clientoffers.theDateStart, clientoffers.theDateEnd
 FROM clientinfo, clientoffers
 WHERE clientinfo.accountStats = 'OPEN'
 AND clientinfo.idNumber = clientinfo.idNumber
 AND '2010-05-08' BETWEEN clientoffers.theDateStart AND clientoffers.theDateEnd
 GROUP BY clientinfo.idNumber
 ORDER BY clientinfo.theCompName ASC

That executes just fine but for Comp2, it just places the calendar info from Comp1 into it when it really doesn't have anything.

The output looks like this:

Comp1 | 2010-05-08 | this is the calendar event 1 | etc etc
Comp2 | 2010-05-08 | this is the calendar event 1 | etc etc <-- this should have no date or event just Comp2
comp3 | 2010-05-09 | this is the calendar event 2 | etc etc

What the output needs to look like is:

Comp1 | 2010-05-08 | this is the calender event 1 | etc etc
Comp2 |            |                              | etc etc
comp3 | 2010-05-09 | this is the calender event 2 | etc etc

I'll also add that if there is no event on the calendar for a company then there's no idNumber in the clientoffer table. So in the example above, Comp2 does not have any information in the clientoffer table so I am guessing that's why when I run my query that it just duplicates the last record's data since it can not find a match in the clientinfo.idnumber = clientoffer.idnumber

Any help would be great :o)

David

UPDATED #2

The DB structure:

clientinfo table:

theCompName | idNumber
comp 1      | 513200
comp 2      | 8944
comp 3      | 03884

clientoffers table:

idNumber | theDateStart | theDateEnd
513200   | 2010-05-08   | 2010-05-08
03884    | 2010-05-07   | 2010-05-09

Hope that helps.

Answer 1

Not sure I understood correctly ... but does this help:

SELECT clientinfo.id
     , clientinfo.theCompName
     , clientinfo.theURL
     , clientinfo.picURL
     , clientinfo.idNumber
     , IFNULL(clientoffers.idNumber,-1)
     , IFNULL(clientoffers.theDateStart,'')  
     , IFNULL(clientoffers.theDateEnd,'')
  FROM clientinfo 
  LEFT JOIN clientoffers 
    ON (      clientinfo.idNumber = clientoffers.idNumber
         AND '2010-05-08' BETWEEN clientoffers.theDateStart 
                              AND clientoffers.theDateEnd
       )
 WHERE clientinfo.accountStats = 'OPEN'
 GROUP BY clientinfo.idNumber
 ORDER BY clientinfo.theCompName ASC

You would have to check for an offer ID of -1 and then display the logo.

---

The idea here is to select all clients from clientinfo and show the joined info from clientoffers where it exists and a null if it doesn't exist. That's what the left join syntx does.

Answer 2

Here is my go at it. I have:

• Reduced number of columns so its easier to see whats going on
• Transformed this query to a LEFT JOIN query
• Moved the date comparison from WHERE clause to the ON clause
• Removed the GROUP BY clause: if a company has two events for same date, two rows are returned

So we have:

SELECT
    clientinfo.idNumber,
    clientinfo.theCompName,
    clientoffers.theDateStart,
    clientoffers.theDateEnd
FROM      clientinfo
LEFT JOIN clientoffers ON
    ( clientinfo.idNumber = clientoffer.idNumber) AND
    ( '2010-05-08' BETWEEN clientoffers.theDateStart AND clientoffers.theDateEnd )
WHERE    clientinfo.accountStats = 'OPEN'
ORDER BY clientinfo.theCompName ASC

I still need to know what primary and foreign keys are being used by the two tables. Let me know and I'll fix the query.