MySql query for complicated select

Question

I have these tables:

Users, A table contains the users:

+--------------------------
| ID    |     Name        |
+--------------------------

Dogs, A table contains the dogs (Last change is a time stamp telling when this dog status last changed):

+---------------------------------------------------------
| ID    |       Name       |        Last_Changed_At      |
+---------------------------------------------------------

Users_Dogs_Relations, A table that describe which dog belong to which user, where multiple dogs can be belong to one user, multiple users can have the same dog, and multiple dogs can be belong to multiple users. (In short, any user can be related to any dog):

+------------------------------------
|   UserID        |     DogID       |
+------------------------------------

Users_Friend_Relation, A table describe which user is friend of which user:

+------------------------------------
|   UserID1     |       UserID2     |
+------------------------------------

What i want to do is to tell Steve (one of the users), all the name of the dogs that his friends have (ordered by Last_Changed_At from a given timestamp).

for example, Steve with given time 11:23:00, will query all the dogs of Steve's friends ordered by the timestamp and only below 11:23:00. Oh, And i dont want that dogs will appear twice. Any one can give me some clue?

can you give sample records?

John Woo
– John Woo

2013-04-07 14:23:00 +00:00
Commented Apr 7, 2013 at 14:23 — John Woo
– John Woo, Commented Apr 7, 2013 at 14:23

MarcinJuraszek · Accepted Answer · 2013-04-07 14:24:48Z

1

You have to join all your tables on correct columns

Users to Users_Friend_Relation to get friends of Steve
Users_Dogs_Relations to Users_Friend_Relation to get dogs of *Steve*s friends
Dogs to Users_Dogs_Relations to get these dogs names

and specify your where conditions.

SELECT DISTINCT d.Name
FROM Users u1
JOIN Users_Friend_Relation f ON u1.ID = f.User1ID
JOIN Users_Dogs_Relations udr ON udr.UserID = f.User2ID
JOIN Dogs d ON udr.DogID = d.ID
WHERE u1.Name = Steve
AND d.Last_Changed_At < '11:23:00'

answered Apr 7, 2013 at 14:24

MarcinJuraszek

126k15 gold badges200 silver badges267 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySql query for complicated select

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related