CODEWITHSUNDEEP
mysqlinner-join
Bin Salem
Bin Salem

Reputation: 26

How i Can do inner join with mysql

songs_albums.cat = songs_singers.id

CAT = ID

How i can do JOIN to get all albums singers ?

$qr=mysql_query("
select songs_albums.*
     , songs_singers.name as singer_name
     , songs_singers.id singer_id
     , songs_singers.page_name singer_page_name
     , songs_singers.img singer_img  
  from songs_albums
     , songs_singers 
 where songs_singers.active=1
   and songs_albums.cat=songs_singers.id 
 limit 8
") ;

Upvotes: 0

Views: 54

Answers (2)

xQbert
xQbert

Reputation: 35323

You already are if we assume the song_singers.Id is the same as the song_albums.cat. You're just using the ANSI 89 standard instead of the ANSI 92 standard.

A few hints:

  1. Limit w/o order by generally doesn't make a lot of sense. Say I have records 1-10 in a table. and I limit at 8. I could get those any combination of 8. it may not always be the same 8, and it may not always be in the same order.
  2. Use table aliases to make reading/typing easier.
  3. Avoid use of * in a select instead spell out the fields to get only what you need. Retrieving more data than needed increases network traffic, slows query retrieval efforts and adds overhead to calling server. * is generally acceptable in troubleshooting/debugging or starting to understand data/structure; but not generally wise in code going to production.

.

ANSI-89 standard:

SELECT SA.*
     , ss.name as singer_name
     , ss.id as singer_id
     , ss.page_name as singer_page_name
     , ss.img as singer_img  
FROM songs_albums SA
   , songs_singers SS
WHERE ss.active=1 
  and sa.cat=ss.id 
ORDER BY sa.ID, SS.ID
LIMIT 8

ANSI-92 Standard using inner join.

SELECT SA.*
     , ss.name as singer_name
     , ss.id as singer_id
     , ss.page_name as singer_page_name
     , ss.img as singer_img  
FROM songs_albums SA
INNER JOIN song_singers SS
  on sa.cat=ss.id
WHERE ss.active=1 
ORDER BY sa.ID, SS.ID
LIMIT 8

Upvotes: 1

Harry baldaniya
Harry baldaniya

Reputation: 167

you can assign album id to particular singer.so now to get all singers of all albums left join query is like that

$sql = "select sa.*,s.id,s.name as singer_name from song_singers as s left join songs_album as sa ON sa.id=s.album_id where s.active=1";

Upvotes: 0

Related Questions