SQL query returning ambiguous error

Question

Good mroning SO. I'm working on an over-normalized DB running off a program that we didn't build in house. We need to pull data from different tables to make it work. When I run the following query, it results in:

Msg 4104, Level 16, State 1, Line 14 The multi-part identifier "dbo.db_record.db_record_id" could not be bound.

I have looked into this, and I cannot find a reason for the error, and am trying to figure out what I can possibly do to fix this.

USE [Acuity_Train]

select p.provider_identity,
    p.provider_type,
    p.provider_name,
    p.NPI,
    t.taxonomy_code,
    udf.user_field_data_09 AS [Medicaid #]

 from provider p

 --This join links provider data to taxonomy table for taxonomy code select above
LEFT OUTER JOIN dbo.provider_taxonomy prvt 
             ON prvt.provider_identity = p.provider_identity
INNER JOIN dbo.taxonomy t 
        ON t.taxonomy_identity = prvt.taxonomy_identity

-- This allows for access to medicaid ID #
LEFT OUTER JOIN v_provider_network_affiliation vpna
             ON vpna.key2 = p.provider_identity
            AND vpna.key1 in ('55', '56')
LEFT OUTER JOIN user_field_data AS udf
             ON udf.table_code='PRNW' 
            AND udf.key1=vpna.provider_affiliation_identity 

-- Next 3 Joins needed to access company.company_code
LEFT OUTER JOIN dbo.provider_affiliation praf
             ON praf.db_record_id_child = dbo.db_record.db_record_id
LEFT OUTER JOIN dbo.db_record AS dbr_parent 
             ON praf.db_record_id_parent = dbr_parent.db_record_id  
LEFT OUTER JOIN company 
             ON dbr_parent.key1 = company.company_identity 

Where company.company_code in ('Prestige', '114', '115', '148', '181')

SQLBadPanda · Accepted Answer · 2017-09-28 13:10:43Z

3

Where you have...

LEFT OUTER JOIN dbo.provider_affiliation praf
         ON praf.db_record_id_child = dbo.db_record.db_record_id

You have at that point not introduced dbo.db_record so it has no idea what you want to join to.

answered Sep 28, 2017 at 13:10

SQLBadPanda

6355 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

dragos_kai · Accepted Answer · 2017-09-28 14:08:09Z

0

Missed a join step, needed to hit another table on the way here.

LEFT OUTER JOIN dbo.db_record 
             ON p.provider_identity = dbo.db_record.key1 
            AND dbo.db_record.table_code = 'PROV' 
LEFT OUTER JOIN dbo.provider_affiliation praf
             ON praf.db_record_id_child = dbo.db_record.db_record_id
LEFT OUTER JOIN dbo.db_record AS dbr_parent 
             ON praf.db_record_id_parent = dbr_parent.db_record_id  
LEFT OUTER JOIN company 
             ON dbr_parent.key1 = company.company_identity

answered Sep 28, 2017 at 14:08

dragos_kai

1201 gold badge5 silver badges23 bronze badges

Collectives™ on Stack Overflow

SQL query returning ambiguous error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related