SQL Join... Maybe?

[The Little Guy]

I have 4 table:

artists

albums

lyric

artist_images

 

I have a query:

SELECT views,filename,title,albumName,artistName, lyric.id as lid
FROM lyric,albums,artists,artist_images
WHERE 
albums.artistID = artists.id
AND
lyric.albumID = albums.id
AND
artist_images.artistID = artists.id
ORDER BY views DESC LIMIT 10

 

I need to get artist_images.artistID and match it with artists.id but.... not all artists have an image, so what should I do here?

[pocobueno1388]

First off, you need to make it so the query and we know which fields go with which table. Look at your SELECT part

SELECT views,filename,title,albumName,artistName, l.id as lid

 

Now look at your FROM part

FROM lyric,albums,artists,artist_images

 

Which fields are coming from with tables?

 

Do something like this:

SELECT l.views, l.filename, a.title, art.albumName, ar.artistName, lyric.id as lid
FROM lyric l, albums a, artists ar, artist_images art

 

After you clear that up, then I can help you. If you want, just post each field you used, then tell us which table it belongs too.

[The Little Guy]

Table: artists

id

artistName

---

Table: albums

id

artistID

albumName

---

Table: lyric

id

albumID

title

lyric

views

---

Table: artist_images

id

artistID

filename

---

id is an auto incremented field

*ID matches the id from another table

 

If that makes sense

[yzerman]

I wrote a mini-tutorial about multi-table querys - let me know if this helps:

 

ok from what I understand, you have 2 tables, and you want to pull information from both of them.

 

This is actually less complicated then it seems.

 

Using MySql, the easiest way to accomplish this is using the period. Here is the first query, for users:

 

SELECT username FROM users

 

Thats it.

 

Now, lets say you just want the username and password from the users table, and the posts that that user made in the forums table

 

SELECT users.username, users.password, forums.topicid FROM users, forums WHERE forums.post_user = users.username AND users.username = 'yzerman'

 

This will output the following:

USERNAME

PASSWORD

TOPICID

yzermanMD5HASHPWD12345

 

ok, so basically, when you are doing querys, there are 3 levels you can select, update, insert, delete, etc with

 

Rows

 

SELECT * FROM users

 

Tables

 

SELECT users.username, users.password, forums.topicid FROM users, forums WHERE forums.post_user = users.username AND users.username = 'yzerman'

 

And finally databases.

 

Say your users table is in a database called bio, and your forums were in a database called community

 

To select from different databases, all you have to do is add another level to your query.

 

 

SELECT bio.users.username, bio.users.password, community.forums.topicid FROM bio.users, community.forums WHERE community.forums.post_user = bio.users.username AND bio.users.username = 'yzerman'

 

Just remember when your using querys like this, you always have to specify the table, and database if you select it in this fashion.

 

 

 

As far as the query its self goes, until know more about how you store each field - I really don't know. Lyrics not having an artistID field is throwing me off.

 

 

[The Little Guy]

I think I have to add an artistID field in the lyric table... because currently I am connecting to the table in a longer query:

 

Here is an example of one of my other queries:

 

SELECT title,artistName,lyric.id as sid 
FROM lyric,artists,albums
WHERE
lyric.albumID = albums.id
AND
albums.artistID = artists.id
ORDER BY lyric.id DESC LIMIT 10

[corbin]

SELECT views,filename,title,albumName,artistName, lyric.id as lid

FROM lyric,albums,artists,artist_images

WHERE

albums.artistID = artists.id

AND

lyric.albumID = albums.id

AND

artist_images.artistID = artists.id

ORDER BY views DESC LIMIT 10

 

*Thinks for a long time*

 

SELECT views, filename, title, albumname, artistName, lyric.id as lid FROM artists INNER JOIN albums ON albums.artistID = artists.id INNER JOIN lyric ON lyric.albumID = albums.id RIGHT JOIN artists_images ON artists_images.artistID = artists.id ORDER BY views DESC LIMIT 10

 

Ok... Assuming I typed that correctly, it should do what you want.

[The Little Guy]

Nope....

 

 

Right column: http://viplyrics.com/

 

If the artist doesn't have an image related to his/her name, nothing should show up next to their name.

 

Here is what the order should be:

1. Jane Fonda

Mickey Avalon

 

2. So Rich, So Pretty

Mickey Avalon

 

3. Tell Me

Bobby Valentino

 

4. If We Were A Movie

Hannah Montana

 

5. Public Affair, A

Jessica Simpson

 

6. Opheliac

Emilie Autumn

 

7. Phoebe Cates

Fenix Tx

 

8. Gothic Lolita

Emilie Autumn

 

9. Presidential

Young Dro

 

10. 'bout It

Young Joc

[corbin]

Bah.  I think the left join should've been right join.  As for it not showing an image, that could be PHP's fault, not the mysql query ;p.

[The Little Guy]

You mean "Right join" should have been "Left join" because that works.

 

 

Thank you for your help.

[yzerman]

I noticed another bug in your viplyrics site. You have the artists (the ones with First Last) sorted by first name, and not by last name.