CODEWITHSUNDEEP
mysqlsql
lewicki
lewicki

Reputation: 479

SQL - Select distinct rows and join them with another table to get data

I have 2 tables:

1) person (person_id, person_name)

2) cars (car_id, person_id)

I want to get all the people's names that have cars with no duplicates.

This is what I have come up with:

SELECT person.person_name, cars.person_id
FROM cars
INNER JOIN person
ON person.person_id=cars.person_id

But I don't want duplicates, so I need to incorporate it using something like this:

SELECT DISTINCT person_id FROM cars

Upvotes: 2

Views: 7025

Answers (5)

lewicki
lewicki

Reputation: 479

SELECT p.person_id AS person_id, p.name AS name FROM person p, cars c    
WHERE p.person_id = c.person_id    
GROUP BY b.brand_id

Upvotes: 0

Chandu
Chandu

Reputation: 82913

Try this:

SELECT  p.person_id, p.person_name
   FROM  person p
WHERE     EXISTS
(
    SELECT 1
      FROM cars c
    WHERE c.person_id = p.person_id
)

Upvotes: -1

Arthur Frankel
Arthur Frankel

Reputation: 4705

SELECT DISTINCT(person.person_name) 
FROM person, cars 
WHERE person.person_id = cars.person_id

Upvotes: -1

Jason McCreary
Jason McCreary

Reputation: 72991

SELECT DISTINCT person.person_name, person.person_id
FROM cars
INNER JOIN person
ON person.person_id=cars.person_id

Although there may be more performant alternatives.

Upvotes: 0

Randy
Randy

Reputation: 16677

select person_name from person
where person_id in ( select person_id from cars )

Upvotes: 5

Related Questions