stuffie

problem... You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'keys USING(customer_nr) WHERE c.customer_name = '%word%' OR keys.customer_nr IS NOT NULL' at line 9

Thx a lot dude! I will try it today... I will keep you updated!

[!--quoteo(post=349077:date=Feb 24 2006, 06:34 PM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Feb 24 2006, 06:34 PM) [snapback]349077[/snapback][/div][div class=\'quotemain\'][!--quotec--] You can also do it in three steps... find all the keywords that match first, then all the customer names that match, then synthesize. That will be quite a bit faster than the single query JOIN. [/quote] Can you compose it please, so I get what you mean. Thx a lot!!

[!--quoteo(post=349071:date=Feb 24 2006, 06:04 PM:name=obsidian)--][div class=\'quotetop\']QUOTE(obsidian @ Feb 24 2006, 06:04 PM) [snapback]349071[/snapback][/div][div class=\'quotemain\'][!--quotec--] try something like this: [!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] DISTINCT(customer_name) [color=green]FROM[/color] [color=orange]customer[/color] a, customer_category b, category_keyword c, customer_category_keyword d [color=green]WHERE[/color] a.customer_nr [color=orange]=[/color] b.customer_nr [color=blue]AND[/color] b.category_nr [color=orange]=[/color] c.category_nr [color=blue]AND[/color] c.keyword_nr [color=orange]=[/color] d.keyword_nr [color=blue]AND[/color] (customer_name [color=orange]=[/color] [color=red]'$word'[/color] || customer_name [color=orange]LIKE[/color] [color=red]'%$word%'[/color] || keyword_word [color=orange]=[/color] [color=red]'$word'[/color]); [!--sql2--][/div][!--sql3--] may not be perfect, as it's a little hard to understand exactly what you're after, but i think it should get you on the right track [/quote] Thx for the effort... I have got already something like this... [!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--]$query [color=orange]=[/color] "[span style=\'color:blue;font-weight:bold\']SELECT[/span] distinct(Klanten.KlantNr), Klanten.KlantNaam, Categorie_Trefwoord.Trefwoord_Omschrijving [color=green]FROM[/color] [color=orange]Klanten,[/color] Klant_Categorie, Klant_Categorie_Trefwoord, Categorie_Trefwoord [color=green]WHERE[/color] Klanten.KlantNr [color=orange]=[/color] Klant_Categorie.KlantNr [color=blue]AND[/color] Klanten.KlantNr [color=orange]=[/color] Klant_Categorie_Trefwoord.KlantNr [color=blue]AND[/color] Klant_Categorie.KlantNr [color=orange]=[/color] Klant_Categorie_Trefwoord.KlantNr [color=blue]AND[/color] Klant_Categorie.CategorieNr [color=orange]=[/color] Categorie_Trefwoord.Categorie_Nummer [color=blue]AND[/color] Klant_Categorie.CategorieNr [color=orange]=[/color] Klant_Categorie_Trefwoord.CategorieNr [color=blue]AND[/color] Klant_Categorie_Trefwoord.CategorieNr [color=orange]=[/color] Categorie_Trefwoord.Categorie_Nummer [color=blue]AND[/color] Categorie_Trefwoord.Trefwoord_Nummer [color=orange]=[/color] Klant_Categorie_Trefwoord.TrefwoordNr [color=blue]AND[/color] (Categorie_Trefwoord.Trefwoord_Omschrijving [color=orange]LIKE[/color] [color=red]'%"[/color].$_SESSION[[color=red]"zoekWoord"[/color]].[color=red]"%'[/color] [color=blue]OR[/color] Klanten.KlantNaam [color=orange]LIKE[/color] [color=red]'%"[/color].$_SESSION[[color=red]"zoekWoord"[/color]].[color=red]"%'[/color]) GROUP BY Klanten.KlantNr"; [!--sql2--][/div][!--sql3--] The problem with this methode is that it takes very long to execute the query (it's a large database) Is there a more optimized method? Now in the meanwhile i get to the following... [!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--]$query [color=orange]=[/color] " [span style=\'color:blue;font-weight:bold\']SELECT[/span] customer_category_keyword.customer_nr [color=green]FROM[/color] [color=orange]customer[/color] [color=green]LEFT[/color] [color=green]JOIN[/color] customer_category ON (customer_category.customer_nr[color=orange]=[/color] customer.customer_nr) [color=green]LEFT[/color] [color=green]JOIN[/color] customer_category_keyword ON (customer_category_keyword.customer_nr[color=orange]=[/color] customer.customer_nr) [color=green]WHERE[/color] customer.customer_name[color=orange]LIKE[/color] [color=red]'%"[/color].$word.[color=red]"%'[/color] "; [!--sql2--][/div][!--sql3--] Now I just have to make sure that he also looks in the keywords of the categories. Now he only search in the name of the customer.

I have the following tables... I've got the following tables customer(customer_nr, customer_name) customer_category (customer_nr, category_nr) category_keyword(category_nr, keyword_nr, keyword_word) customer_category_keyword(customer_nr, customer_nr, keyword_nr) A custumor has a costumor_name and is linked to a certain category. This category has also keywords (keyword_word) The Problem: --------------- Give a list of all the customers where the name is equal to or contains a part of a given word. Also there has to be looked in the categories to check if the given word is equal to a keyword. All the customers of this category are also to be listed. A customer may be listed 1 time en the results must be ordered by customer_name. Thx a lot guys! sorry for the bad english... :)