Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2003
    Posts
    7

    Unanswered: !Help with building mysql Query

    I have two tables

    tblDetail (fields: DetailTicketNum,DetailText....)
    tblSolution (fields: SolutionTicketNum,SolutionText...)

    I want to run a query on both tables where the name "Kalani" is found
    in either DetailText or SolutionText

    Here are the results of the query on each individual table

    mysql> select DetailTicketNum from tblDetail WHERE DetailText LIKE
    '%Kalani%';
    +-----------------+
    | DetailTicketNum |
    +-----------------+
    | 510 |
    | 2271 |
    | 5685 |
    | 6133 |
    +-----------------+
    4 rows in set (0.02 sec)

    mysql> select SolutionTicketNum from tblSolution WHERE SolutionText
    LIKE '%Kalani%';
    +-------------------+
    | SolutionTicketNum |
    +-------------------+
    | 526 |
    | 805 |
    | 2198 |
    | 5685 |
    +-------------------+
    4 rows in set (0.04 sec)


    However when I do the query on both the tables I should get 8
    results.. but I get 16 ?

    Can someone please look at the query and tell me what I'm doing wrong.

    mysql> select DetailTicketNum,SolutionTicketNum from
    tblDetail,tblSolution WHERE tblDetail.DetailText LIKE '%Kalani%' &&
    tblSolution.SolutionText LIKE '%Kalani%';
    +-----------------+-------------------+
    | DetailTicketNum | SolutionTicketNum |
    +-----------------+-------------------+
    | 510 | 526 |
    | 2271 | 526 |
    | 5685 | 526 |
    | 6133 | 526 |
    | 510 | 805 |
    | 2271 | 805 |
    | 5685 | 805 |
    | 6133 | 805 |
    | 510 | 2198 |
    | 2271 | 2198 |
    | 5685 | 2198 |
    | 6133 | 2198 |
    | 510 | 5685 |
    | 2271 | 5685 |
    | 5685 | 5685 |
    | 6133 | 5685 |
    +-----------------+-------------------+
    16 rows in set (0.06 sec)


    Thanks!

  2. #2
    Join Date
    Jan 2003
    Posts
    7

    Re: !Help with building mysql Query

    Originally posted by gandalf
    I have two tables

    tblDetail (fields: DetailTicketNum,DetailText....)
    tblSolution (fields: SolutionTicketNum,SolutionText...)

    I want to run a query on both tables where the name "Kalani" is found
    in either DetailText or SolutionText

    Here are the results of the query on each individual table

    mysql> select DetailTicketNum from tblDetail WHERE DetailText LIKE
    '%Kalani%';
    +-----------------+
    | DetailTicketNum |
    +-----------------+
    | 510 |
    | 2271 |
    | 5685 |
    | 6133 |
    +-----------------+
    4 rows in set (0.02 sec)

    mysql> select SolutionTicketNum from tblSolution WHERE SolutionText
    LIKE '%Kalani%';
    +-------------------+
    | SolutionTicketNum |
    +-------------------+
    | 526 |
    | 805 |
    | 2198 |
    | 5685 |
    +-------------------+
    4 rows in set (0.04 sec)


    However when I do the query on both the tables I should get 8
    results.. but I get 16 ?

    Can someone please look at the query and tell me what I'm doing wrong.

    mysql> select DetailTicketNum,SolutionTicketNum from
    tblDetail,tblSolution WHERE tblDetail.DetailText LIKE '%Kalani%' &&
    tblSolution.SolutionText LIKE '%Kalani%';
    +-----------------+-------------------+
    | DetailTicketNum | SolutionTicketNum |
    +-----------------+-------------------+
    | 510 | 526 |
    | 2271 | 526 |
    | 5685 | 526 |
    | 6133 | 526 |
    | 510 | 805 |
    | 2271 | 805 |
    | 5685 | 805 |
    | 6133 | 805 |
    | 510 | 2198 |
    | 2271 | 2198 |
    | 5685 | 2198 |
    | 6133 | 2198 |
    | 510 | 5685 |
    | 2271 | 5685 |
    | 5685 | 5685 |
    | 6133 | 5685 |
    +-----------------+-------------------+
    16 rows in set (0.06 sec)


    Thanks!
    Ok, I think I figured out the problem, if I use the following query I get the right results

    select DetailTicketNum,SolutionTicketNum from tblDetail LEFT JOIN tblSolution ON tblSolution.SolutionTicketNum=tblDetail.DetailTick etNum WHERE MATCH (tblDetail.DetailText) AGAINST ('Kalani') || MATCH (tblSolution.SolutionText) AGAINST ('Kalani');

    However the query takes 3+ minutes. This is being done on a web page and it has to be more efficient than this. There are about 6000 records in each table. Can someone suggest a way of making it run faster ? Or another method of doing it ?

    Thanks!

  3. #3
    Join Date
    Sep 2002
    Location
    Kyiv, Ukraine
    Posts
    77
    Will this one work?

    Code:
    SELECT DetailTicketNum,SolutionTicketNum
       FROM tblDetail LEFT JOIN tblSolution ON tblSolution.SolutionTicketNum = tblDetail.DetailTicketNum
       WHERE (tblDetail.DetailText LIKE '%Kalani%') || (tblSolution.SolutionText LIKE '%Kalani%');
    Last edited by aZa; 01-24-03 at 21:44.
    Yours faithfully,
    Yaroslav Zaremba

  4. #4
    Join Date
    Jan 2003
    Posts
    7
    Originally posted by aZa
    Will this one work?

    Code:
    SELECT DetailTicketNum,SolutionTicketNum
       FROM tblDetail LEFT JOIN tblSolution ON tblSolution.SolutionTicketNum = tblDetail.DetailTicketNum
       WHERE (tblDetail.DetailText LIKE '%Kalani%') || (tblSolution.SolutionText LIKE '%Kalani%');
    This one gives the same results.. but it takes even longer 7+ minutes

  5. #5
    Join Date
    Sep 2002
    Location
    Kyiv, Ukraine
    Posts
    77
    What if doing two different SELECTs for each table? I think this should lower overall process time, because in this case it looks through: 6000 + 6000 = 12 000 records and when using JOIN it looks through 6000 * 6000 = 36M records ...
    Yours faithfully,
    Yaroslav Zaremba

  6. #6
    Join Date
    Dec 2002
    Location
    Iceland
    Posts
    9

    Re: !Help with building mysql Query

    First off ... what version of mysql are you using ? If it is version 4.xxxx then you can use the union statement.

    select bla bla bla UNION select bla bla ble
    witch will give you the results from both queries

    did you create a index for the tables you are using ?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    UNION is definitely what you want

    if you aren't on mysql 4, see Writing UNION statements in MySQL 3.x

    rudy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •