Jump to content

MySql difficult query


stuffie

Recommended Posts

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... :)
Link to comment
Share on other sites

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
Link to comment
Share on other sites

[!--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.
Link to comment
Share on other sites

Keep in mind that, due to the use of LIKE, this query will always be forced to scan through a great number of rows, and will be very slow as the database grows.

You may want to look into substring indexing strategies.

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.
Link to comment
Share on other sites

[!--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!!
Link to comment
Share on other sites

This should do the trick. I'm not clear on whether the keyword search should be a substring search or not, so I used LIKE. This query would go even faster if you could change it to = instead of LIKE.

Here's the query:
[code]SELECT customer_nr FROM customer c
LEFT JOIN (
    SELECT customer_nr FROM customer_category_keyword
    INNER JOIN (
        SELECT keyword_nr FROM category_keyword
        WHERE keyword_word LIKE "%searchStr%"
    ) cats USING(keyword_nr)
    GROUP BY customer_nr
) keys USING (customer_nr)
WHERE c.customer_name LIKE "%searchStr%"
OR keys.customer_nr IS NOT NULL
ORDER BY c.customer_name[/code]
Please note that this is still going to be slow when you have a lot of customers. Maintaining your own custom index would be the best solution.

Edit: I just corrected an error.
Edit: Made another improvement.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.