Stop SQL returning the same result twice in a JOIN
- by nbs189
I have joined together several tables to get data i want but since I am new to SQL i can't figure out how to stop data being returned more than once.
her's the SQL statement;
SELECT
T.url,
T.ID,
S.status,
S.ID,
E.action,
E.ID,
E.timestamp
FROM tracks T, status S, events E
WHERE S.ID AND T.ID = E.ID
ORDER BY E.timestamp DESC
The data that is returned is something like this;
+----------------------------------------------------------------+
| URL | ID | Status | ID | action | ID | timestamp |
+----------------------------------------------------------------+
| T.1 | 4 | hello | 4 | has uploaded a track | 4 | time |
| T.2 | 3 | bye | 3 | has some news | 3 | time |
| t.1 | 4 | more | 4 | has some news | 4 | time |
+----------------------------------------------------------------+
That's a very basic example but does outline what happens. If you look at the third row the URL is repeated when there is a different status.
This is what I want to happen;
+-------------------------------------------------------+
| URL or Status | ID | action | timestamp |
+-------------------------------------------------------+
| T.1 | 4 | has uploaded a track | time |
| hello | 3 | has some news | time |
| bye | 4 | has some news | time |
+-------------------------------------------------------+
Please notice that the the url (in this case the mock one is T.1) is shown when the action is has uploaded a track. This is very important. The action in the events table is inserted on trigger of a status or track insert. If a new track is inserted the action is 'has uploaded a track' and you guess what it is for a status. The ID and timestamp is also inserted into the events table at this point.
Note: There are more tables that go into the query, 3 more in fact, but I have left them out for simplicity.
