How can I get only one row per record in master ta

问题:

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

回答1:

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

回答2:

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;