Query to return related data based on column value in original query

• s.green

  SSC Veteran

  Points: 299

  More actions

  March 7, 2011 at 5:51 am

  #247750

  Hi all,

  I've got a simple query that returns a list of transactions for suppliers/subcontractors. The table contains the code for the supplier/subby however I want to return the name which is in a related table. The problem I am encountering is that the related table is different based on a 'source type' value - if it is 1 then I need to perform the lookup against the supplier table, if 2 then against the subcontractor table.

  A current resultset is something like this

  Code - Value - SourceType

  BR01 - 100 - 1

  WD02 - 500 - 2

  Is there any way of doing this within a single query? Can I use the value in a case statement somehow?

  Appreciate any help.

  Regards,

  Simon

• Ken McKelvey

  SSCoach

  Points: 18843

  More actions

  March 7, 2011 at 6:06 am

  #1295274

  If possible, redesign the schema.

  With your current schema, you can UNION ALL queries with SourceType = 1 and SourceType = 2.

• s.green

  SSC Veteran

  Points: 299

  More actions

  March 7, 2011 at 8:11 am

  #1295353

  Ken,

  Thanks for the post - can't change the schema as it's our ERP solution.

  Will have a plan with union queries.

  Regards,

  Simon