MySQL query

safra

Hi,

I have 2 tables, 1 with employee data and 2 with contract data for each employee. For the results page to search for employees I have to display, apart from name etc., the current contract data from the second table.

I use something like this:

SELECT comerciales.name, comHistory.startdate, etc, etc... 
FROM comerciales,comHistory 
WHERE comHistory.startDate <= '...' AND comHistory.endDate >= '...' AND comerciales.id=comHistory.user
LIMIT 0, 15

The problem is, how to implement this in the current system where we only use the first table? How can we also display all employees for which we have not yet created a record in table 2?

Thanks,
Raoul

bpat1434

SELECT a., b.
FROM comerciales a,
INNER JOIN comHistory b
ON a.id=b.user

that gives you all the users whether they've got a record or not. Now, how do you do it with one table? You really can't. A join (which you did in the first place and I just explicitly stated is the only way to link the data together in the query.

safra

Thanks! I tried that, table a has over 300 records, the query only returned those that have a record in table 2?

bpat1434

SELECT a., b.
FROM comerciales a, comHistory b
WHERE a.id=b.user
AND b.user=NULL

try that one... cna't promise you anything..

esukf

You want either a LEFT OUTER JOIN or RIGHT OUTER JOIN depending on what results you want. Read up on it here http://en.wikipedia.org/wiki/Join_(SQL)

safra

Thanks guys!

LEFT OUTER JOIN basically works. The thing is that table b holds contract data. An employee can have more then one record in table B. If an employee has a record then it must only return the current record. I tried things like this:

SELECT a.*, b.*
FROM comerciales a,
LEFT OUTER JOIN comHistory b
ON a.id=b.user
WHERE b.startDate <= '...' AND b.endDate >= '...'

// and
SELECT a.*, b.*
FROM comerciales a,
LEFT OUTER JOIN comHistory b
ON a.id=b.user
WHERE (b.startDate <= '...' AND b.endDate >= '...') OR b.user = null

But then it doesnot return those users without a record in table b anymore. Can this be solved?

esukf

I thought this was gonna easy at first but it took a while to figure it out and I'm not completely sure it works.

The problem seems to be there is not easy way to select the row id that has the MAX date value. Anyway, please post back if it works or not.

If anyone know I better solution, I would like to know too.

SELECT employee.*, c.* FROM employee LEFT OUTER JOIN (SELECT contract.* FROM
 contract INNER JOIN (SELECT MAX(date) AS maxdate FROM contract GROUP BY
contract.id)d ON contract.date = d.maxdate GROUP BY id)c ON employee.id = c.id

safra

I am afraid not, I get errors.

esukf

hmm, it did work with the dummy tables i set up. Can you post the exact statement you used and the errors you got.

safra

I can now see what you are doing and experimented with some changes but no luck so far.

This is my exact implementation of what you suggested:

SELECT comerciales.id,comerciales.name, comerciales.apellido1,comerciales.apellido2,comerciales.username,c.startDate
FROM comerciales LEFT OUTER JOIN 
(SELECT comHistory.* FROM comHistory 
INNER JOIN (SELECT MAX(startDate) AS maxdate FROM comHistory 
GROUP BY comHistory.user) d ON comHistory.startDate = d.maxdate GROUP BY user) c 
ON comerciales.id = c.user LIMIT 0, 15

The returned error is just an ordinary "mysql_fetch_row(): supplied argument is not a valid MySQL result resource in ....."

esukf

Don't seem to be any syntax error with that statement. Can only guess your tables are differnt than my dummy ones. That php error doesn't really help. I suggest you download mysql query browser and use that to debug/build the statement.

safra

This is the error returned through PHPMyAdmin:

#1064 - You have an error in your SQL syntax near '(SELECT comHistory.* FROM comHistory
INNER JOIN (SELECT MAX(startDate) AS maxd' at line 3

I also just noticed that the MySQL version on this server is 3.23.58. Could that be the problem?

bpat1434

Okay, you've got two different queries posted.... the one you posted with the error, is not what you posted two posts up.... so which is it?

safra

Not sure what you mean? I copied the query and pasted it in PHPMyAdmin. It is exactly the same query as posted here at 01:21 PM.

liquorvicar

If you're running MySQL 3.23.58, then I'd guess that's the problems as I'm pretty sure subqueries were not supported before MySQL v4.

safra

Thanks liquorvicar, I think you are right, I am not sure but I a run in a similar problem with subqueries a while ago on another server.