CODEWITHSUNDEEP
sql-server
dragos_kai
dragos_kai

Reputation: 110

SQL query returning ambiguous error

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')

Upvotes: 0

Views: 96

Answers (2)

dragos_kai
dragos_kai

Reputation: 110

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

Upvotes: 0

SQLBadPanda
SQLBadPanda

Reputation: 635

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.

Upvotes: 3

Related Questions