CODEWITHSUNDEEP
phpmysqljoin
Harinder
Harinder

Reputation: 1257

Join 2 tables with multiple DISTINCT

i an trying to get values from table using DISTINCT but this is not working, here is wt i am doing

SELECT top100albums.songs, english_fm.artist, 
    english_fm.album, english_fm.image
FROM top100albums, english_fm
WHERE top100albums.songs = english_fm.album

i am confused how can i use DISTINCT in this query can i use it like this ...

 SELECT  top100albums.songs, english_fm.DISTINCT(artist), 
     english_fm.DISTINCT(album), english_fm.DISTINCT(image)
 FROM top100albums, english_fm
 WHERE top100albums.songs = english_fm.album

this is wrong ...

i want to get DISTINCT (not repeated result of these rows artist,album,image from table english_fm, so how can i do that

Upvotes: 0

Views: 72

Answers (5)

Ronak Vyas
Ronak Vyas

Reputation: 573

This is fully optimized query with needed result.

Try this

SELECT ta.songs, ef.artist, ef.album, ef.image
FROM english_fm AS ef
INNER JOIN top100albums AS ta ON ef.album = ta.songs
GROUP BY ef.artist, ef.album, ef.image;

Hope, this will helpful

Upvotes: 1

Slowcoder
Slowcoder

Reputation: 2120

DISTINCT applies for the rows and not for individual columns.

So use `GROUP BY' for your requirement.

SELECT top100albums.songs, english_fm.artist, english_fm.album), english_fm.image
 FROM top100albums, english_fm
 WHERE top100albums.songs = english_fm.album GROUP BY english_fm.artist, english_fm.album, english_fm.image;

Upvotes: 0

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33391

DISTINCT keyword tells engine to return unique cortege (result set record). See usage in documentation.

SELECT Syntax

Upvotes: 0

C.O.D.E
C.O.D.E

Reputation: 902

SELECT top100albums.songs, english_fm.artist, english_fm.album, english_fm.image
FROM top100albums, english_fm
WHERE top100albums.songs = english_fm.album
GROUP BY top100albums.songs, english_fm.artist, english_fm.album, english_fm.image

Upvotes: 2

echo_Me
echo_Me

Reputation: 37243

try this 

   SELECT top100albums.songs, english_fm.artist, english_fm.album, english_fm.image
   FROM top100albums, english_fm
   WHERE top100albums.songs = english_fm.album
   GROUP BY english_fm.artist, english_fm.album, english_fm.image

insetead of distinct use group by

Upvotes: 2

Related Questions