How can I get only one row per record in master table?
it is possible to get only one row per record in a multitable query?
I have this three tables:
APPLES
ID | APPLE
----------
1 | RED
2 | YELLOW
3 | GREEN
FARMS
ID | FARM
--------------------
B1 | GEORGE'S FARM
B2 | JOHN'S FARM
FARM_APPLES
FARM | APPLE
---------------
B1 | 1
B1 | 2
B1 | 3
B2 | 1
B3 | 3
With this tables I need this result:
FARM_NAME | APPLE_1 | APPLE_2 | APPLE_3
----------------------------------------
B1 | 1 | 2 | 3
B2 |1 | | 3
Any help is much appreciated, thanks in advance.
EDIT
Thanks both OMG Ponies and Bill, I'll try both of your solutions, just one last thing, its possible to get this result:
FARM | RED | YELLOW | GREEN
-------------------------------------
GEORGE'S FARM | YES | YES | YES
JOHN's FARM 开发者_如何学运维| YES | NO | YES
Firebird 2.0 supports the CASE expression, so you can use:
SELECT fa.farm AS farm_name,
MAX(CASE WHEN fa.apple = 1 THEN fa.apple ELSE NULL END AS apple_1,
MAX(CASE WHEN fa.apple = 2 THEN fa.apple ELSE NULL END AS apple_2,
MAX(CASE WHEN fa.apple = 3 THEN fa.apple ELSE NULL END AS apple_3,
FROM FARM_APPLES fa
GROUP BY fa.farm
SELECT F.ID AS FARM_NAME,
A1.APPLE AS APPLE_1,
A2.APPLE AS APPLE_2,
A3.APPLE AS APPLE_3
FROM FARMS AS F
LEFT OUTER JOIN FARM_APPLES AS A1 ON F.ID = A1.FARM AND A1.APPLE = 1
LEFT OUTER JOIN FARM_APPLES AS A2 ON F.ID = A2.FARM AND A2.APPLE = 2
LEFT OUTER JOIN FARM_APPLES AS A3 ON F.ID = A3.FARM AND A3.APPLE = 3;
精彩评论