MySQL question
- Started
- Last post
- 15 Responses
- 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
- flavorful0
Use a sub-query? Where are your NOLOCK statements and explicit JOINS?
This is an outrage!
- cosmoo0
To hell with JOINS man!
But i do think an INNER JOIN would be satisfying.
- cosmoo0
using sub query means using LIKE and I hate that shit man. it's ghetto.
- acescence0
seems like a join would work here
- flavorful0
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') )
)
- cosmoo0
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')
- flavorful0
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.flavorful
- 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 1cosmoo - Well it's not my fail, I explained why it won't work if there is no link field present in both tables.flavorful
- hehe...i know.cosmoo
- cosmoo0
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.
- flavorful0
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.
- acescence0
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.flavorful
- 01. tag
02. blog_post_Tag_Link
03. articles_Tag_Linkflavorful - Where tag would hold all the unique tags available, and the link tables would be the match up for each table.flavorful
- That can have tags.flavorful
- thanks guys!cosmoo
- 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.flavorful
- example to share though. Good luck!flavorful
- cosmoo0
needing a mapping table, you are right acescence. well, these are changes that are coming at the last night. mapping table it is.