this is my problem:

given the following tables:

gpc
idgpc gpcname
1 s
2 a
3 t
4 i
5 n
7 l
8 m
10 o

lexicon
idlexicon orthform
1 sit
2 it
3 its
4 sits
5 sat
6 at
7 sis
8 nit
9 lit
10 mit
11 not
12 ton
13 tom

lexicon_has_gpc
gpcid lexid
1 1
4 1
3 1
4 2
3 2
4 3
3 3
1 3
1 4
4 4
3 4
1 4
1 5
2 5
3 5
2 6
3 6
1 7
4 7
1 7
5 8
4 8
3 8
7 9
4 9
3 9
8 10
4 10
3 10
5 11
10 11
3 11
3 12
10 12
5 12
3 13
10 13
8 13

So here is the scenario

given that gpcid = 1,2,3,4 (a.k.a (s,i,t,a) from the gpc table)

the results should be as follows

sit, it, its, sits, sat, at, sis which if you refer to the lexicon table is rows 1-7.

It be great if someone could come up with a query for this. Im using mysql by the way.

thanks

is this what you are attempting to ask?

Dave

not sure if the query is correct. Let me explain in detail

essentially the idea is similar to an "anogram"

where you are give a set of letters ex. (s,a,i,t)
the set of letters is written in the database as ('s','a','i','t')

that being said we want words that are formed using a combination of these letters like an anogram

ex. sit, sat, it, sis
and so. And you can not form words outside of this collection.

so ex. "salt" should not be one of the results because "L" is not found in the collection. I don't know if I was able to describe it clearer..

here ya go --

didn't realize that you were in this forum as well.

also do mind translating the that query to fit these tables, so i can test if it works.

lexicon
idlexicon
orthform
gpcform

lexicon_has_gpc
idgpc
idlexicon

gpc
idgpc
gpcname

Given: gpcname ('s_s','i_i','t_t')

results should be = sit, it, sits, sis..

thanks

you can do that, surely

I was able to test the query. And yes the query works. however it is based on idgpc. Would it be possible to get the same result if it was based on gpcname instead?

this is the modified version by the way

select lexicon.orthform
from (select idlexicon
from lexicon_has_gpc
where idgpc IN (1,2,3,4)
group by idlexicon) as those
INNER JOIN (select idlexicon, count(CASE when idgpc in(1,2,3,4) THEN NULL
ELSE 'no' END) AS bad
from lexicon_has_gpc
group by idlexicon) AS these
ON these.idlexicon = those.idlexicon
AND these.bad=0 INNER JOIN lexicon on lexicon.idlexicon=those.idlexicon;

yes

just replace idgpc IN (1,2,3,4) with an equivalent condition for the other column

in both places

great i got it working thanks

Ok here is another problem of mine.

Given the same db structure as above.

if the give gpcnames are c_c, b_b,r_r
and focus gpcs = a_a,t_t (Focus gpc means the give set of focus gpc must be present in the result.

Ex. Results =brat, rat, bat, cat [notice that the results can contain any of the gpcnames given, but it always has to contain the list of focus gpcs in the result word set.

Normally I would do a "Like" in this case, but the presence of "_" screws things up. Also note gpcnames in the table gpc are stored as "c_c" ,"b_b" etc.

I also forgot to mention that the focus gpcs in this case a_a,t_t can appear in any part of the result word, which is the twist of this query, which I cant seem to figure how to do.

any suggestions? thanks

you can simply escape the underscore

the examples from da manual:

SELECT 'David!' LIKE 'David\_' returns 0 (false)

SELECT 'David_' LIKE 'David\_' returns 1 ( true)

don't quite understand what you mean by that. Could u please elaborate?

sure, i'd be happy to

in order to use an example that might make more sense than the examples used in da manual, could you please post the query you ~would've~ tried (when you said "Normally I would do a "Like" in this case")

select orthform from v_lexicon where gpcname IN ('k_c','b_b','r_r') *can also be written as [where idgpc in(9,15,6)]

AND gpcname like LIKE (%at%)

but because in the database "a" is written as a_a and tis written as "t_t" and doing like that would only cover where "at" is at the middle of the word..

So yea thats where im lost.