PHP MySQL join two tables

Question

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?

lc. · Accepted Answer · 2012-08-14 17:47:14Z

3

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)

edited Aug 14, 2012 at 17:47

answered Aug 14, 2012 at 16:58

lc.

117k21 gold badges163 silver badges190 bronze badges

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

Comments

Vinay · Accepted Answer · 2012-08-14 17:21:20Z

2

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

edited Aug 14, 2012 at 17:21

answered Aug 14, 2012 at 17:10

Vinay

2,5944 gold badges27 silver badges35 bronze badges

Comments

Maitrey Soparia · Accepted Answer · 2012-08-14 17:08:48Z

1

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.

answered Aug 14, 2012 at 17:08

Maitrey Soparia

212 bronze badges

Comments

fey · Accepted Answer · 2012-08-14 19:03:25Z

1

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.

answered Aug 14, 2012 at 19:03

fey

1,3471 gold badge11 silver badges20 bronze badges

Comments

Muthukumar M · Accepted Answer · 2012-08-14 17:04:29Z

0

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

edited Aug 14, 2012 at 17:04

answered Aug 14, 2012 at 16:57

Muthukumar M

1,13610 silver badges19 bronze badges

1 Comment

lc. Over a year ago

No, this would just do a complete Cartesian product of the two tables

Collectives™ on Stack Overflow

PHP MySQL join two tables

5 Answers 5

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related