[SQL][SQlite] Pattern matching with subquery

[Varghjärta]

Hey,

 

I'm having problems using LIKE when using a SELECT within the main SELECT.

 

items.tags might contains something to the effect of "1,2,3,".

The subquery returns one result in form of id which will be a number. To make my main select work I would idealy insert a wildcard before and after the injection from subquery so that the actual injection beomes "%1,%". Would make things real easy. But I can't get it working.

 

How would I form such a query so that it actually works lol ?

 

Any help appreciated, having googled for an hour i'm no smarter.

 

Thanks.

 

# Psuedo code

SELECT items.path FROM items WHERE items.tags LIKE "%" + (SELECT tags.id FROM tags WHERE tags.name='one') + ",%"

[kejpa]

Hey!

Subselects are beautiful in their compexity, but most of the time not necessarry. This is AFAIK one of these times...

# Psuedo code
SELECT items.path FROM items, tags WHERE items.tags LIKE "%" + tags.id + "%" and tags.name='one'

 

I might be wrong, it's easier if we get to see the actual code...

 

HTH

/Kejpa

Edited May 19, 2006 by kejpa

[Varghjärta]

Maybe you are right. But then again, the whole point of me using subselects is to kind of do a query that is automatically used in another query (all in _one_ query), so that I won't have to _manually_ via coding (whatever programming language) actually do one query and then another one and insert the previous result (somehow it feels more dirty and hack'ish).

 

I'm afraid I don't have any code since there is none, i'm doing all of this directly against the database so to test if it is even possible before I embark on designing the actual application -- I tend to do this, experiment and see what can be done if it is a territory I'm not _very_ comfortable in. So as to avoid future problems.

 

This is driving me crazy since it _should_ all be so easy, I want to associate rows in a table with rows in another table.

 

The thing is, I will have two tables (for example):

_______________________________________

## items

#id(int) | path(string) | tags(string)

0|"item1"|"o,2,"

1|"item2"|"1,2,"

 

## tags

#id(int) | name(string)

0|tag1

1|tag2

2|AnotherTag

_______________________________________

 

In this example I wanted to associate tag1 with item1, and tag2 with item2. And "AnotherTag" with both all of the items.

 

What I will get from user input (via a GUI), is the name of the tag(string). So if I want to fetch all items associated with the tag "AnotherTag", it would be sweet if I could make the query work having only knowledge of the Tag name, and not already knowing it's _actual inner id_.

 

 

Is this overkill, and is there an easier more elegant way of making association in this fashion?. I imagine the most _elegant_ approach would be to have a third table where I would store all the associations. BUT, it feels as though that could add to the complexity and it would be nice if I could make due with as little fuss as possible, namley somehow using The already existing and fast pattern matching ('%' for instance) to match against the content in the "tags" field of the items.

 

Thank you!

 

P.S. The more elegant approach of a third table would increse the size of the db too. There will be well over (minimum) of 300 000 items. The size of the db(my test-server so to speak) is at present about 20Mb. Having double that or tripple the amount of rows in a third table due to each tag association with each item, feels as if it might become something overwhelming, and not necessary if using SubSelects the way I want to would work :( .

 

 

 

Hey!

Subselects are beautiful in their compexity, but most of the time not necessarry. This is AFAIK one of these times...

# Psuedo code
SELECT items.path FROM items, tags WHERE items.tags LIKE "%" + tags.id + "%" and tags.name='one'

 

I might be wrong, it's easier if we get to see the actual code...

 

HTH

/Kejpa

[Nerseus]

I'm going to look at this one blurb that you provided:

## items
#id(int) | path(string) | tags(string)
0|"item1"|"o,2,"
1|"item2"|"1,2,"

## tags
#id(int) | name(string)
0|tag1
1|tag2
2|AnotherTag

 

Is your question about filtering appropriately or on how to SELECT the tags associated to your items?

 

In either case, you seem to want a relationship between items and tags - I think you want something like 1 item associated to 0 or more tags? If so, you really need to store the tags in another table for a relational database to work. It will definitely be a bit more work, but then the queries will actually work.

 

Trying to define relationship values inside of a string field may be more compact, but just won't work. You'll need another table, something like this:

Table:  items
Columns:
   id (int) 
   path (string)

Table:  ItemTags
Columns:
   itemID (int)
   tag (int)

Table:  tags
Columns:
   id (int)
   name (string)

 

The data would then have two rows in item and 4 rows in ItemTags, two tags associated each of the two items.

 

If that's what you want, let us know - we can then help with your queries.

 

-ner