#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    6
    Rep Power
    0

    Match() Against() Help Neeeded


    Hi,

    I have the following sql statement on a mysql db

    SELECT i21.nr01 AS fld1,
    i21.p00 AS fld3,
    i21.pc01 AS fld5,
    i22.nr01 AS fld7,
    i22.p00 AS fld19,
    i22.pc01 AS fld11,
    i29.nr01 AS fld13,
    i29.p00 AS fld15,
    i29.pc01 AS fld17
    FROM i00
    LEFT JOIN i22 ON i21.nr01=i22.p01
    LEFT JOIN i29 ON i22.nr01=i29.p01
    LEFT JOIN i21 ON i00.p9901=i21.nr01

    WHERE (i21.p00 like'%{var1}%' && i22.p00 like'%{var3}%' && i29.p00 like'%{var5}%')
    GROUP BY i21.nr01 ASC , i22.nr01 ASC , i29.nr01 ASC
    ORDER BY i21.nr01 ASC , i22.nr01 ASC , i29.nr01 ASC

    I've set up 3 var (var1,2,3) but it suites better for me if i had 1 variable and i could match it against those three columns. Bear in mind that the name of the column is the same in all of the tables and i can't change it. Also i would like the result to show all the fields that might include the variable even if the rest of the fields on the connected tables are empty. Is it too much?

    Any help would be wonderful
    Thanks
    Chris
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,451
    Rep Power
    1751
    You seem to be checking three distinct columns against three distinct variables ... unless what you are saying is that the actual variables (var1, var3 and var5) all have the same value and you are checking those three columns for containing the same string?

    I'm sure Rudy could devise something cunning, but to me the issue is that you need to test three different columns and only want the data if you find the string in all three.

    I think, stress think!, you could head there by modifying your JOINs from LEFT to INNER and adjusting their ON conditions and dropping the WHERE clause:
    Code:
    INNER JOIN i22
       ON i21.nr01=i22.p01
                  AND
            i22.p00 like'%{var3}%'
    INNER JOIN i29
       ON i22.nr01=i29.p01
                  AND
            i29.p00 like'%{var5}%'
    INNER JOIN i21
       ON i00.p9901=i21.nr01
                 AND
            i21.p00 like'%{var1}%'
    But that just moves the situation to another place in the query, though it may be more efficient?

    I am also no sure what you mean by:
    Also i would like the result to show all the fields that might include the variable even if the rest of the fields on the connected tables are empty.
    That sounds like you want to see every row, regardless, as any column of the correct type, presumably might contain the search value
    Last edited by SimonJM; September 5th, 2013 at 08:12 AM.
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    6
    Rep Power
    0
    Thanks for your prompt reply SimonJM

    Let me clarify my need:

    i21 tbl contains some data, i22 tbl contains data associated 1 to many to i21 and i29 contains data associated 1 to many to i22.

    I need to enter one variable and then the query to search for that string (included - not exact search) in all three columns delivering the rows that contain that string no matter in which column is found.

    So i need the match against expr to look up for the string in all three columns and return the corresponding dependent strings in this column and the dependent columns even if they are null..

    thanks


    Originally Posted by SimonJM
    You seem to be checking three distinct columns against three distinct variables ... unless what you are saying is that the actual variables (var1, var3 and var5) all have the same value and you are checking those three columns for containing the same string?

    I'm sure Rudy could devise something cunning, but to me the issue is that you need to test three different columns and only want the data if you find the string in all three.

    I think, stress think!, you could head there by modifying your JOINs from LEFT to INNER and adjusting their ON conditions and dropping the WHERE clause:
    Code:
    INNER JOIN i22
       ON i21.nr01=i22.p01
                  AND
            i22.p00 like'%{var3}%'
    INNER JOIN i29
       ON i22.nr01=i29.p01
                  AND
            i29.p00 like'%{var5}%'
    INNER JOIN i21
       ON i00.p9901=i21.nr01
                 AND
            i21.p00 like'%{var1}%'
    But that just moves the situation to another place in the query, though it may be more efficient?

    I am also no sure what you mean by:

    That sounds like you want to see every row, regardless, as any column of the correct type, presumably might contain the search value
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,207
    Rep Power
    4279
    Originally Posted by akis7365
    So i need the match against expr to look up for the string in all three columns...
    MATCH AGAINST syntax works only when you declare a fulltext index (otherwise you get a table scan, which is deadly slow if there are joins)

    fulltext indexes cannot span more than one table

    does this information change your requirements at all?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    6
    Rep Power
    0
    Actually no since my tbls have i21 20-25 rows, i22 500-600 row and i29 aprox 1000. So the size Is not such a big problem
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    6
    Rep Power
    0

    Search String In A Three Tables Query Needed


    Here is a simple query based on i21,i22,i29 tbls:

    SELECT i21.p00 AS fld1,
    i22.p00 AS fld3,
    i29.p00 AS fld5,
    i21.pc01 AS fld7,
    i22.pc01 AS fld9,
    i29.pc01 AS fld11,
    i21.nr01 AS fld13,
    i22.nr01 AS fld15,
    i29.nr01 AS fld17,
    i21.nr01 AS nr01
    FROM i21
    LEFT JOIN i22 ON i21.nr01=i22.p01
    LEFT JOIN i29 ON i22.nr01=i29.p01

    ORDER BY i21.pc01 ASC , i22.pc01 ASC , i29.pc01 ASC

    Is there a way i can incorporate a variable string that it will look up in i21.p00, i22.p00, i29.p00 simultaneously? Bear in mind the "left join" connections. If the variable is found in i21.p00 i want the outcome to be all the connected to i22 and i29 lines even there is no i29 connected to the corespondent i22.

    Let me explain:

    i21.p00 includes "a" connected to i22.p00 "bb" and "bc".
    i22.p00 "bb" is connected to i29.p00 "bbb" and "bbc"
    but "bc" has no connection to i29.p00

    When the variable will be "a" i want all the data in all the three tables to be shown even those from i22 that have no connection to i29. It should look something like:

    i21.p00 i22.p00 i29.p00
    "a" "bb" "bbb"
    "a" "bb" "bbc"
    "a" "bc"

    the same way if the variable is "bc" the outcome should be
    "a" "bc"

    Do you think it's possible? Bear in mind the i can't change the names of the fields.
    Thanx
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,451
    Rep Power
    1751
    You have the 'basics' of a query already, which I assume (and hope) returns all the data you want, even if it also does return extra data. If that is the case then all that is needed is to craft a set of conditions that will remove the extraneous data.
    The LEFT JOINs mean that you will get the 'cascade' of data linked by the keys that define the realtionship between the tables. What is next is to test the contents of the p00 columns of those three tables. My problem is that you seem to be saying if i21.p00 contains "a" you want the data from i22 as i22.p00 with a value of "bb" is linked to that of i21.p00 (when that column has a value of "a") and I fail to see the link!
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    6
    Rep Power
    0
    Ok i come to conclusion that the problem is the LEFT Join. Let me explain once more my structure.

    We have 3 tbls a, b and c

    a is connected one to many to b and the latter is connected one to many to c.

    We need a variable to look up for a value in a column in a and in b and in c.

    If the value is found in c tbl it should return this row (data from a,b and c).

    If the value is found in b it should return as many rows as they are found in c OR just one row if there is no connected data in c (until now when i input a variable in b if data in b doesn't have connected data in c it is not shown at all).

    Accordingly if it is found in a it should return as many rows as they are found in b and c regardless if some data in b don't have connections to c .

    Is it more clear? Should we search for a solution in a different way (ie create table with all the queried data without search, match against expr and finally drop table)?
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,207
    Rep Power
    4279
    Originally Posted by akis7365
    Ok i come to conclusion that the problem is the LEFT Join.
    no

    LEFT JOIN is what you actually want

    however, if you put the search conditions in the WHERE clause, you will only ever get inner join results

    you have to put those conditions into the ON clause of the join
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    6
    Rep Power
    0
    Can you give me an example since i don't really get it.
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,207
    Rep Power
    4279
    Originally Posted by akis7365
    Can you give me an example since i don't really get it.
    see post #2 in this thread, but with LEFT OUTER JOINs instead

    notice the search conditions are in the ON clauses
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo