CODEWITHSUNDEEP
mysqlsqlselectjoininner-join
Francois Lemieux
Francois Lemieux

Reputation: 59

Inner join return only the first matching Row

I've looked everywhere in the site and over google and did not found the answer I was looking for, I have 2 tables with specific info on each and the report gives duplicates I would need to get the name, the feedback ID and the site with a maximum of 1 feedback ID.

Here are my 2 tables

Table: Feedback 

userID              | ID 
john.smith          |1
george.wilson       |2
justin.example      |3
justin.example      |4
juliana.something   |5
george.wilson       |6

Table: Users (other info on this table give a reason for the duplicates )

UserID              |Site
george.wilson       |location 1
george.wilson       |location 1
george.wilson       |location 1
john.smith          |Location 2
john.smith          |Location 2
juliana.something   |Location 3
justin.example      |Location 4
justin.example      |Location 4

Current Query

SELECT feedback.userID,  feedback.id, Users.Site        
FROM feedback       
INNER JOIN users ON     
feedback.userID = users.userid      
WHERE feedback.userID <> 'x'

Current result

UserID              |ID | Sites
john.smith          |1  |Location 2
john.smith          |1  |Location 2
george.wilson       |2  |location 1
george.wilson       |2  |location 1
george.wilson       |2  |location 1
justin.example      |3  |Location 4
justin.example      |3  |Location 4
justin.example      |4  |Location 4
justin.example      |4  |Location 4
juliana.something   |5  |Location 3
george.wilson       |6  |location 1
george.wilson       |6  |location 1
george.wilson       |6  |location 1

Expected results

UserID              |ID | Sites
john.smith          |1  |Location 2
george.wilson       |2  |location 1
justin.example      |3  |Location 4
justin.example      |4  |Location 4
juliana.something   |5  |Location 3
george.wilson       |6  |location 1

My query has been minimize, more content is required but the error comes from my "inner join" users, can anywone help me with this issue please? ( i am using My Sql Workbench 6.0 ) thank you in advance!

Upvotes: 0

Views: 6481

Answers (2)

rhoward
rhoward

Reputation: 1

The problem is the duplicated data. SQL doesn't know or care why it's been duplicated in the table, it's just going to return everything that matches your query exactly. It's a really simple query so just add DISTINCT to eliminate the duplicate rows without any extra weird joins.

SELECT DISTINCT feedback.userID,  feedback.id, users.Site
FROM feedback, users
WHERE feedback.userID = users.userid      
AND feedback.userID <> 'x'

Upvotes: 0

Mureinik
Mureinik

Reputation: 311073

You could join the users table with a query on feedback, using a limit clause to limit the nuber of rows:

SELECT     f.userID, f.id, u.site
FROM       feedback f
INNER JOIN (SELECT   userID, site
            FROM     users
            ORDER BY site DESC
            LIMIT    1) u ON g.userID = u.userID
WHERE      f.userID <> 'x'

Upvotes: 1

Related Questions