CODEWITHSUNDEEP
mysql
user1537779
user1537779

Reputation: 2331

Select Distinct using a join

select DISTINCT cid,cname,tid
    from colors c
    left join tags t on t.tname = c.cname;

colors:

cid cname

1 red

2 blue

3 green

tags:

tid tname

1 red

2 red

3 blue

4 green

Current Result:

1 red 1

1 red 2

2 blue 3

3 green 4

Expected Result:

1 red 1

2 blue 3

3 green 4

Upvotes: 0

Views: 75

Answers (5)

eatSleepCode
eatSleepCode

Reputation: 4637

I dont have mysql to try this but this should help you. One more thing is you can not directly use tid into your query you need to take count of it.

select  cid, cname, count(tid)
    from Color c
    left join tags t on t.name = c.cname
GROUP BY cname;

here is sqlFiddle for it

Upvotes: 0

G one
G one

Reputation: 2729

Use group by

select cid,cname,tid
from colors c
left join tags t on t.tname = c.cname
group by cid,cname,tid;

Upvotes: 0

Joshua Kissoon
Joshua Kissoon

Reputation: 3309

GROUP BY tag name

select DISTINCT cid,cname,tid
from colors c
left join tags t on t.tname = c.cname;
GROUP BY t.name

Upvotes: 0

Drixson Oseña
Drixson Oseña

Reputation: 3641

This query should match your expected result

select cid,cname,tid
from colors c
left join tags t on t.tname = c.cname;
group by c.cid

Upvotes: 3

Abdul Manaf
Abdul Manaf

Reputation: 4888

select 
    cid, cname, tid
from
    colors c
        left join
    tags t ON t.tname = c.cname
GROUP BY c.cname,c.cid,t.tid;

Upvotes: 0

Related Questions