CODEWITHSUNDEEP
mysqlsql
candlejack
candlejack

Reputation: 1209

How to limit a field in a query in MySQL

I have two tables like this:

TableOne(id, name, attr)
{
    1, Mark, A
    2, Peter, B
    3, John, A
    4, Mary, A
}
TableTwo(id, id_TableOne, path)
{
    1, 1, one.png
    2, 1, two.png
    3, 1, three.png
    4, 3, foo.png
    5, 3, bar.png
}

SELECT TableOne.name, TableTwo.path
FROM TableOne, TableTwo
WHERE TableOne.attr = 'A'
AND TableOne.id = TableTwo.id_TableOne

That outputs:

{
    Mark, one.png
    Mark, two.png
    Mark, three.png
    John, foo.png
    John, bar.png
}

I want to limit the subquery, and show only one path per name. The desired output would be:

{
    Mark, one.png
    John, foo.png
    Mary, NULL
}

I tried doing LIMITS, and write a select within another, but I did not get the result I want, I also read something that worries me

"MySQL does not support LIMIT in subqueries for Un Certain subquery operators"

from http://dev.mysql.com/doc/refman/5.0/en/subquery-restrictions.html

Can anyone guide me to solve my problem? Please.

Upvotes: 1

Views: 55

Answers (3)

Ôrel
Ôrel

Reputation: 7622

You need to group, and use and aggregation function

SELECT TableOne.name, MIN( TableTwo.path ) 
FROM TableOne
LEFT JOIN TableTwo ON TableOne.id = TableTwo.id_TableOne
GROUP BY name

Upvotes: 1

fthiella
fthiella

Reputation: 49049

Which path do you want to return if there are more than one? If any path is fine, then you can use this query:

SELECT TableOne.name, TableTwo.path
FROM
  TableOne INNER JOIN TableTwo
  ON TableOne.id = TableTwo.id_TableOne
WHERE
  TableOne.attr = 'A'
GROUP BY
  TableOne.name

if you want to return the first path, then you need to use this:

SELECT TableOne.name, TableTwo.path
FROM
  TableOne INNER JOIN (
    SELECT   id_TableOne, MIN(id) as min_id
    FROM     TableTwo
    GROUP BY id_TableOne
  ) m ON TableOne.id = m.id_TableOne
  INNER JOIN TableTwo
  ON TableOne.id = TableTwo.id_TableOne
     AND m.min_id = TableTwo.id
WHERE TableOne.attr = 'A'

Upvotes: 1

Egor Lyashenko
Egor Lyashenko

Reputation: 797

Try this:

SELECT TableOne.name, MIN(TableTwo.path)
FROM TableOne LEFT JOIN TableTwo ON (TableOne.id = TableTwo.id_TableOne)
WHERE TableOne.attr = 'A'
GROUP BY TableOne.name

Upvotes: 1

Related Questions