MySQL question 1515 Responses

Last post: 5 months, 3 weeks ago | Thread started: Jun 2, 08, 12:28 p.m.

RespondNew TopicDisable Images

• cosmoo

  mysql guru's,

  Having some issue with full text searching, both statements are listed below. I need to compare two columns on separate tables. At the moment I can only compare one column at a time, when i enter statement #2 I get a mysql error. Both "tags" column are full text enabled. Can I not compare two tables using full text? Help!

  Statement #1 - Working Statement:

  SELECT * FROM blog_post, article WHERE blog_post.id = '8' AND MATCH(article.tags) AGAINST ('relationships')

  Statement #2 - invalid statement

  SELECT * FROM blog_post, article WHERE blog_post.id = '8' AND MATCH(blog_post.tags,article.tag… AGAINST ('work')

  Error: #1210 - Incorrect arguments to MATCH

  Jun 2, 08, 12:28 p.m. – Permalink

• madirish

  this is a Q for cosmo. oh wait...

  • cosmo would help but he is climbing the mountains of Peru,– cosmoo1/3

    shut up! where??? i lived there– madirish2/3

    hehe...in his head he is up there.– cosmoo3/3

  

  You must be logged in to add a note. Login now or register for an account.

  Cancel

  Dog-earJun 2, 08, 12:28 p.m. – Permalink

• flavorful

  Use a sub-query? Where are your NOLOCK statements and explicit JOINS?

  This is an outrage!

  • nerd :P– Jaline1/3

    zOMG, hide the chil'ren!– Point52/3

    :D– flavorful3/3

  

  You must be logged in to add a note. Login now or register for an account.

  Cancel

  Dog-earJun 2, 08, 12:30 p.m. – Permalink

• cosmoo

  To hell with JOINS man!

  But i do think an INNER JOIN would be satisfying.

  

  You must be logged in to add a note. Login now or register for an account.

  Cancel

  Dog-earJun 2, 08, 12:31 p.m. – Permalink

• cosmoo

  using sub query means using LIKE and I hate that shit man. it's ghetto.

  

  You must be logged in to add a note. Login now or register for an account.

  Cancel

  Dog-earJun 2, 08, 12:33 p.m. – Permalink

• philipdrumman

  ???
  http://www.qbn.com/topics/553765…

  

  You must be logged in to add a note. Login now or register for an account.

  Cancel

  Dog-earJun 2, 08, 12:33 p.m. – Permalink

• flavorful

  I honestly have no idea what this MATCH and AGAINST shit is.
  MySQL is teh suck. MSSQL4EVA!!!

  • M$SQL wanker– madirish1/3

    Make money, money,
    Make money, money, monaaaay!– flavorful2/3

    still wanker.– cosmoo3/3

  

  You must be logged in to add a note. Login now or register for an account.

  Cancel

  Dog-earJun 2, 08, 12:34 p.m. – Permalink

• cosmoo

  http://dev.mysql.com/doc/refman/…

  

  You must be logged in to add a note. Login now or register for an account.

  Cancel

  Dog-earJun 2, 08, 12:35 p.m. – Permalink

• acescence

  seems like a join would work here

  

  You must be logged in to add a note. Login now or register for an account.

  Cancel

  Dog-earJun 2, 08, 12:37 p.m. – Permalink

• flavorful

  I don't think I'm grasping what you are doing, but can you use an OR Statement and simply search both individually?

  (
  ( MATCH(article.tags) AGAINST ('relationships') )
  OR
  ( MATCH(blog_post.tags) AGAINST ('relationships') )
  )

  

  You must be logged in to add a note. Login now or register for an account.

  Cancel

  Dog-earJun 2, 08, 12:38 p.m. – Permalink

• cosmoo

  acescence can you show me a quick example on that?

  blog_post.tags and article.tags are the columns that need to be compared.

  SELECT * FROM blog_post, article WHERE blog_post.id = '8' AND MATCH(blog_post.tags,article.tag…
  AGAINST ('work')

  

  You must be logged in to add a note. Login now or register for an account.

  Cancel

  Dog-earJun 2, 08, 12:40 p.m. – Permalink

• flavorful

  SELECT
  blog_post.*,
  article.*
  FROM
  blog_post WITH(NOLOCK)
  INNER JOIN article WITH(NOLOCK)
  ON blog_post.tags = articles.tags
  WHERE
  blog_post.id = 8
  AND
  MATCH(blog_post.tags, article.tags) AGAINST ('work')

  ---
  Is tags a Unique ID, because if there really isn't any correlation to these tables in conjunction with that field you will have to run two seperate queries I imagine using a UNION and/or find a way to match them up.

  • Der, my tabs didn't show up, haha.– flavorful1/4

    FAIL!
    #1064 - 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 'WITH ( NOLOCK ) INNER JOIN article WITH ( NOLOCK ) ON blog_post.tags = article' at line 1– cosmoo2/4

    Well it's not my fail, I explained why it won't work if there is no link field present in both tables.– flavorful3/4

    hehe...i know.– cosmoo4/4

  

  You must be logged in to add a note. Login now or register for an account.

  Cancel

  Dog-earJun 2, 08, 12:43 p.m. – Permalink

• cosmoo

  no correlation at all. basically each blog post has tags and i am trying to match those with tags on articles. i might just have to run two queries.

  

  You must be logged in to add a note. Login now or register for an account.

  Cancel

  Dog-earJun 2, 08, 12:46 p.m. – Permalink

• flavorful

  Uh yes you're going to need multiple queries.

  And I don't think you can make fun of using sub-queries or LIKE statements if you're pulling information from tables the way you are, haha.

  

  You must be logged in to add a note. Login now or register for an account.

  Cancel

  Dog-earJun 2, 08, 12:50 p.m. – Permalink

• acescence

  oh, i think i misunderstand how your tables are structured.

  seems to make more sense to have a global tags table, where each row is a unique tag, then posts and articles tables, then two tables that basically map a post or article id to a tag id.

  but maybe too late to go restructuring your whole db, ha.

  • < Yes. You needs a link table if you want the potential to share tags.– flavorful1/7

    01. tag
    02. blog_post_Tag_Link
    03. articles_Tag_Link– flavorful2/7

    Where tag would hold all the unique tags available, and the link tables would be the match up for each table.– flavorful3/7

    That can have tags.– flavorful4/7

    thanks guys!– cosmoo5/7

    No problemo homie! :D I've never used tags myself, but I've thought about them before in potential diagrams. No working example to share though.– flavorful6/7

    example to share though. Good luck!– flavorful7/7

  

  You must be logged in to add a note. Login now or register for an account.

  Cancel

  Dog-earJun 2, 08, 12:52 p.m. – Permalink

• cosmoo

  needing a mapping table, you are right acescence. well, these are changes that are coming at the last night. mapping table it is.

  

  You must be logged in to add a note. Login now or register for an account.

  Cancel

  Dog-earJun 2, 08, 12:56 p.m. – Permalink