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:
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
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
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
theCompName | idNumber
comp 1 | 513200
comp 2 | 8944
comp 3 | 03884
idNumber | theDateStart | theDateEnd
513200 | 2010-05-08 | 2010-05-08
03884 | 2010-05-07 | 2010-05-09
Here is my go at it. I have:
WHEREclause to the
GROUP BYclause: 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.