从2或3个表中搜索SQL

Question

I have a search form where I can search for my webshop products.

1 product can be in multiple categoris, not just in one. I store this in the termek_katgoria_kapcsolo table. At insert, it creates as many lines, as the product belong to many categoria.

Example: The ID 12 product belong to ID 1, ID 2, ID 3 categoria.

The search sql only look at categoria, when one categoria is selected. Most often, I just search for the products name, I don't sort it to categoris.

How can I write the sql, that if I select a categoria also? I show you the tables on a pic.

if($termek_kategoria == 0 ) // Sort to categoria or not only search for product name, id...
{
  $sql = "
    SELECT termek_id, termek_nev, termek_cikkszam, termek_status FROM termek
    WHERE $kereses_helye LIKE '%$kw%' ORDER BY $kereses_rendezes $kereses_sorrend
  ";
}
else
{
  // Sorting for categoria also
  $sql = "
    SELECT termek_id, termek_nev, termek_cikkszam, termek_status FROM termek
    WHERE $kereses_helye LIKE '%$kw%' AND termek_kategoria =
    '$termek_kategoria' ORDER BY $kereses_rendezes $kereses_sorrend
  ";
}

Update:

$sql = "
  SELECT termek.termek_id, termek.termek_nev, termek.termek_cikkszam, termek.termek_status
  termek_kategoria_kapcsolo.*, termek_kategoria.kat_id
  FROM termek
  LEFT JOIN termek_katgoria_kapcsolo ON termek_kategoria 
  WHERE termek_kategoria_kapcsolo.kat_kapcs_kategoria_id = termek_kategoria.kat_id
  AND termek.termek_id IN (SELECT kat_kapcs_termek_id FROM
  termek_kategoria_kapcsolo WHERE kat_kapcs_kategoria_id = '$termek_kategoria')
";

This result:

Whats going wrong here?

What I want is when I select a categoria, the program give me the products, that are in the selected categoria.

Answer 1

I solved the problem:

$sql = 
                   "
                        SELECT 
                            t.termek_id, 
                            t.termek_nev, 
                            t.termek_cikkszam, 
                            t.termek_status,

                            kapcs.kat_kapcs_kategoria_id,
                            kapcs.kat_kapcs_termek_id

                            FROM termek t

                            LEFT JOIN termek_katgoria_kapcsolo kapcs ON kapcs.kat_kapcs_kategoria_id = '$termek_kategoria' 

                        WHERE t.termek_id = kapcs.kat_kapcs_termek_id AND t.$kereses_helye LIKE '%$kw%' ORDER BY t.$kereses_rendezes $kereses_sorrend                           
                   ";