Mysql match against unknown column

Question

I have a problem with this mysql query:

select * from tb1 as a 
 where (select count(*) from tb2 as b 
         where Match(b.column1) against(`a.id`) )  = '1'

Mysql has this error:

1054 - Unknown column 'a.id' in 'where clause'

So I want to get all entries in tb1 where the number of entries in tb2 where a.id is inside the 'column1' column is 1.

I hope you understand my purpose otherwise feel free to ask.

what do you mean by "a.id is inside the 'column1' column, could you give us an example? At least the data type of these two columns. — grape_mao, Sep 12 '13 at 06:35
Sure: id is an INT and column1 is a text like [1000,2000,3000,4000]. — Wikunia, Sep 12 '13 at 06:38
See [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/a/3653574) — eggyal, Sep 12 '13 at 06:53
@eggyal it is not helping me now, but can you tell me how to make it better next time? — Wikunia, Sep 12 '13 at 06:58
Is the best way really to have something like id,typeid,value? so to have a new row for each entry? I was thinking that my solution is better, but now I think it's not :D — Wikunia, Sep 12 '13 at 07:07
To clarify error `select * from tb1 as a` produces the same result as `select * from tb1` ie no column `a.id`.It should be `select id from tb1` — david strachan, Sep 12 '13 at 07:25
@davidstrachan it doesn't matter if it is select * from tb1 as a or select id from tb1 as a — Wikunia, Sep 12 '13 at 07:40

score 0 · Answer 1 · answered Sep 12 '13 at 09:07

0

The table name should be outside the quotes, "a.id" is now handled as the column name (which off course doesn't exists), instead of a table.column pair:

against(a.`id`)

answered Sep 12 '13 at 09:07

davey

1,801
2
17
22

Then this error exists: #1210 - Incorrect arguments to AGAINST – Wikunia Sep 12 '13 at 09:13
1

You're doing a full text search on a non string column (and it's not necessary to do a FULL TEXT search here) that's the problem i think. This should do it: SELECT * FROM tb1 AS a WHERE (SELECT COUNT(*) FROM tb2 as b WHERE b.column1 = a.id) = 1 – davey Sep 12 '13 at 09:22
ah no this is actually not the same the count(*) = 1 means that there is only one row matching. – Wikunia Sep 12 '13 at 10:01
I thought you meant it that way. What result do you expect then? – davey Sep 12 '13 at 10:31
there is an row where column1 is [1000,2000,3000,4000] and one with [1000,1001,2000,3000,4000] and I want to have the id 1001 because it is only in one row but this id (1001) is not the only one in this row – Wikunia Sep 12 '13 at 10:47
Then the count should be 0 right? Try this one then: SELECT * FROM table1 WHERE NOT EXISTS (SELECT * FROM table2 WHERE table1.column1 = table2.id); – davey Sep 12 '13 at 11:49
the column is never = id. – Wikunia Sep 12 '13 at 11:54
Then you can just change it to the column you want (i got 'id' from your OP) – davey Sep 12 '13 at 12:10
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/37259/discussion-between-wikunia-and-davey) – Wikunia Sep 12 '13 at 12:14

score 0 · Answer 2 · answered Sep 12 '13 at 09:33

0

I'm nearly sure that the problem is in data range. You use a subquery and in this subquery there is no info about 'a'. You can use data from subquery in main query but not inversely. Try something like that (I can't test it so I can't guarantee query works):

SELECT a.*, b.count(*) FROM tb1 AS a 
LEFT JOIN tb2 AS b ON Match(b.column1) against(a.id)
HAVING b.count(*) = 1

answered Sep 12 '13 at 09:33

MKB

281
2
14

He has problems with b.count(*) and with a.id in against again. :( – Wikunia Sep 12 '13 at 10:13
AGAINST needs a string not an integer. From MySQL docs: "AGAINST takes a string to search for". So this won't work anyway. – davey Sep 12 '13 at 10:31
1

Well I've never used `MATCH...AGAINST` before and assumed that this part is ok. But it isn't! Documentation says: **The search string must be a string value that is constant during query evaluation. This rules out, for example, a table column because that can differ for each row**. So you need to find another approach for this condition. – MKB Sep 12 '13 at 10:32

Mysql match against unknown column

2 Answers2