CODEWITHSUNDEEP
mysql
Jay
Jay

Reputation: 373

How to do this inner join query in mysql

Here is my query

select * from table1 inner join table2 on table1.typeId=table2.typeId;

This works fine if the typeId in table2 is'nt repeated more than once but gives duplicate rows if typeId exists more than once. Is there any way to avoid duplicate rows?? 

table1  -- Name typeId
           Jay  1
           roy  2
table2  -- Type typeId
           L    1
           M    1
           N    2
           K    2

Expected output Jay  1
Output getting is Jay  1
                  Jay  1

Upvotes: 0

Views: 229

Answers (3)

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171371

Below is an example where the maximum typeid gets returned when there is than one match:

select t1.Name, max(t1.TypeID) as TypeID
from table1 t1
inner join table2 t2 on t1.typeId = t2.typeId
group by t1.Name

If you want to return all records from table1 even when there is no match in table 2, you can this:

select t1.Name, max(t1.TypeID) as TypeID
from table1 t1
group by t1.Name

Upvotes: 0

A.B.Cade
A.B.Cade

Reputation: 16905

select DISTINCT table1.Name, table2.typeId
from table1 inner join table2 on table1.typeId=table2.typeId;

Upvotes: 1

Ben English
Ben English

Reputation: 3918

You need to join on multiple columns to avoid duplication (If your rows are identical then use SELECT DISTINCT)

select * from table1 inner join table2 on table1.typeId=table2.typeId AND table1.otherId = table2.otherId;

Add as many join conditions as possible until you get a unique result. If there are multiple values that match your conditions then the database has no way of knowing which row you want to select, so you will have to specify how you want to choose that single row in the ON clause of your INNER JOIN.

Upvotes: 0

Related Questions