CODEWITHSUNDEEP
sql
Jacob Windsor
Jacob Windsor

Reputation: 6980

Stop SQL returning the same result twice in a JOIN

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.

Upvotes: 1

Views: 4609

Answers (4)

Jacob Windsor
Jacob Windsor

Reputation: 6980

Thanks for all the answers, they helped a lot. I have made a query that works and manipulated the outcome with if statements in PHP.

Here it is;

SELECT 
T.url AS track_url,

S.status,

E.action,
E.ID,
E.timestamp,

A.name,
A.url AS artist_url

FROM events E

LEFT JOIN 
TRACKS T
ON T.ID = E.ID AND E.action = 'has uploaded a track.'

LEFT JOIN  
STATUS S
ON S.ID = E.ID AND E.action = 'has some news.'

LEFT JOIN 
ARTISTS A
ON A.ID = E.ID

ORDER BY E.timestamp DESC

The outcome of it is lots of NULL columns but that's fine as it works! IF the ID hasn't uploaded a track the relevant columns are NULL and the same applies to the other bits of the query. The ID is not repeated as only the ID in the events table is selected.

If you see any problems that I may have overlooked please shout about it!

Upvotes: 0

Kermit
Kermit

Reputation: 34063

Change this

WHERE S.ID AND T.ID = E.ID

To this

WHERE S.ID = E.ID AND T.ID = E.ID

Update

There are some individuals that are extremely touchy feely about using implicit join notation. To appease these individuals and as better practice, you should use explicit JOIN statements.

Upvotes: 0

pixe
pixe

Reputation: 1

try using group by;

SELECT T1.url, T1.id, S1.estatus, S1.id, E1.action, E1.id, E1.timestamp FROM t T1, s S1, e E1 WHERE S1.id AND T1.id = E1.id GROUP BY S1.id ORDER BY E1.timestamp DESC;

Upvotes: 0

MatBailie
MatBailie

Reputation: 86775

Don't use 20 year out of date syntax. Although many RDBMS support FROM a, b, c style syntax it has been deprecated by all systems that I've worked with. (I haven't worked with everything, but it's really strongly advisable to not use this style.)

Instead use ANSI-92 standard JOIN syntax. Then it's much harder to get things wrong...

SELECT
  *
FROM
  status S
INNER JOIN
  tracks T
    ON T.ID = S.ID
INNER JOIN
  events E
    ON E.ID = S.ID
ORDER BY
  E.timestamp DESC

Also, you example data suggest that you have the following in table S...

 Status | ID
--------+----
 hello  | 4
 bye    | 3
 more   | 4

ID = 4 has two rows. If you really want each row in T to only join on one row in S, which row should you pick? Should one be deleted, or do you have some other logic/condition that you can use to pick one of the two?

Upvotes: 2

Related Questions