[SOLVED] Tricky SQL issue, Ordering through other table.

[Petsmacker]

I'm trying to retrieve information from one database and use it to order the details of another.

First I'll give a basic interpretation of my database situation. (Bold words are table names)

 

watch -

user_id | watch_id

 

events -

user_id | time

 

 

What I'm attempting to do, is get the watch_id's from watch and find its first user_id equivalent in events and then subsequently order the results by time desc.

 

For example:

watch -

1 | 34

1 | 56

 

events -

56 | 1235278075

75 | 1235235986

34 | 1235280000

34 | 1000050000

 

So assuming my User_id is 1, I need to get the user_id's in events that are in my watch_id's in watch and then have it so that in a query, they're ordered by time DESC.

In this case, it would give me the results:

34 | 1235280000

56 | 1235278075

 

I hope I've explained the situation properly. Any help would be greatly appreciated. Thanks

[corbin]

SELECT * FROM watch w JOIN events e ON e.user_id  = w.user_id WHERE w.user_id = <user_id> ORDER BY w.time;

[Petsmacker]

Hey thank you, it almost works, I had to fix

ORDER BY w.time

because it brought up an error. I changed it to e.time. I also added DESC at the end.

"SELECT * FROM watch w JOIN events e ON e.user_id  = w.user_id WHERE w.user_id = 1 ORDER BY e.time DESC;"

 

--------

Assuming the DB still looks like this:

 

watch -

user_id | watch_id

1 | 34

1 | 56

 

events -

user_id | time

56 | 1235278075

75 | 1235235986

34 | 1235280000

34 | 1000050000

 

--------

Running the revised query you gave me creates results like this:

 

34 | 1235280000

56 | 1235280000

34 | 1235280000

 

All  the times are the same.

--------

 

I edited the query slightly to see if I could get it working:

SELECT * FROM watch w JOIN events e ON e.user_id  = w.watch_id WHERE w.user_id = 1 ORDER BY e.time DESC

But now its giving me:

 

Results:

34 | 1235280000

56 | 1235278075

34 | 1000050000

 

So it is only giving me the ID's in watch and they are in order but they're not distinct.

I've had numerous goes at getting it to work but when I add DISTINCT, it then ignores the ORDER BY time DESC

---

 

I hope this is clear, I'm so stuck.

[corbin]

Hrmmm, try:

 

SELECT w.user_id, MAX(e.time) FROM watch w JOIN events e ON e.user_id  = w.watch_id GROUP BY e.user_id WHERE w.user_id = 1 ORDER BY e.time DESC

[Petsmacker]

 

Your query gave me this error:

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 'WHERE e.user_id = 1 ORDER BY e.time DESC

 

I placed the WHERE section behind the GROUP BY:

SELECT w.user_id, MAX(e.time) FROM watch w JOIN events e ON e.user_id  = w.watch_id WHERE w.user_id = 1 GROUP BY e.user_id ORDER BY e.time DESC

But it returned 0 rows which is incorrect.

 

Aargh!

[Petsmacker]

I tweaked the code and you gave me, and guess what - we have workidge!

 

Here is the full query for anybody that needs it.

SELECT e.user_id, MAX(e.time) FROM watch w JOIN events e ON e.user_id  = w.watch_id WHERE w.user_id = 1 GROUP BY e.user_id ORDER BY MAX(e.time) DESC

 

Thank you so much corbin.