JOIN with 2 Tables (Voting) Mysql, PHP

Question

Hello I have 3 Tables but i need only help with 2 Tables.

The first Table is champions. In this Table are over 100 names.

The second Table is champion_names there are nicknames for the first Table.

The third Table is champion_names_vote. There are the votes for the nicknames. Thumb up if I like any nickname or thumb down.

On my website I have a site where I can see a list full of Names (Table 1). There are 2 columns. In the first is the normal name (Table 1) in the second the nickname (Table 2). Now I want to show the best nickname in column 2. Actually it's random but I only want to show the best nickname.

I can show all Names that's not the problem. But if I want to show only the best nicknames I don't know how.

Table 2: id(AI), champ_id(this is the id for Table 1), sender_id, name

Table 3: id(AI), userid, name_id(Table 2 ID), like_dislike

like_dislike = 1 is like, -1 is dislike and 0 is nothing.

Example:

Table 2: 50, 2, 4, Test
Table 3: 1, 3, 50, 1,

I liked the name of Table 2. So the name_id in Table 3 is the id of Table 2

So how can I do this with JOIN? Can you help me please.

score 0 · Answer 1 · answered Oct 15 '14 at 15:53

something like this should do the trick.

Idea is to Analyse your votes per nickname first. Then select the Maximum vote-score by HAVING:

SELECT voteResult.name, vote as winnerVoteScore
FROM(
    select a.name_id, b.name,
    sum(IF(a.like_dislike = 1, 1, IF(a.like_dislike = 2, -1, 0 ))) as vote
    FROM champion_names_vote as a JOIN champion_names as b 
    ON a.name_id = b.ID
    GROUP BY 1
) as voteResult
GROUP BY 1 
HAVING vote = max(vote)

JOIN with 2 Tables (Voting) Mysql, PHP

1 Answers1