CODEWITHSUNDEEP
mysql
user1872523
user1872523

Reputation: 3

Matching records from a single table with filter on same columnn

Updates: It seems that solution did not fix the issue so I am trying to explain in a better way.

I am trying to implement a filter on products display, which has many custom fields and values.

E.g.

Color: Red, Blue, Green Material: Silver, Gold, Platinum Manufacturer: MF1, MF2, MF3

Now if someone wants to select all products with Color as Red & Blue, Material as Gold and Manufacturer as MF1, what would be the correct SQL. Please see the DB schema at Fiddle at http://www.sqlfiddle.com/#!2/6373d/2/0

I am using this query as suggested but it is not showing the correct results:

SELECT d1.productid, d1.fieldid, d1.value FROM `xcart_extra_field_values` d1 LEFT JOIN xcart_products_categories AS cat ON d1.productid = cat.productid WHERE (d1.fieldid= '36' AND d1.value LIKE '%14 karat guld%') AND cat.categoryid = '797' UNION ALL SELECT d2.productid, d2.fieldid, d2.value FROM `xcart_extra_field_values` d2 LEFT JOIN xcart_products_categories AS cat ON d2.productid = cat.productid WHERE (d2.fieldid= '37' AND d2.value LIKE '%Brillanter%') AND cat.categoryid = '797'

Upvotes: 0

Views: 73

Answers (3)

Saurabh Mishra
Saurabh Mishra

Reputation: 31

whats wrong with this straightforward query :

select productid from tbl_fld_val where (fieldid=36 and value like '%Søl%') or (fieldid= 37 AND value LIKE '%Farvede%');

Upvotes: 1

Muthu Kumaran
Muthu Kumaran

Reputation: 17920

try this SQL query, I used OR to get both 36 and 37 fieldid

SELECT d1.productid, d1.fieldid, d1.value
FROM tbl_fld_val d1
WHERE ( (d1.fieldid= 36 AND d1.value LIKE '%Søl%') OR (d1.fieldid= 37 AND d1.value LIKE '%Farvede%') )

Try: http://www.sqlfiddle.com/#!2/d42ad/3

Upvotes: 0

Taryn
Taryn

Reputation: 247840

You did not show what the desired result of your query is but, from what I can tell your query is working but you are only showing the values that are from your table tbl_fld_val with the alias d1. If you change your query slightly, you will see that the records are present:

SELECT d1.productid, d1.fieldid, d1.value
  ,d2.productid d2pid, d2.fieldid d2did, d2.value d2val
FROM tbl_fld_val d1
INNER JOIN tbl_fld_val d2 
  ON d1.productid=d2.productid
WHERE (d1.fieldid= 36 AND d1.value LIKE '%Søl%') 
  AND (d2.fieldid= 37 AND d2.value LIKE '%Farvede%');

See SQL Fiddle with Demo

If you want the data in to appear in the same columns, then you can use a UNION ALL similar to this:

SELECT d1.productid, d1.fieldid, d1.value
FROM tbl_fld_val d1
WHERE (d1.fieldid= 36 AND d1.value LIKE '%Søl%') 
union all
SELECT d2.productid, d2.fieldid, d2.value
FROM tbl_fld_val d2
WHERE (d2.fieldid= 37 AND d2.value LIKE '%Farvede%')

See SQL Fiddle with Demo

Or you can try an OR between your WHERE clauses:

SELECT d1.productid, d1.fieldid, d1.value
FROM tbl_fld_val d1
INNER JOIN tbl_fld_val d2 
  ON d1.productid=d2.productid
WHERE (d1.fieldid= 36 AND d1.value LIKE '%Søl%') 
  OR (d2.fieldid= 37 AND d2.value LIKE '%Farvede%');

See SQL Fiddle with Demo

Upvotes: 0

Related Questions