Jump to content

[SOLVED] LEFT JOIN ORDER BY not working


freshness

Recommended Posts

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?

Link to comment
https://forums.phpfreaks.com/topic/55059-solved-left-join-order-by-not-working/
Share on other sites

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]

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? :)  ???

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.

 

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]

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.