Professional Web Applications Themes

One-to-Many Relation Question - MySQL

Hi, Just a heads up, I have some SQL experience but not a ton (READ: newbie). Here's the scenario. I'm modeling a state graph using a relational database. Each graph state has a variable number of tags (0 or more) associated with them. I started by modeling this as having a Tag table with id's as an index and a text field for the actual tag data (what this tag data is is irrelevant to my problem). I then created a State table with just an integer index. Then, I created a third table (call it StateSet) which has a ...

  1. #1

    Default One-to-Many Relation Question

    Hi,

    Just a heads up, I have some SQL experience but not a ton (READ:
    newbie). Here's the scenario. I'm modeling a state graph using a
    relational database. Each graph state has a variable number of tags (0
    or more) associated with them. I started by modeling this as having a
    Tag table with id's as an index and a text field for the actual tag
    data (what this tag data is is irrelevant to my problem). I then
    created a State table with just an integer index. Then, I created a
    third table (call it StateSet) which has a foreign key to the State
    table's index and then another foreign key to the Tag table. In this
    way, I can now have one State with many Tags since I can have multiple
    rows for the same state but each with a different tag reference.

    My problem is I need to be able to query the database in a such a way
    that if I have a bunch of tags, I need to find if there are any states
    with those exact tags. I can write a query that does the find but it
    requires me to join x number of tables where x is the number of tags in
    the query. This is obviously bad. How would a more experience SQL
    programmer attack this problem? I'm sure I'm missing something obvious.

    Thanks!

    -Salil

    staufman@gmail.com Guest

  2. #2

    Default Re: One-to-Many Relation Question

    com wrote:

    [snip]
     

    This is a frequently asked question. And this is the default answer:

    SELECT state_id,
    COUNT(*) AS hits
    FROM StateSet
    WHERE tag_id IN (<list of tag_ids>)
    GROUP BY state_id
    HAVING hits = <nr. of tag_ids in list>


    XL
    --
    Axel Schwenke, Senior Software Developer, MySQL AB

    Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    MySQL User Forums: http://forums.mysql.com/
    Axel Guest

  3. #3

    Default Re: One-to-Many Relation Question

    Aha, very clever. Now, here's another question. This query restricts
    the results to those states that have AT LEAST the tags I want meaning
    it might have other tags in addition to those I'm searching on. Is
    there a way to include states that have exactly and only exactly those
    tags which I specify?

    Thanks for your help thus far!

    -Salil

    Axel Schwenke wrote: 
    >
    > This is a frequently asked question. And this is the default answer:
    >
    > SELECT state_id,
    > COUNT(*) AS hits
    > FROM StateSet
    > WHERE tag_id IN (<list of tag_ids>)
    > GROUP BY state_id
    > HAVING hits = <nr. of tag_ids in list>
    >
    >
    > XL
    > --
    > Axel Schwenke, Senior Software Developer, MySQL AB
    >
    > Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    > MySQL User Forums: http://forums.mysql.com/[/ref]

    staufman@gmail.com Guest

  4. #4

    Default Re: One-to-Many Relation Question

    Ok, what about something like this?

    SELECT a.anim_state_id, COUNT(*) AS cardinality FROM (SELECT
    anim_state_id, COUNT(*) AS hits FROM anim_state_set WHERE anim_tag_id
    IN (1,2) GROUP BY anim_state_id HAVING hits=2) AS sq, anim_state_set AS
    a WHERE sq.anim_state_id=a.anim_state_id GROUP BY a.anim_state_id
    HAVING cardinality=2

    Is there a better way to do it?

    -Salil

    com wrote: 
    > >
    > > This is a frequently asked question. And this is the default answer:
    > >
    > > SELECT state_id,
    > > COUNT(*) AS hits
    > > FROM StateSet
    > > WHERE tag_id IN (<list of tag_ids>)
    > > GROUP BY state_id
    > > HAVING hits = <nr. of tag_ids in list>
    > >
    > >
    > > XL
    > > --
    > > Axel Schwenke, Senior Software Developer, MySQL AB
    > >
    > > Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    > > MySQL User Forums: http://forums.mysql.com/[/ref][/ref]

    staufman@gmail.com Guest

  5. #5

    Default Re: One-to-Many Relation Question

    "com" <com> wrote:
    /me wrote:
     [/ref]
     

    Well, you can follow the same idea of "counting number of rows per
    state_id" and come up with something like

    SELECT state_id,
    SUM(tag_id IN (<list of tag_ids>)) AS my_tags,
    COUNT(*) AS all_tags
    FROM StateSet
    GROUP BY state_id
    HAVING my_tags = <nr. of tag_ids in list>
    AND all_tags = <nr. of tag_ids in list>


    [fullquote deleted]

    Salil, please don't top post! Read here how to quote correctly:
    http://www.netmeister.org/news/learn2quote.html


    XL
    --
    Axel Schwenke, Senior Software Developer, MySQL AB

    Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    MySQL User Forums: http://forums.mysql.com/
    Axel Guest

  6. #6

    Default Re: One-to-Many Relation Question

    Axel Schwenke wrote: 

    Duh, gotcha -- that is a lot easier to read. Thanks for the help...

    -Salil

    staufman@gmail.com Guest

Similar Threads

  1. SQL query and possible relation
    By Ryunosuke in forum Coldfusion Database Access
    Replies: 1
    Last Post: August 4th, 03:06 AM
  2. Can't find relation oid
    By ntinos@aueb.gr in forum PostgreSQL / PGSQL
    Replies: 2
    Last Post: January 3rd, 09:18 PM
  3. C function taking a relation and returning a similar relation.
    By Ben Martin in forum PostgreSQL / PGSQL
    Replies: 0
    Last Post: January 3rd, 03:23 PM
  4. [PHP-DEV] some public relation for php 5
    By Wolfgang Drews in forum PHP Development
    Replies: 0
    Last Post: November 12th, 10:05 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139