Cleverjoin?

  • Thread starter Thread starter Edwin Knoppert
  • Start date Start date
E

Edwin Knoppert

I have a field in a table (TABLE2) with commaseperated keywordes for
example: "AA,BB"

Another table (TABLE1) has multiple (user)records field only having "AA" or
only having "BB" etc..

I would like to use a join on these fields like:
SELECT Table1.*
FROM Table2 INNER JOIN Table1 ON Table2.Field1 = Table1.Field1
WHERE (((Table2.Field1) Like ",NH,"));

However, i need fix the join instead, the WHERE was for testing purposes
only (and does not work obviously).

Pseudocode:
SELECT * TABLE1.*
JOIN TABLE1 WITH TABLE2 WHERE INSTR( TABLE2.FIELD1, TABLE1.FIELD1 ) >0

(Where TABLE2.FIELD1 = "AA,BB" and TABLE1.FIELD1 = "AA" or "BB" etc..)

Also problematic might be the lack of first and last comma.

The TABLE1.FIELD1 will be joined with the actual text these keys refer to.

I'm using ASP.NET v2 with OLEDB (MDB)
 
Try:

SELECT Table1.*
FROM Table2 INNER JOIN Table1 ON Table1.Field1 like '%' + Table2.Field1 + '%'

This will obviously only work if there's no overlaps (such as having "AA" in
field1 of table2, and having "AAA,BBB" in field1 of table1). If you can
guarantee commas at the beginning and end of each field1 record of table1,
then you can do this and get much better results:

SELECT Table1.*
FROM Table2 INNER JOIN Table1 ON Table1.Field1 like '%,' + Table2.Field1 +
',%'

-Keith
 
I will try, thanks!

Overlap? well i added ',' + in my previous attempt, so each part is
seperated.
Now checking your SQL and see if i can add the first and last comma as well.
Otherwise i think i'll store it with these.
To bad, not a big deal though.
 
Tested and didn't work, so i created a new MDB:

TABLE1:

ID Keyword
1 AA
2 BB
3 CC
4 AA

TABLE2:
ID Keywords
1 AA,BB,CC,DD
2 AA
3 AA,BB

Query:
SELECT Table1.*
FROM Table1 INNER JOIN Table2 ON Table1.Keyword Like '*' +Table2.Keywords
+'*';

Returns Table1-id : 1 and 4 only (AA's)
BB and CC should be shown as well imo.

I test this in access, it uses an asterisk, change to your needs of course..
Access can not switch to visual design modus, i have this sometimes if the
query is to wierd.
Does not implie it cannot work.

:)
 
Back
Top