Espen Espen - 16 days ago 6
SQL Question

Getting single entrances from a JOIN

I have these two tables:

stores:
+----------+--------------+
| store_id | store_number |
+----------+--------------+
| 1 | 231 |
| 2 | 132 |
| 3 | 321 |
+----------+--------------+

entrances:
+-------------+----------+-----------------+
| entrance_id | store_id | entrance_number |
+-------------+----------+-----------------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 1 |
| 4 | 3 | 1 |
| 5 | 3 | 2 |
| 6 | 3 | 3 |
+-------------+----------+-----------------+


The
entrance_number
is a sequence number for each store starting on
1
. What I want to do is
JOIN
these two tables and then add
1
to the
entrance_number
so I get the next
entrance_number
for each store.

Like this:

SELECT
store_number,
entrance_number + 1
FROM
stores
JOIN
entrances
ON
stores.store_id = entrances.store_id


which gives this result:

+--------------+-----------------+
| store_number | entrance_number |
+--------------+-----------------+
| 231 | 2 |
| 231 | 3 |
| 132 | 2 |
| 321 | 2 |
| 321 | 3 |
| 321 | 4 |
+--------------+-----------------+


What I want is getting the single top entrance_number for each store so the result is this:

+--------------+-----------------+
| store_number | entrance_number |
+--------------+-----------------+
| 231 | 3 |
| 132 | 2 |
| 321 | 4 |
+--------------+-----------------+


Which gives the next
entrance_number
for each store.

How can I use
JOIN
to get this "single entrance" from entrance?

Answer

You need to apply Group By and Max.

 SELECT
        store_number,
        max(entrance_number + 1)
    FROM
        stores
    JOIN
        entrances
    ON
        stores.store_id = entrances.store_id
        group by store_number