sql - Oracle 查询 : Making conditional fetch in single query

Question

我在 oracle 数据库中有三个表，分别是 newitems、itemdetails、ticketitems 表。一些虚拟数据如下:

门票:

id    ticketid     itemid    quantity

1     100          9999      2
2     100          9998      5
3     100          2222      3

项目详情:

id    description    col_sumthing   extra_col
9999  Marlboro       val_sumthing   123_op
9998  Cigar Black    val_sumthing   456_pqwe

新品:

id    description    col_sumthing
2222  100Pipes       val_different

最初我只能从 itemdetails + ticketitems 中获取数据，使用简单的连接非常容易。查询是:

SELECT "TI".*, "I"."ID" AS "ITEMID", "I"."DESCRIPTION", "I"."col_sumthing" 
  FROM "TICKETITEMS" "TI"
 INNER JOIN "ITEMDETAILS" "I" ON TI.ITEMID = I.ID
 WHERE (TI.TICKET = '100')

类似的东西。

现在引入了 newitem 表，它也可能在 ticketitems 表中存在一些项目。

所以我想要这样的结果:

Final Result:
id    description    itemid    quantity   col_sumthing    extra_col
1     Marlboro       9999      2          val_sumthing    123_op
2     Cigar Black    9998      5          val_sumthing    456_pqwe
3     100Pipes       2222      3          val_different   

我面临的问题是，当在 itemdetails 中找不到详细信息时，它应该只检查 NEWITEMS。也欢迎任何其他解决方法。

Answer 1

SELECT TI.ID AS "id",
       COALESCE( I.DESCRIPTION, NI.DESCRIPTION ) AS "description"
       COALESCE( I.ID, NI.ID ) AS "itemid",
       TI.QUANTITY AS "quantity"
       COALESCE( I.COL_SUMTHING, NI.COL_SUMTHING ) AS "col_sumthing"
       I.EXTRA_COL AS "extra_col"
  FROM TICKETITEMS TI
  LEFT OUTER JOIN ITEMDETAILS I ON TI.ITEMID = I.ID
  LEFT OUTER JOIN NEWITEMS NI ON I.ID IS NULL AND TI.ITEMID = NI.ID
 WHERE TI.TICKETID = '100'