not equal JOIN problem

MichaelHe

I have a little JOIN problem.

I have a costumer table, a company table, and the 2 tables are compared with some filters, to find the companies that matches the costumers request.

This works fine, but now I have added a date in the costumer table.
And I now have another table where the companies put in some dates where they are not available.

So it a costumer table:
CostumerID
RequestDate

A Company table:
CompanyID
And some company date.

And a Not Available table:
AvailabilityID
CompanyID
Date

The companies then puts in the data in the available table where they are not available.

All this means that a company should not be matched with the costumer if the company is not available on the costumers request data.

The problem is that if i write
SELECT * FROM costumers cu INNER JOIN companies ca ON (cu.requestdate != ca.date)
it will work if the company as only this one date in the table.
But if the company puts in more dates it doesn't work.

And exampel:

CostumerID 1
RequestDate 2007-03-03

AvailabilityID 1
CompanyID 5
Date 2007-03-03

AvailabilityID 2
CompanyID 5
Date 2007-03-04

In this exampel company 5 will still be matched with costumer 1 on the AvailabilityID 2 so when I GROUP BY CompanyID at the end to find the companies that matches it will still show company 5 because it matches (not equal) on the date 2007-03-04.
And the company 5 should not be matched at all, because they are not available on the costumers request date...

So how do I 'not include' a company id when one of his dates is the same as the request date.

Roger_Ramjet

What you are after is a list of companies that are available on the given date = a list of companies who are not in the list of companies unavailable on that date.

// unavailable
"SELECT * FROM available WHERE date = $input";

Now from that you can construct several queries using it as a sub query to eliminate unavailable companies.

"SELECT * FROM costumers cu INNER JOIN companies ca 
WHERE companyid NOT IN (SELECT * FROM available WHERE date = $input)";

"SELECT * FROM costumers cu INNER JOIN companies ca 
LEFT JOIN (SELECT * FROM available WHERE date = $input) AS ua USING(companyid)
WHERE ua.companyid IS NULL";

MichaelHe

I see what you mean, and I can probably get this to work.. thank a lot...

But I see that you need a WHERE in both examples... I might need to compare more that one costumer in the same query in some situations.
That is findes the matches for costumer 3 and 8 ex. in the same query.

This will not be possible with the 2 scripts...

So isn't it possible at all to do it without the WHERE ??

Roger_Ramjet

No.

The sub query can be any valid select query so you can change it to account for multiple customers etc. Mine was just an example. Just start with the query that would get all the companies that are unavailable and build from there.