CODEWITHSUNDEEP
phpsql
user1277467
user1277467

Reputation: 199

SQL Inner Join - retrieving wrong ids

i try to retrieve datas from my database with following sql.

select mc.ADD_DATE,mc.MOVIE_ID,mc.COMMENT,m.ID,m.NAME 
from MOVIE_COMMENT as mc 
INNER Join MOVIE as m ON  m.ID=mc.MOVIE_ID  
WHERE user_ID = " . $getUser->id . " 
    and STAT='onayli' 
ORDER BY mc.ADD_DATE DESC LIMIT " . $commentCount . "

I use Inner join because i also retrieve movie names from another table.

mc = MOVIE_COMMENT table
m = MOVIE Table

My problem is that when i try to retrieve mc ids, it gives me the movie ids why?

Upvotes: 0

Views: 81

Answers (4)

lubar
lubar

Reputation: 2639

You're returning the ids from the MOVIE_COMMENT table as mc.MOVIE_ID. Thus, when you try to access there (e.g., from PHP), you need to access MOVIE_ID. Asking for ID will return m.ID, the ids from the MOVIE table.

Upvotes: 0

James Osborn
James Osborn

Reputation: 1275

If you want the Movie_Comment IDs, you need to return the mc.ID field.

SELECT mc.ID as Movie_Comment_ID, mc.ADD_DATE, mc.MOVIE_ID, mc.COMMENT, m.ID, m.NAME 
FROM MOVIE_COMMENT as mc 
INNER Join 
MOVIE as m 
ON  m.ID = mc.MOVIE_ID

Upvotes: 3

Kosta
Kosta

Reputation: 1867

You have m.ID in your select list. You probably want mc.ID.

Upvotes: 1

beny23
beny23

Reputation: 35018

Have you tried replacing m.ID with mc.ID in your select clause?

Upvotes: 0

Related Questions