Left Join Headache [RESOLVED]

Hi All

I am using the following Query: (MySQL v4.1.1a)

SELECT DISTINCT avail_template.staffkey, avail_template.day_no,
avail_template.details,
avail_template.is_shift,
avail_template.start,
avail_template.finish
FROM avail_template
LEFT JOIN rostered_shifts ON
avail_template.staffkey = rostered_shifts.staffkey and
adddate('2004-04-16', avail_template.day_no) = rostered_shifts.date
WHERE (((rostered_shifts.staffkey) Is Null));

I thought that this would return a result of those records in the avail_template table that no matching record in the rostered_shifts existed where rostered_shifts.staffkey = avail_template.staffkey and rostered_shifts.date = the date calculated in the adddate function... (This I wanted to be a unique date for each day_no in avail_template)

My problem is that the query is returning all the records from the avail_template table even though there are records that match the staffkey, date combination in the rostered_shifts table.

I am new at the whole Join thing and have played with various elements within the query for hours now. To no avail. I have called the computer the filthiest names I could imagine, and whispered sweet endearments at the thing and Nuthin...

Any suggestions before I lose my sanity would be much appreciated.

Thanks

Dave

The problem is, is you have the date condition inside your join condition. It should look like this:

Code:

---

SELECT DISTINCT avail_template.staffkey, avail_template.day_no,
avail_template.details,
avail_template.is_shift,
avail_template.start,
avail_template.finish
FROM avail_template
LEFT JOIN rostered_shifts ON
avail_template.staffkey = rostered_shifts.staffkey
WHERE rostered_shifts.staffkey IS NULL AND rostered_shifts.date = adddate('2004-04-16', avail_template.day_no);

---

I think the query you posted is fine, however the "date" portion of the Left Join is not matching any records (therefore everything is returned).

Does a Date field in MySQL (I don't use MySQL) contain the Time as well? If yes, make sure you are only comparing the date portion of the fields.

Quote:

---

Originally posted by brucevde
I think the query you posted is fine, however the "date" portion of the Left Join is not matching any records (therefore everything is returned)

---

If the date condition doesn't match anything then an empty result set should be returned - not one containign everything.

Thanks heaps for the replies visualad & brucevde. I tried the query with the date comparison in the WHERE clause and swore more at the computer. Nothing was returned in the query....

Which leads to the date issue. MySQL uses the date format yyyy-mm-dd hh:mm:ss

When I run a query like this:

Code:

---

select rostered_shifts.rs_key, rostered_shifts.date from
rostered_shifts
where rostered_shifts.date = adddate('2004-04-16', 1)

---

I get the correct rows returned - 35 - i.e. those rows in rostered_shifts where date = 2004-04-17

When I run a query like this:

Code:

---

select rostered_shifts.rs_key, rostered_shifts.date from
rostered_shifts inner join avail_template on rostered_shifts.staffkey = avail_template.staffkey
where rostered_shifts.date = adddate('2004-04-16', 1)

---

I get 490 rows returned (14 records per staff member in avail_template * 35 rows = 490 rows)

When I run a query like this:

Code:

---

select rostered_shifts.rs_key, rostered_shifts.date from
rostered_shifts inner join avail_template on rostered_shifts.staffkey = avail_template.staffkey
where rostered_shifts.date = adddate('2004-04-16', avail_template.day_no)

---

Nothing is returned. So it looks like the adddate function is not working with a field value inside of it. Which is a real bugger.

The 'date' parameter of the adddate function is assigned via code. (VB6 app) This is passing in the right string. I will play with the adddate function and see what happens

Thanks again
Dave

It was the date thing..

If I change the line in statement to read

Code:

---

adddate('2004-04-16', avail_template.day_no) = date(rostered_shifts.date)

---

Keeping this condition in the JOIN clause of the statement, then it works beautifully.

Thanks again. You guys have got me moving forward again rather than banging my head against a brick wall.

Nice one

Quote:

---

Posted by Visualad
If the date condition doesn't match anything then an empty result set should be returned - not one containign everything.

---

That would be true if a query uses an Inner Join. A Left Join will return all rows in the "left table" regardless of the join condition.

You can have a normal where condition in a join clause?? I didn't know that. I always though you could only have joins between primary keys and foreign keys. :confused:

What effect does this have on the returned result set?