1

I habe a main table that i select from and a table with subelements that i select from in a join. Example:

person         skill           person_to_skill
id  | name     id | skill      id | p_id | s_id
------------   ------------    ----------------
1   | jim      1  | sewing     1  | 1    | 2
2   | peter    2  | cooking    2  | 2    | 1
3   | susan    3  | singing    3  | 2    | 3
4   | kevin                    4  | 3    | 1
                               5  | 3    | 2
                               6  | 4    | 3

So now we see, sim has only one skill, peter has two and so forth. Now if i select from person, koin skill and then also join person_to_skill, but i only want two persons. How do i manage to do so without grouping and thereby not getting all the skills?

Shortly: I want to select two persons from "person" with all their skills.

I tried just using LIMIT but that limits the result rows, not the persons. If i use GROUP BY i only get one skill per person. Is this possible without a subselect?

Any ideas anyone?

My Approach so far, changed to work with the example, looks like this:

SELECT p.id,p.name,s.skill
FROM person AS p
LEFT JOIN person_to_skill psk ON (psk.p_id = p.id)
LEFT JOIN skill s ON (s.id = psk.s_id)
ORDER BY p.name
LIMIT 0,2
Andresch Serj
  • 35,217
  • 15
  • 59
  • 101

2 Answers2

4

Limit number of persons at very beginning in subquery then join to them other tables as you've already done:

SELECT p.id,p.name,s.skill
FROM (select * from person ORDER BY name LIMIT 0,2) AS p
LEFT JOIN person_to_skill psk ON (psk.p_id = p.id)
LEFT JOIN skill s ON (s.id = psk.s_id)

Added after comment:

If you really can't use subqueries you can do it using two queries. Firstly select users ids:

select id from person ORDER BY name LIMIT 0,2

and then use those ids in next query:

SELECT p.id,p.name,s.skill
FROM person p
LEFT JOIN person_to_skill psk ON (psk.p_id = p.id)
LEFT JOIN skill s ON (s.id = psk.s_id)
WHERE p.id IN (ids from previous query)
Michał Powaga
  • 22,561
  • 8
  • 51
  • 62
  • I guess doing this without a subselect isn't possible so i probably have to accept your answer as the correct one right? – Andresch Serj Jan 13 '12 at 11:12
  • @AndreschSerj AFAIK there two possibilities mine that returns skill after skill in separated rows for first two persons or two persons and skills listed in one field showed by Michael Krelin - hacker. I don't understand what's wrong with subquery. – Michał Powaga Jan 13 '12 at 11:17
  • The problem is that i hve to use a class that you give the select, from, where and limit options and it has some logic which i can not use if i have subqueries. – Andresch Serj Jan 15 '12 at 15:25
  • 1
    @AndreschSerj then you'll have to do it using more than one query, check my edit. – Michał Powaga Jan 15 '12 at 19:17
  • Powaga: Thanks. I ended up deriving that class and writing more code than i wanted to write but it works now, using your first approach. Thanks a lot for your edit and all the effort. – Andresch Serj Jan 15 '12 at 20:44
  • Thanks this idea worked and saved my lot of time. I used second option because I am using doctrine2 and it may mess up the things if I use subSelect. – Jaskaran Singh Nov 27 '13 at 11:48
1

You can do something like

SELECT p.id, p.name, group_concat(s.skill separator ',')

and then group by person and limit the number of rows.

Michael Krelin - hacker
  • 138,757
  • 24
  • 193
  • 173
  • Well, apparently, even though i didn't point that out in the example, i do need the id's to the skills as well. But it is quite a good idea. Thank you! – Andresch Serj Jan 13 '12 at 11:11
  • 1
    Well, you have two options: 1. also aggregate skill ids (separately, or by bundling them with names). 2. go for Michal's answer. Both seem reasonable to me. – Michael Krelin - hacker Jan 13 '12 at 11:35