Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    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. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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. #3
    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. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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
                               ELSE 'no' END) AS bad
               FROM lexicon_has_gpc
             GROUP
                 BY lexid ) AS these
        ON these.lexid = those.lexid
       AND these.bad = 0
    INNER
      JOIN lexicon
        ON lexicon.idlexicon = those.lexid
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2011
    Posts
    14
    Quote Originally Posted by r937 View Post
    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
                               ELSE 'no' END) AS bad
               FROM lexicon_has_gpc
             GROUP
                 BY lexid ) AS these
        ON these.lexid = those.lexid
       AND these.bad = 0
    INNER
      JOIN lexicon
        ON lexicon.idlexicon = those.lexid

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

  6. #6
    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. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by snipersix View Post
    also do mind translating the that query to fit these tables, so i can test if it works.
    you can do that, surely

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    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
    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;

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by snipersix View Post
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jan 2011
    Posts
    14
    Quote Originally Posted by r937 View Post
    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. #11
    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. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by snipersix View Post
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jan 2011
    Posts
    14
    Quote Originally Posted by r937 View Post
    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. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by snipersix View Post
    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")
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    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
  •