[SOLVED] Is something like this possible? (Regexp table join)

[derrick24]

SELECT vine_tags.title, vine_listings.*  FROM vine_listings

LEFT JOIN vine_tags ON vine_listings.tags REGEXP '[[:<:]]vine_tags.id[[:>:]]'

[Barand]

Try it. Experimenting is a great way to learn.

[derrick24]

yeah I have been, I think the REGEX syntax part is wrong, as its treating vine_tags.id as a normal string.

 

maybe it should be something like:  '[[:<:]]' . vine_tags.id . '[[:>:]]'

 

but that does not work either.

[fenway]

Why is your data stored this way anyway?  And yes, you'll need to concat() those.

[derrick24]

The tags field consists of integers representing the id of the tags table, ie 1-2-5-6- = to the id of the tag for example.

 

All I'm really trying to do is prevent tags from showing up if nothing is associated with it. Pointless having the user click on a tag only be told there is no associated data for that tag.

 

Anyway, that query is not working for some odd reason. I'm starting to think its not doable.

 

TAG TABLE

ID TAG              HITS

1, 'Kitchens',        0

2, 'Cupboards',    0

3, 'Cabins',          0

4, 'Huts',            0

5, 'Sheds',          0

6, 'Flooring',        0

7, 'Tiles',            0

 

 

SEARCH TABLE

ID DETALS      TAGS

1,      6,        '1-2-9-'

2,      8,        '3-4-5-'

3,      4,        '6-7-'

4,      2,        '1-2-'

5,      1,        '21-17-'

[derrick24]

SOLVED!

 

 

SELECT vine_tags.title, vine_listings.*  FROM vine_listings

LEFT JOIN vine_tags ON vine_listings.tags REGEXP CONCAT('[[:<:]]', vine_tags.id, '[[:>:]]')

 

 

Thanks for all your help guys.