CODEWITHSUNDEEP
mysqlleft-joinlimit
stramin
stramin

Reputation: 2390

mysql - Limit a query with left join

I have 2 tables like this:

Table person

id | name
---------
1  | john
2  | mike
3  | carl
4  | keny
5  | anna

Table vehicle

owner | vechicle
----------------
1     | RTA457
3     | GSW684
3     | GKI321
3     | SNE798
5     | YTT662

So, I want to make a query joining both tables, something like this:

SELECT * FROM person LEFT JOIN vehicle ON person.id=vehicle.owner

Getting these results

id | name | owner | vechicle
----------------------------
1  | john | 1     | RTA457
2  | mike | NULL  | NULL
3  | carl | 3     | GSW684
3  | carl | 3     | GKI321
3  | carl | 3     | SNE798
4  | keny | NULL  | NULL
5  | anna | 5     | YTT662

Finally, I want to limit it to 3 persons, showing all their vehicles, like this:

id | name | owner | vechicle
----------------------------
1  | john | 1     | RTA457
2  | mike | NULL  | NULL
3  | carl | 3     | GSW684
3  | carl | 3     | GKI321
3  | carl | 3     | SNE798

There is any way to do it?

Upvotes: 0

Views: 72

Answers (3)

jm.
jm.

Reputation: 23714

Didn't try it, but something like this:

SELECT * FROM person
LEFT JOIN vehicle ON person.id = vehicle.owner
WHERE person.id IN (SELECT ID FROM PERSON LIMIT 3);

Upvotes: 1

ydoow
ydoow

Reputation: 2996

May help with a subquery

SELECT
    *
FROM
    (SELECT * FROM person LIMIT 3) t
LEFT JOIN vehicle ON t.id = vehicle.owner

Upvotes: 2

Kulasangar
Kulasangar

Reputation: 9434

You could simply have your query as such:

SELECT * FROM person LEFT JOIN vehicle ON person.id=vehicle.owner LIMIT 10;

This SO could be handy as well. Hope this helps!

Upvotes: 0

Related Questions