CODEWITHSUNDEEP
sqlmariadb
Rey
Rey

Reputation: 1433

Handling Duplicate Column Name Issue in MariaDB Count Query

I am getting an error on the query I'm running to get a count in MariaDB. This is the error:

Error Code: 1060. Duplicate column name 'id_number'

And this is my SQL code:

SELECT COUNT(*) as count FROM (
      SELECT * FROM ((cr.customers
        INNER JOIN (progress_notes_details
        INNER JOIN progress_notes ON progress_notes_details.progress_note_id = progress_notes.id_number) 
        ON customers.id_number = progress_notes.c_id)
        INNER JOIN open_balances ON progress_notes_details.id_number = open_balances.progress_notes_detail_id)
        INNER JOIN
          customer_payer_xref ON customers.id_number = customer_payer_xref.c_id
        WHERE
          (((progress_notes_details.qb_isbillable) IS NULL
            OR (progress_notes_details.qb_isbillable) <> 1)
            AND ((progress_notes_details.date_of_visit) BETWEEN coverage_start AND coverage_end)
            AND ((progress_notes_details.dynamics_status) = 3)
            AND ((customer_payer_xref.payer_id) = 23)
            AND ((customer_payer_xref.primary_secondary_account_type) = 1))
    ) AS qdat

Can this be resolved via aliases? If so, it's unclear to me where to add them. In the main query? In the subquery?

Also, to clarify, I just inherited this code - and yes, it's bracket-happy.

Upvotes: 0

Views: 255

Answers (2)

Himanshu
Himanshu

Reputation: 3970

Alias customers table as c and use it as c.id_number at one of the places it will remove that duplicate error as this is the only table you are using multiple times with idnumber hence duplicate

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Remove the outer query:

SELECT COUNT(*)
FROM ((cr.customers . . .

Clearly, you have tables with the same column name. This causes a problem with SELECT *.

All the parentheses are probably not needed for the JOINs as well.

Upvotes: 0

Related Questions