StealthRT StealthRT - 14 days ago 7
MySQL Question

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

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

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.