CODEWITHSUNDEEP
sql
Lance
Lance

Reputation: 27

How to use JOIN correctly

Can someone help me with this? I am new to SQL.

I want to return one record for every record in Product_Entity.

Product_Entity is linked to Entity_Varchar by the common column entity_id.

I want each record in the results to include Entity_Varchar.value, but only when Entity_Varchar.attribute_id = ‘73’ as there will be many records in Entity_Varchar that will match on entity_id, but only one where also Entity_Varchar.attribute_id=’73’

SELECT
    Product_Entity.sku AS SKU,
    Entity_Varchar.value AS Description
FROM
    Product_Entity
JOIN
    Entity_Varchar ON Entity_Varchar.entity_id = Entity_Varchar.entity_id
WHERE 
    Entity_Varchar.attribute_id = '73'

Upvotes: 0

Views: 50

Answers (4)

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

Your query is correct you just misspelled table name. Instead of Product_Entity you used Entity_Varchar in both side of on clause.

SELECT Product_Entity.sku AS SKU, Entity_Varchar.value AS Description FROM Product_Entity inner Join Entity_Varchar 
ON Product_Entity.entity_id = Entity_Varchar.entity_id
WHERE Entity_Varchar.attribute_id = '73'

Upvotes: 0

dougp
dougp

Reputation: 3089

If I understand you need, you want every record from Product_Entity, whether or not it has an attribute_id = 73 record on Entity_Varchar. But if there is an attibute_id = 73 record on Entity_Varchar, you want the description from that row also.

First, you need a LEFT OUTER JOIN to get all of the records from Product_Entity, not filtered by the contents of Entity_Varchar.
Then, you need to filter Entity_Varchar. But if you place this filter in the WHERE clause, it causes the join to become an INNER JOIN. (I see error a lot. I call it a "LEFT INNER JOIN". Of course, that's not a real thing.) To overcome this, put the filter in the join logic. Then it's filtered before being joined.

SELECT Product_Entity.sku AS SKU
, Entity_Varchar.value AS Description

FROM Product_Entity
  LEFT OUTER JOIN Entity_Varchar ON Entity_Varchar.entity_id = Entity_Varchar.entity_id
                                AND Entity_Varchar.attribute_id = '73'

Upvotes: 0

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

Your query is correct you just misspelled table name. Instead of Product_Entity you used Entity_Varchar in both side of on clause.

Corrected query:

SELECT Product_Entity.sku AS SKU, Entity_Varchar.value AS Description FROM Product_Entity inner Join Entity_Varchar 
ON Product_Entity.entity_id = Entity_Varchar.entity_id
WHERE Entity_Varchar.attribute_id = '73'

For better readability you can use table aliaces.

SELECT pe.sku AS SKU, ev.value AS Description 
FROM Product_Entity pe inner Join Entity_Varchar ev
ON pe.entity_id = ev.entity_id
WHERE ev.attribute_id = '73'

If you want to select rows from Product_Entity table even when there is no row in Entity_Varchar table with same entity_id and for attribute_id 73 you need to use left join instead of inner join.

SELECT pe.sku AS SKU, ev.value AS Description 
FROM Product_Entity pe left Join Entity_Varchar ev
ON pe.entity_id = ev.entity_id
WHERE ev.attribute_id = '73'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Your query basically looks right. However, if you want all rows in the first table, then use a LEFT JOIN:

SELECT pe.sku AS SKU, ev.value AS Description
FROM Product_Entity pe LEFT JOIN
     Entity_Varchar ev
     ON ev.entity_id = pe.entity_id AND
        ev.attribute_id = '73';

Rows that don't match will have NULL for the description.

Upvotes: 1

Related Questions