# Thread: Would anyone have a solution to this problem?

1. Registered User
Join Date
Jan 2011
Posts
14

## Unanswered: Would anyone have a solution to this problem?

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

2. Registered User
Join Date
Dec 2007
Location
Richmond, VA
Posts
1,328
is this what you are attempting to ask?

Code:
```select distinct lexi.orthform
from lexicon lexi
inner join lexicon_has_gpc lexi_gpc
on lexi_gpc.lexid = lexi.idlexicon
and lexi_gpc.gpcid in (1,2,3,4)```
Dave

3. Registered User
Join Date
Jan 2011
Posts
14
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..

4. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
here ya go --
Code:
```SELECT lexicon.orthform
FROM ( SELECT lexid
FROM lexicon_has_gpc
WHERE gpcid IN (1,2,3,4)
GROUP
BY lexid ) AS those
INNER
JOIN ( SELECT lexid
, COUNT(CASE WHEN gpcid IN (1,2,3,4)
THEN NULL
FROM lexicon_has_gpc
GROUP
BY lexid ) AS these
ON these.lexid = those.lexid
INNER
JOIN lexicon
ON lexicon.idlexicon = those.lexid```

5. Registered User
Join Date
Jan 2011
Posts
14
Originally Posted by r937
here ya go --
Code:
```SELECT lexicon.orthform
FROM ( SELECT lexid
FROM lexicon_has_gpc
WHERE gpcid IN (1,2,3,4)
GROUP
BY lexid ) AS those
INNER
JOIN ( SELECT lexid
, COUNT(CASE WHEN gpcid IN (1,2,3,4)
THEN NULL
FROM lexicon_has_gpc
GROUP
BY lexid ) AS these
ON these.lexid = those.lexid
INNER
JOIN lexicon
ON lexicon.idlexicon = those.lexid```

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

6. Registered User
Join Date
Jan 2011
Posts
14
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

7. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Originally Posted by snipersix
also do mind translating the that query to fit these tables, so i can test if it works.
you can do that, surely

8. Registered User
Join Date
Jan 2011
Posts
14
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
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;

9. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Originally Posted by snipersix
Would it be possible to get the same result if it was based on gpcname instead?
yes

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

in both places

10. Registered User
Join Date
Jan 2011
Posts
14
Originally Posted by r937
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

11. Registered User
Join Date
Jan 2011
Posts
14

## Handorm Bible,

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
Last edited by snipersix; 01-18-11 at 04:02.

12. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Originally Posted by snipersix
Normally I would do a "Like" in this case, but the presence of "_" screws things up.
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)

13. Registered User
Join Date
Jan 2011
Posts
14
Originally Posted by r937
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?

14. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Originally Posted by snipersix
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")

15. Registered User
Join Date
Jan 2011
Posts
14
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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•