[SQL][SQlite] Pattern matching with subquery

V

Varghj

Active member
Joined
Aug 28, 2004
Messages
27
Hey,

Im 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 cant get it working.

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

Any help appreciated, having googled for an hour im no smarter.

Thanks.

Code: 
# Psuedo code

SELECT items.path FROM items WHERE items.tags LIKE "%" + (SELECT tags.id FROM tags WHERE tags.name=one) + ",%"
 
Hey!
Subselects are beautiful in their compexity, but most of the time not necessarry. This is AFAIK one of these times...
Code: 
# Psuedo code
SELECT items.path FROM items, tags WHERE items.tags LIKE "%" + tags.id + "%" and tags.name=one

I might be wrong, its easier if we get to see the actual code...

HTH
/Kejpa
 
Last edited by a moderator:
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 wont 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 hackish).

Im afraid I dont have any code since there is none, im 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 Im 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 its _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 :( .



kejpa said:
Hey!
Subselects are beautiful in their compexity, but most of the time not necessarry. This is AFAIK one of these times...
Code: 
# Psuedo code
SELECT items.path FROM items, tags WHERE items.tags LIKE "%" + tags.id + "%" and tags.name=one

I might be wrong, its easier if we get to see the actual code...

HTH
/Kejpa
Click to expand...
 
Im going to look at this one blurb that you provided:
Code: 
## 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 wont work. Youll need another table, something like this:
Code: 
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 thats what you want, let us know - we can then help with your queries.

-ner
 
Back
Top