CODEWITHSUNDEEP
mysqlsearch
WraithLux
WraithLux

Reputation: 699

mysql searching, condition from another table

I have two tables. products and productgroups. I use

"SELECT * FROM `product` `t` 
  WHERE (name LIKE '%test%' OR ean LIKE '%test%') 
    AND closed=0 "

To search in products. Now I have another table called productgroups. Every product has its own productgroup_id. I need to show only those products that have their productgroup_id.closed=0. If productgroup.closed = 1 it shouldn't display it.

How do I do this?

Upvotes: 2

Views: 2949

Answers (4)

oezi
oezi

Reputation: 51797

just use an inner join:

SELECT
  *
FROM
  product t 
INNER JOIN 
  productgroups g ON t.productgroup_id = g.id
WHERE
  (t.name LIKE '%test%' OR t.ean LIKE '%test%') 
AND
  g.closed = 0

Upvotes: 1

JHolyhead
JHolyhead

Reputation: 984

Maybe I've missed the complexity in the problem, but...

SELECT * FROM
products INNER JOIN
     productgroups ON product.productGroup_id = productgroups.Id
WHERE
(products.name LIKE '%test%' OR products.ean LIKE '%test%') AND products.closed=0 and productgroups.closed = 0

should do the trick

Upvotes: 1

Johan
Johan

Reputation: 76537

SELECT p.* FROM product p
INNER JOIN productgroup pg ON (pg.id = p.productgroup_id)
WHERE (p.name LIKE '%test%' OR p.ean LIKE '%test%') 
AND p.closed=0
AND pg.closed=0

Upvotes: 3

Rami Alshareef
Rami Alshareef

Reputation: 7140

SELECT * FROM product t 
INNER JOIN productgroup pg ON t.productgroup_id = pg.id
WHERE (t.name LIKE '%test%' OR t.ean LIKE '%test%') AND t.closed=0 AND pg.Closed = 0

Upvotes: 0

Related Questions