CODEWITHSUNDEEP
mysqljoindistinct
Steven
Steven

Reputation: 19455

How do I return distinct rows when using join?

I have the following query that returns 1738 rows:

select a.street, b.id 
from TableA a
left join TableB b on a.city = b.city
order by a.street

Executing the following query, returns 1073 rows:
select distinct street from TableA

How can I return distinct rows in my first query?
I tried using select distinct a.street, b.id, but this returns 1090 rows.

Do I need another join?

Upvotes: 2

Views: 112

Answers (2)

slaakso
slaakso

Reputation: 9080

You should use GROUP BY only with aggregate function. MySQL will not flag it as an error, but you will lose some data while doing so.

As for your query: if you have 1073 distinct streets and you getting 1090 rows while doing "distinct a.street, b.id" it means that there are more than one b.id for some of the streets. You can either get all of them (and get 1090 rows) or take just some of them. To get 1073 rows and only one b.id for each street use:

select a.street, max(b.id)
from TableA a
left join TableB b on a.city = b.city
group by a.street
order by a.street

Upvotes: 0

Kevin Holditch
Kevin Holditch

Reputation: 5313

select a.street, b.id 
from TableA a
left join TableB b on a.city = b.city
group by a.street, b.id
order by a.street

Upvotes: 5

Related Questions