CODEWITHSUNDEEP
sqlsql-serverdatabase
Mark
Mark

Reputation: 307

Need multiple results from DB Query

In my scenario I have the following 2 tables:

Table 1: tdc_doc_field_def

Column 1: field_id
Column 2: field_name

Table 2: tdc_doc_field_data

Column 1: info_card_id
Column 2: field_id
Column 3: field_data

Inside of Table 1 I have 3 field_names I need to get, txtAppProposedChangeDesc, txtAppProposedChangeTechBasis and txtAppProposedChangeWorkConductedBy.

Inside of Table 2 is the field_data for those fields.

What I need is a parameterized query that if I specify the info_card_id I am able to get the 3 fields of data. I have the following query that will get me the data from one field name data but not all three:

SELECT
    tdc_doc_field_data.field_data AS txtProposedChange
FROM  tdc_doc_field_def 
INNER JOIN tdc_doc_field_data 
    ON tdc_doc_field_def.field_id = tdc_doc_field_data.field_id
WHERE
    (tdc_doc_field_data.info_card_id = '[txtInfoCardNumber]') 
    AND (tdc_doc_field_def.field_name = 'txtAppProposedChangeDesc')

Need help to expand this to help me get all three, txtAppProposedChangeDesc, txtAppProposedChangeTechBasis and txtAppProposedChangeWorkConductedBy? I'd really appreciate it!

EDIT:

I need to return the result set into the mapped values into my SELECT AS query.

Upvotes: 0

Views: 93

Answers (2)

Ronnis
Ronnis

Reputation: 12833

Assuming the keys are defined as follows:

tdc_doc_field_def(field_id)
tdc_doc_field_def(field_name)
tdc_doc_field_data(info_card_id, field_id)

You should be able to find what you want with the following query:

select a.info_card_id
      ,max(case when b.field_name = 'txtAppProposedChangeDesc' 
                then a.field_data end) as txtAppProposedChangeDesc
      ,max(case when b.field_name = 'txtAppProposedChangeTechBasis' 
                then a.field_data end) as txtAppProposedChangeTechBasis
      ,max(case when b.field_name = 'txtAppProposedChangeWorkConductedBy' 
                then a.field_data end) as txtAppProposedChangeWorkConductedBy
  from tdc_doc_field_data a
  join tdc_doc_field_def  b using(field_id)
 where a.info_card_id = '[txtInfoCardNumber]'
   and b.field_name in(
           'txtAppProposedChangeDesc'
          ,'txtAppProposedChangeTechBasis'
          ,'txtAppProposedChangeWorkConductedBy'
       )
 group 
    by a.info_card_id;

Or alternatively, if you have a info_card table, something like this should also work:

select a.info_card_id
      ,b1.field_data as txtAppProposedChangeDesc
      ,c1.field_data as txtAppProposedChangeTechBasis
      ,d1.field_data as txtAppProposedChangeWorkConductedBy
  from info_card a
  left join tdc_doc_field_data b1 on(a.info_card_id = b1.info_card_id)
  left join tdc_doc_field_def  b2 on(
       b2.field_id   = b1.field_id
   and b2.field_name = 'txtAppProposedChangeDesc'
  )    
  left join tdc_doc_field_data c1 on(a.info_card_id = c1.info_card_id)
  left join tdc_doc_field_def  c2 on(
       c2.field_id   = c1.field_id
   and c2.field_name = 'txtAppProposedChangeTechBasis'
  )
  left join tdc_doc_field_data d1 on(a.info_card_id = d1.info_card_id)
  left join tdc_doc_field_def  d2 on(
       d2.field_id   = d1.field_id
   and d2.field_name = 'txtAppProposedChangeWorkConductedBy'
  )
 where a.info_card_id = '[txtInfoCardNumber]';

Upvotes: 0

pcofre
pcofre

Reputation: 4066

Probably you just need to use the IN clause in the WHERE statement

WHERE
(
tdc_doc_field_data.info_card_id = '[txtInfoCardNumber]') 
AND (tdc_doc_field_def.field_name in (
    'txtAppProposedChangeDesc',
    'txtAppProposedChangeTechBasis',
    'txtAppProposedChangeWorkConductedBy')
)

Upvotes: 1

Related Questions