CODEWITHSUNDEEP
phpmysql
austinh
austinh

Reputation: 1071

PHP MySQL join two tables

I have two tables, songs and history. The songs table looks like:

ID | title      | artist      | duration
1  | some title | some artist | 83592

The history table looks like:

ID | title      |   artist    | duration | date_played
5  | some title | some artist | 83592    | 2012-08-08 11:22:00

How would I echo the ID from the songs table if the title and the artist from the most recent entry in the history table matches?

I tried SELECT * FROM history JOIN songs ON title=songs.title AND artist=songs.artist ORDER BY date_played DESC LIMIT 0, 1 but that didn't work. Any ideas?

Upvotes: 1

Views: 4837

Answers (5)

fey
fey

Reputation: 1348

You can use

SELECT   songs.id
FROM     songs,
         history
WHERE    songs.title = history.title
AND      songs.artist = history.artist
ORDER BY history.date_played DESC

or

SELECT     songs.id
FROM       songs
INNER JOIN history ON  history.title = songs.title
                   AND history.artist = songs.artist
ORDER BY   history.date_played DESC

but it would better if you organised your tables as suggested by Vinay.

Upvotes: 1

lc.
lc.

Reputation: 116438

SELECT s.ID
FROM songs s
INNER JOIN (SELECT * FROM history h ORDER BY date_played DESC LIMIT 1) lastHistory
ON lastHistory.title = s.title AND lastHistory.artist = s.artist

(Sqlfiddle)

Upvotes: 3

Vinay
Vinay

Reputation: 2594

SELECT * FROM history A INNER JOIN songs B 
ON A.title=B.title AND A.artist=B.artist 
ORDER BY A.date_played DESC

My suggestion is in history table you can use song id of songs table instead of artist and title.

Table: Songs

ID | title      | artist      | duration
1  | some title | some artist | 83592

Table: History

ID | songid  | date_played
5  | 1       | 2012-08-08 11:22:00

So that you can bring some optimization in your schema.

Then you can try this query.

SELECT * FROM history A INNER JOIN songs B 
ON A.songid=B.ID ORDER BY A.date_played DESC

Upvotes: 2

Maitrey Soparia
Maitrey Soparia

Reputation: 21

SELECT songs.* 
FROM songs, (SELECT * FROM history ORDER BY DESC date_played LIMIT 1) hist_view
WHERE songs.title = hist_view.title 
    AND songs.artist = hist_view.artist

The above query creates and inline view of the most recently played song called hist_view (using LIMIT and ORDER BY DESC). It then joins with songs table to find the matching song on the basis of artist and title.

I would suggest you add something like song_id in the history table as a foreign key.

Upvotes: 1

Muthukumar M
Muthukumar M

Reputation: 1126

Check this

select songs1.id,history1.title,history1.artist 
from songs as songs1,history as history1
order by date_diplayed desc

i thing this query solve your problem

Upvotes: 0

Related Questions