Help with SQL for calendar system

geskerrett

I am trying to create an SQL statement for a calendar system and I having some difficulty. I am using mySQL v3.23.53 running on Win2k.

I have the the following tables;

User Table:
Status
username
fullname

Calendar table:
id
username
stdate
enddate
caldesc
notes

I am tring to create a "SELECT" statement that returns the calendar entries for a specific date linked to the user as well as all of the users who DO NOT have entries in the calendar.

Something like;
username Stdate enddate caldesc
bill 2003-05-20 2003-05-30 Doing something
george null // no calendar entries
mary null // no calendar entries
Danny 2003-05-21 0000-00-00 Doing something

To date I have tried a number of things including a LEFT JOIN as follows;

SELECT u.username, d.stdate,d.enddate,d.caldesc,d.note
FROM webuser as u
LEFT JOIN caldates as d USING (username)
WHERE (d.stdate = "2003-05-30" or "2003-05-30" between d.stdate and d.enddate or d.stdate IS NULL)

This SQL returns all of the calendar entries, BUT only returns one user who do not have entries in the calendar table, when it should return 8 users with no entries for that date.

So ... anyone have any suggestions ??

weekender

break it down into two sql queries...

the first one gets a list of users...

$sql = "SELECT DISTINCTROW(username) FROM usertable";

the distinctrow just makes sure duplicates are ignored

then, cycle through each user (returned in the last query), and for each one, get the username and send another query

$subsql = "SELECT * FROM calendartable WHERE username = '$username'";

see?

geskerrett

Thanks, but forgot to explain that the users are all unique to the site. I was aware that I can do it with two queries and perhaps use the NOT IN (Array) command.

My confusion is that I really though this should be a dead simple LEFT JOIN ... so I could do it all with one query.

... any reason why the LEFT JOIN doesn't return the result I am looking for ?

geskerrett

Solved the problem myself.

The trick was I needed to move all of the conditions to the LEFT JOIN and use "ON" rather that the USING for the keys.

So the select ends up like this;

SELECT u.username, d.stdate,d.enddate,d.caldesc,d.note
FROM webuser as u
LEFT JOIN caldates as d ON u.username=d.username AND (dstdate = "2003-05-30" or "2003-05-30" between d.stdate and d.enddate or d.stdate IS NULL)

This gave me exactly the data i was expecting.