开发者

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;
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜