SQL Select - Calculated Column if Value Exists in another Table

Question

Trying to work through a SQL query with some very limited knowledge and experience. Tried quite a few things I've found through searches, but haven't come up with my desired result.

I have four tables:

ORDERS
[ID][DATE]

ORDER_DETAILS
[ID][ITEM_NO][QTY]

ITEMS
[ITEM_NO][DESC]

KITS
[KIT_NO][ITEM_NO]

Re: KITS - [KIT_NO] and [ITEM_NO] are both FK to the ITEMS table. The concatenation of them is the PK.

I want to select ORDERS, ORDERS.DATE, ORDER_DETAILS.ITEM_NO, ITEMS.DESC

No problem. A few simple inner joins and I'm on my way.

The difficulty lies in adding a column to the select statement, IS_KIT, that is true if:

EXISTS(SELECT null FROM KITS WHERE KITS.ITEM_NO = ORDER_DETAILS.ITEM_NO).

(if the kits table contains the item, flag this row)

Is there any way to calculate that column?

Answer 1

There are different ways to do this.

The simplest is probably a LEFT JOIN with a CASE calculated column:

SELECT
  o.date,
  od.item_no,
  i.desc,
  CASE WHEN k.item_no IS NULL THEN 0 ELSE 1 END AS is_kit
FROM      orders        o
JOIN      order_details od ON od.id=o.id
JOIN      items         i  ON i.item_no = od.item_no
LEFT JOIN kits          k  ON k.item_no = od.item_no

But you could also use a SUBSELECT:

SELECT
  o.date,
  od.item_no,
  i.desc,
  (SELECT COUNT(*) FROM kits k WHERE k.item_no = od.item_no) AS is_kit
FROM orders        o
JOIN order_details od ON od.id=o.id
JOIN items         i  ON i.item_no = od.item_no