What’s wrong with this query? Not Unique table/alias

Question

I came across a problem:

1066 - Not Unique table/alias: 'catalog_product_entity_decimal'

SELECT DISTINCT catalog_product_entity.entity_id, sku, price as preco, qty as quantidade, stock_status as estoque, catalog_product_entity_decimal.value as embutido_regra_promo, catalog_product_entity_decimal.value as embutido FROM catalog_product_entity 
INNER JOIN cataloginventory_stock_status ON (cataloginventory_stock_status.product_id = entity_id)
INNER JOIN catalog_product_index_price ON (catalog_product_index_price.entity_id = catalog_product_entity.entity_id)
INNER JOIN catalog_product_entity_decimal ON (catalog_product_entity_decimal.entity_id = catalog_product_entity.entity_id AND catalog_product_entity_decimal.attribute_id=249)
INNER JOIN catalog_product_entity_decimal ON (catalog_product_entity_decimal.entity_id = catalog_product_entity.entity_id AND catalog_product_entity_decimal.attribute_id=251)

The point is that the last two INNERS use the same table and column, but with different attributes in select, one set to bring the result of attribute_id 251 and the other 249.

Some solution?

RESOLUTION:

SELECT DISTINCT c0.entity_id, sku, price as preco, qty as quantidade, stock_status as estoque, c3.value as embutido_regra_promo, c4.value as embutido FROM catalog_product_entity c0 INNER JOIN cataloginventory_stock_status c1 ON (c1.product_id = c0.entity_id) INNER JOIN catalog_product_index_price c2 ON (c2.entity_id = c0.entity_id) INNER JOIN catalog_product_entity_decimal c3 ON (c3.entity_id = c0.entity_id AND c3.attribute_id=249) INNER JOIN catalog_product_entity_decimal c4 ON (c4.entity_id = c0.entity_id AND c4.attribute_id=251)

Answer 1

The problem is that you are not actually using for the joins. Try so:

SELECT DISTINCT catalog_product_entity.entity_id, sku, price as preco, qty as quantidade, stock_status as estoque, catalog_product_entity_decimal.value as embutido_regra_promo, catalog_product_entity_decimal.value as embutido 
FROM catalog_product_entity c0
INNER JOIN cataloginventory_stock_status c1 ON (c1.product_id = c0.entity_id)
INNER JOIN catalog_product_index_price c2 ON (c2.entity_id = c0.entity_id)
INNER JOIN catalog_product_entity_decimal c3 ON (c3.entity_id = c0.entity_id AND c3.attribute_id=249)
INNER JOIN catalog_product_entity_decimal c4 ON (c4.entity_id = c0.entity_id AND c4.attribute_id=251)

After using the alias before each select field also.

Answer 2

Good morning, someone can give me a light

I have this query down below:

SELECT bairro.ba_bairro AS ba_bairro, count(cadastro.ca_bairro) AS 
count_ca_outro_bairro, sum(cadastro.ca_bairro) AS sum_ca_bairro FROM 
(cadastro LEFT JOIN bairro ON bairro.ba_codigo=cadastro.ca_bairro) GROUP BY bairro.ba_bairro

And I make a mysql_fetch_assoc() showing the name of the district x and its registration total in the registration table.

Since the same exists but 10 fields called:

ca_outro_bairro_1,ca_outro_bairro_2,ca_outro_bairro_3,ca_outro_bairro_4,ca_outro_bairro_5,ca_outro_bairro_6,ca_outro_bairro_7,ca_outro_bairro_8,ca_outro_bairro_9,ca_outro_bairro_10

I wanted to add all because a record can live in the neighborhood x but has known in the neighborhood y.

I want to have a report where it shows the total by neighborhood that add the total of registration in these columns related above

Example: The neighborhood : neighborhood "center" There is 20 registration in the column ca_bairro and in the column ca_outro_bairro_1 has 10 record so in the center neighborhood will show 30 record and so with the other columns.