CODEWITHSUNDEEP
sqlsql-server
David Tunnell
David Tunnell

Reputation: 7542

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?

Upvotes: 1

Views: 47

Answers (1)

EoinS
EoinS

Reputation: 5482

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

Upvotes: 2

Related Questions