SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Evangelist bradical1379's Avatar
    Join Date
    Feb 2007
    Posts
    443
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help writing a mySQL query - Matching a column from one table to a column in another

    I am attempting to write a query that matches a titles of some material I have in a database to a list in another table of authors, and then spit out the results. The issue is that sometimes the author name may be along the lines of CJ, C.J. or C J Watson, so its impossible to get an exact match all of the time.

    Any ideas how I could match up the data from the author tables to match the data in the title fields?

    Code MySQL:
    SELECT AVG( d.price ) AS price , COUNT(d.id) AS total, c.author
    FROM data d
    INNER JOIN counts c ON c.setid = d.setid
    WHERE d.title LIKE concat( '%', c.author, '%' )
    AND d.id = '1'
    AND d.filter = 'today'
    AND d.date >= unix_timestamp(now() - interval 1 day)
    GROUP BY c.author
    ORDER BY price DESC

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    try SOUNDEX() which ignores punctuation
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist bradical1379's Avatar
    Join Date
    Feb 2007
    Posts
    443
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    try SOUNDEX() which ignores punctuation
    Would a fulltext search work better?

    Something like this?

    Code MySQL:
    SELECT AVG( d.price ) AS price , COUNT(d.id) AS total, c.author
    FROM data d
    INNER JOIN counts c ON c.setid = d.setid
    WHERE MATCH(d.title) AGAINST(c.author IN BOOLEAN MODE)
    AND c.id = '1'
    AND d.filter = 'today'
    AND d.date >= unix_timestamp(now() - interval 1 day)
    GROUP BY c.author
    ORDER BY price DESC

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by bradical1379 View Post
    Would a fulltext search work better?
    hey i just met you
    and that's not crazy
    so take your idea
    and test it maybe

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist bradical1379's Avatar
    Join Date
    Feb 2007
    Posts
    443
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    hey i just met you
    and that's not crazy
    so take your idea
    and test it maybe

    Error: #1210 - Incorrect arguments to AGAINST

    Are we allowed the have c.author value in the against clause?

  6. #6
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    does c.author have a FULLTEXT index? If it doesn't you can't.

  7. #7
    SitePoint Evangelist bradical1379's Avatar
    Join Date
    Feb 2007
    Posts
    443
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guelphdad View Post
    does c.author have a FULLTEXT index? If it doesn't you can't.
    Yes, as does d.title.


Bookmarks

Posting Permissions

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