CODEWITHSUNDEEP
mysqlgreatest-n-per-group
luke
luke

Reputation: 3599

Join only with first row that refers to parent table

I have following tables:

A          B
id | a |   id | b
--------   -------
1  | . |   1  | 1
--------   -------
2  | . |   1  | 2
--------   -------
3  | . |   2  | 1
           -------
           2  | 2

B.id is a foreign key which references on A.id. I would like to display A.id, A.a, B.b. But columns from table A should be joined only with the first row from table B which refers to A.id. I also want to select rows from table A which don't have corresponding row in table B. So the result should looks like this:

A.id | A.a | B.b
----------------
  1  |  .  |  1 
----------------
  2  |  .  |  1
----------------
  3  |  .  |

Thanks in advance for any help.

Upvotes: 0

Views: 220

Answers (2)

Rebika
Rebika

Reputation: 199

Try this -:

SELECT A.id, A.a, B.b
FROM A
LEFT JOIN (
            SELECT b1.id, b1.b
            FROM B b1, B b2
            WHERE b1.b < b2.b
          )B
ON A.id = B.id
GROUP BY A.id
ORDER BY A.id asc

Upvotes: 0

zessx
zessx

Reputation: 68820

Simply use GROUP BY and LEFT JOIN clauses :

SELECT A.id, A.a, B.b
FROM A
LEFT JOIN B ON B.id = A.id
GROUP BY A.id
ORDER BY A.id ASC

Upvotes: 1

Related Questions