CODEWITHSUNDEEP
mysql
valicu2000
valicu2000

Reputation: 431

INNER JOIN same table for results in one single row

I have a table named product like (minimal fields): 

manufacturer_id | sku
33              | Icedisk 1<:=:>DEEPCOOL
33              | DP-IDISK1<:=:>DEEPCOOL

I want to get through one SELECT statement both sku's like: Icedisk 1<:=:>DEEPCOOL | DP-IDISK1<:=:>DEEPCOOL.

I've tried, with no luck:

SELECT p1.sku AS sku1, p2.sku AS sku2 
FROM `product` p1 INNER JOIN `product` p2 ON p1.manufacturer_id=p2.manufacturer_id 
WHERE p1.sku LIKE 'Icedisk 1<:=:>%' AND p2.sku='DP-IDISK1<:=:>%'

If I search for each product individually (like SELECTskuFROMproductWHEREskuLIKE 'DP-IDISK1<:=:>%' ) it works fine. What am I missing here?

Many thanks!

Upvotes: 0

Views: 29

Answers (1)

pala_
pala_

Reputation: 9010

change AND p2.sku='DP-IDISK1<:=:>%' to AND p2.sku LIKE 'DP-IDISK1<:=:>%' and you will get the expected results.

As for your comment to your question (please add it to the question) its simple - you dont need to use values and you don't need to wrap the select statement in parenthesis. instead of:

INSERT IGNORE INTO aa_mapare_produse (sku1,sku2) VALUES (SELECT p1.sku, p2.sku FROM product p1 INNER JOIN product p2 ON p1.manufacturer_id=p2.manufacturer_id WHERE p1.sku LIKE 'E-Golf<:=:>%' AND p2.sku LIKE 'DP-EGOLF-WH<:=:>%' )

It should be

INSERT IGNORE INTO aa_mapare_produse (sku1,sku2) SELECT p1.sku, p2.sku FROM product p1 INNER JOIN product p2 ON p1.manufacturer_id=p2.manufacturer_id WHERE p1.sku LIKE 'E-Golf<:=:>%' AND p2.sku LIKE 'DP-EGOLF-WH<:=:>%'

And that's far more likely to work

Upvotes: 1

Related Questions