[SOLVED] LEFT JOIN ORDER BY not working

[freshness]

Hi,

 

Oooooh la - my first post!

 

I was wondering if someone could help me. So far, by googling myself to death, I have managed to suss out a LEFT JOIN command using the SQL below:

 

SELECT ta.*, datediff(NOW(), tn.added) as daysSinceLastUpdate FROM tbl_agents AS ta LEFT JOIN tbl_notes AS tn ON ta.agent_id = tn.for_id GROUP BY ta.agent_id ORDER BY ta.title ASC, tn.added DESC

 

To explain, I have a table filled with Estate Agents (tbl_agents - ta) and a table filled with notes about these agents (so that when staff are on  the phone they can log details of their conversations) - tbl_notes - tn.

 

I go a GROUP BY ta.agent_id so that I only view 1 result per estate agent (instead of many results based upon how many notes have been added for that estate agent).

 

What I wanted to do, was calculate when the last note was added and compare it with NOW() and store the number of days since the last note was added (tn.added stores this as a DATETIME)... but, even if I do an ORDER BY tn.added DESC or ASC it has no effect on whether the first or last added note is being used for the comparrison.

 

Basically at present the SQL query above only ever uses the date of the first note added (oldest) for comparison.

 

I hope this makes sense? Perhaps there is a better way to achieve what I am trying to do? I did think that keeping it in one query would be best, but perhaps multiple queries would give me what I am after?

[Illusion]

I think, we can't use both ASC and DESC in a single query.You can go for either ASC  or DESC.

[freshness]

Thanks for the speedy reply. I have just tried using 1 ASC / DESC command, and it has no effect. Any other ideas?

 

Thanks in advance.

[Illusion]

try this

SELECT tbl_agents.*, datediff(NOW(), tbl_notes.added) as daysSinceLastUpdate FROM tbl_agents,tbl_notes  ON tbl_agents.agent_id = tbl_notes.for_id GROUP BY tbl_agents.agent_id ORDER BY tbl_notes.added DESC

[freshness]

MySQL Said: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON tbl_agents.agent_id = tbl_notes.for_id GROUP BY tbl_agents.agent_id ORDER BY ' at line 1

[Illusion]

sorry yaar, I forgot to change the ON operator toWHERE clause.So replace OR with WHERE

[freshness]

ok, that didn't work, I'm afraid.

 

I've attached a screenshot of the results - the number after the title is daysSinceLastUpdate.

 

The first note for agent 'Carlos Muchacho' (!) was created 21 days ago. Yet the most recent note was created 2 days ago (June 9th).

 

The phone icon and the red highlighting should only be present if daysSinceLastUpdate is >= 3.

 

I'm not sure I can get around this, if the ORDER BY command has no effect?

 

[attachment deleted by admin]

[Illusion]

What exactly you want , the records which have daysSinceLastUpdate as high to be displayed first then  you need to change the DESC to ASC.

[freshness]

What I would like is to have the newest note used in the comparison with NOW() command for each agent in tbl_agents. At present the oldest note is used - changing ASC to DESC seems to have no effect, other than to change the order of the results in the screenshot I attached previously (not actually the number following the title). Does that make any sense?   ???

[Illusion]

try this

ORDER BY daysSinceLastUpdate DESC

if it doesn't work try this query

SELECT tbl_agents.*, MIN(datediff(NOW(), tbl_notes.added)) as daysSinceLastUpdate FROM tbl_agents,tbl_notes  WHERE tbl_agents.agent_id = tbl_notes.for_id GROUP BY tbl_agents.agent_id

if still you are not getting the expecting records post your table structure with sample data and expecting output.

we need to give other columns in the group by clause , I have not mentioned those columns there as I assume that rest of the columns have constant values.

 

[freshness]

Illusion, thank you so much!

 

The use of the MIN() command was enough to fix the problem   

 

Please see the attached image!

 

[attachment deleted by admin]

[freshness]

I spoke too soon!

 

Having tested things out, I can see the reason why I used LEFT JOIN in the first place - if there are no notes for an agent in tbl_agents, the result disappears (see attached).

 

Any ideas?

 

[attachment deleted by admin]

[freshness]

RIGHT!!

 

Fixed!

 

Here is the SQL - I just added a MIN() command to my original SQL query and it did exactly what I needed - thank you for your help Illusion.

 

SELECT ta.*, MIN(DATEDIFF(NOW(), tn.added)) as daysSinceLastUpdate FROM tbl_agents AS ta LEFT JOIN tbl_notes AS tn ON ta.agent_id = tn.for_id GROUP BY ta.agent_id ORDER BY ta.title ASC

 

I've attached an example of the query working correctly - 1 agent needs updating, 1 has no notes at all and the other was updated less than 3 days ago.

 

Many thanks!

 

[attachment deleted by admin]