Retrieving a federated database table with a constraint
I am trying to join two tables, but only showing a limited sum (2) of results from the combined table. Unfortunately I was not able to get the correct results. These are my tables:
Directions
id name
------------
1 Bahamas
2 Caribbean
3 Barbados
dumps
id name destination
---------------------------------
1 Adventure 1
2 For Kids 2
3 All Inclusive 3
4 Seniors 1
5 Singles 2
6 Disney 1
7 Adults 2
This is the request Ive tried:
SELECT
d.name as Destination,
s.name as Sailing
FROM destinations d
JOIN sailings s
ON s.destination = d.id
LIMIT 2
But this gives me 2 because of the limit:
Destination Sailing
-------------------------
Bahamas Adventure
Caribbean For Kids
SAMPLE: SQL FIDDLE
I would like it to LIMIT 2
only be applied to the joined tablesailings
Expected results:
Destination Sailing
-------------------------
Bahamas Adventure
Bahamas Seniors
Caribbean Singles
Caribbean For Kids
Can anyone point me in the right direction?
source to share
try it
select tmp.name as destination,d.name as sailings from (
SELECT
id,
name,
destination
FROM
(
SELECT
id,
name,
destination,
@rn := IF(@p = destination, @rn + 1, 1) AS rn,
@p := destination
FROM sailings
JOIN (SELECT @p := NULL, @rn := 0) AS vars
ORDER BY destination
) AS T1
WHERE rn <= 2
)tmp
JOIN (SELECT * FROM destinations limit 0,2) d
ON(tmp.destination=d.id)
I made 2 derived table and joined to it
source to share
Your problem is that you want to take the two tallest (or lowest) group members for each group in the table. In this case, you need the first two voyages for each target group.
The canonical way to handle this query in a database that supports analytic functions is to use ROW_NUMBER()
. But since MySQL does not support this, we can simulate it using session variables:
SET @row_number = 0;
SET @destination = NULL;
SELECT
t.Destination,
t.Sailing
FROM
(
SELECT
@row_number:=CASE WHEN @destination = Destination
THEN @row_number + 1 ELSE 1 END AS rn,
@destination:=Destination AS Destination,
Sailing,
id
FROM
(
SELECT s.id AS id, d.name AS Destination, s.name AS Sailing
FROM destinations d
INNER JOIN sailings s
ON s.destination = d.id
) t
ORDER BY
Destination,
id
) t
WHERE t.rn <= 2
ORDER BY
t.Destination,
t.rn;
Note that Barbados appears as one row because it only has one float in your sample data. If you also want to restrict only destinations with two or more voyages, this can also be done.
Output:
Demo here:
Rextester
source to share
Can you try
SELECT
d.name as Destination,
s.name as Sailing
FROM sailings s
JOIN (SELECT * from destinations LIMIT 2) d
ON s.destination = d.id
(You say you want to restrict the table sailings
, but I think you might want to restrict on the table destinations
based on the expected result, you can adjust as needed)
source to share