ERROR: column Reference "Customer" is ambiguous

Asked

Viewed 1,140 times

1

GOAL

I’m holding an accountant in the host name column for every customer equal to BRASILFOODS, the only thing I wanted, was for next to the count to appear the name BRASILFOODS

QUERY

SELECT COUNT(*) hostname, customer
FROM tb_get_gap
LEFT JOIN tb_get_customers
ON tb_get_gap.customer = tb_get_customers.cust_cmdb WHERE tb_get_customers.customer = 'BRASILFOODS' and tb_get_gap.exception = 'NO';

OUTPUT

>[Error] Script lines: 1-4 --------------------------
 ERROR: column reference "customer" is ambiguous
 Line: 1
database postgresql

  • Apparently there are columns with the same name in their tables. Prefix with the table to which the field refers, as you have already done in your ON clause.

1 answer

2


There is a column called customerin the two tables involved: tb_get_gap and tb_get_customers, then he doesn’t know which of the two to take, you must give the fully qualified column name, for example tb_get_gap.customer, the same way you used on WHERE.

There seems to be another problem because it is not grouping the data.

  • SELECT tb_get_gap.customer, COUNT(*) hostname
FROM tb_get_gap
LEFT JOIN tb_get_customers
ON tb_get_gap.customer = tb_get_customers.cust_cmdb WHERE tb_get_customers.customer = 'FLEURY' and tb_get_gap.exception = 'NO'

Me retornou: 
 ERROR: column "tb_get_gap.Customer" must appear in the GROUP BY clause or be used in an Aggregate Function

  • 1

    That’s another mistake that’s not in your question, you’re taking data with a different cardinality, so one column takes a lot of data and the other takes a piece of data that needs to normalize it and take it all the same. To tell you the truth looking better I think all of yours query is wrong, and your question should have involved what you want to do so you can redo to give the expected result.

  • Consegui, a query ficou assim: SELECT tb_get_gap.customer, COUNT(*) hostname
FROM tb_get_gap
LEFT JOIN tb_get_customers
ON tb_get_gap.customer = tb_get_customers.cust_cmdb WHERE tb_get_customers.customer = 'FLEURY' and tb_get_gap.exception = 'NO'
GROUP BY tb_get_gap.;

  • 1

    If you have given the result you want, ok, this is a way to do.

  • thank you very much!

Browser other questions tagged database postgresql

You are not signed in. Login or sign up in order to post.