David Tunnell David Tunnell -4 years ago 98
SQL Question

Filtering by an ID that is part of a JOIN query

I am working on a query wthat has many joins. The data that comes back is correct:

SELECT
cmb.item_id,
cmba.field_id,
cmba.attr_val,
cmba.upd_dtt,
cmba.upd_usr
FROM contract_member_brg cmb
INNER JOIN contract_member_brg_attr cmba
ON (cmb.item_id = cmba.item_id) where cmb.item_id=8086
UNION
SELECT
cmb.item_id,
ca.field_id,
ca.attr_val,
ca.upd_dtt,
ca.upd_usr
FROM contract_member_brg cmb
INNER JOIN contract_attr ca
ON (cmb.contract_item_id = ca.item_id)
WHERE ca.field_id IN (413) and cmb.item_id=8086
UNION
SELECT
cmb.item_id,
91,
m.item_name,
m.upd_dtt,
m.upd_usr
FROM contract_member_brg cmb
INNER JOIN contract_attr ca
ON (cmb.contract_item_id = ca.item_id
AND ca.field_id = 234)
INNER JOIN mfr m
ON (ca.attr_val = m.item_pk);


I am trying to filter for a specific item_id:

enter image description here

I have tried WHERE clauses all over the place with different aliases.

Where can I add a filter to this query so that I can return only a specific item_id in this select statement?

Answer Source

make your whole query a sub-query and filter on that:

select * from 
(SELECT
  cmb.item_id,
  cmba.field_id,
  cmba.attr_val,
  cmba.upd_dtt,
  cmba.upd_usr
FROM contract_member_brg cmb
INNER JOIN contract_member_brg_attr cmba
  ON (cmb.item_id = cmba.item_id) where  cmb.item_id=8086
UNION
SELECT
  cmb.item_id,
  ca.field_id,
  ca.attr_val,
  ca.upd_dtt,
  ca.upd_usr
FROM contract_member_brg cmb 
INNER JOIN contract_attr ca 
  ON (cmb.contract_item_id = ca.item_id) 
WHERE ca.field_id IN (413) and cmb.item_id=8086
UNION
SELECT
  cmb.item_id,
  91,
  m.item_name,
  m.upd_dtt,
  m.upd_usr
FROM contract_member_brg cmb
INNER JOIN contract_attr ca
  ON (cmb.contract_item_id = ca.item_id
  AND ca.field_id = 234)
INNER JOIN mfr m
  ON (ca.attr_val = m.item_pk)
) a
where item_id = 'whatevs';

Here is an example to demonstrate

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download