Professional Web Applications Themes

SQL Query Against Normal Formal Tables - MySQL

In my database, I have a table of event/activity listings (in a table called "listing" for example) that are displayed on a web site. Each listing may have multiple attributes associated with it for things like the time of day when the activity takes place - for example, "morning", "afternoon", "evening" or any combination of the three. Since each listing may have more than one time of day, the possible times of day are stored in a table and a third table contains unique pairs of primary keys, "connecting" the listing table and the time of day table. But now ...

  1. #1

    Default SQL Query Against Normal Formal Tables

    In my database, I have a table of event/activity listings (in a table
    called "listing" for example) that are displayed on a web site. Each
    listing may have multiple attributes associated with it for things like
    the time of day when the activity takes place - for example, "morning",
    "afternoon", "evening" or any combination of the three. Since each
    listing may have more than one time of day, the possible times of day
    are stored in a table and a third table contains unique pairs of
    primary keys, "connecting" the listing table and the time of day table.

    But now how would I write a query to select rows from "listing" that
    have certain times of day associated with them? I tried a select
    statement similar to "SELECT * FROM listing, connectingTable WHERE
    connectingTable.listingId=listing.id AND
    connectingTable.keyFromTimeOfDayTable IN ('1','3')" but since this does
    a join that duplicates each posting row for each time of day associated
    with it, I get the same posting back multiple times. If a post has
    three times associated with it, I get three more or less identical
    copies of that row back in the results. I know that's what the query
    is supposed to do but that's not what I am after. Any ideas?

    -Brandon

    bcr07548@creighton.edu Guest

  2. #2

    Default Re: SQL Query Against Normal Formal Tables

    You can use the DISTINCT keyword after the SELECT command.

    [email]bcr07548creighton.edu[/email] wrote:
    > In my database, I have a table of event/activity listings (in a table
    > called "listing" for example) that are displayed on a web site. Each
    > listing may have multiple attributes associated with it for things like
    > the time of day when the activity takes place - for example, "morning",
    > "afternoon", "evening" or any combination of the three. Since each
    > listing may have more than one time of day, the possible times of day
    > are stored in a table and a third table contains unique pairs of
    > primary keys, "connecting" the listing table and the time of day table.
    >
    > But now how would I write a query to select rows from "listing" that
    > have certain times of day associated with them? I tried a select
    > statement similar to "SELECT * FROM listing, connectingTable WHERE
    > connectingTable.listingId=listing.id AND
    > connectingTable.keyFromTimeOfDayTable IN ('1','3')" but since this does
    > a join that duplicates each posting row for each time of day associated
    > with it, I get the same posting back multiple times. If a post has
    > three times associated with it, I get three more or less identical
    > copies of that row back in the results. I know that's what the query
    > is supposed to do but that's not what I am after. Any ideas?
    >
    > -Brandon
    >
    Dikkie Dik Guest

  3. #3

    Default Re: SQL Query Against Normal Formal Tables

    I tried that but since the join operation occurs before DISTINCT, each
    row as a whole becomes distinct and I get back the same results. If a
    posting has three times of day associated with it and I do the join, I
    get three results back that are more or less the same except for the
    timeOfDayDescription column that was added during the join. However,
    it is that added column that keeps those three rows from being
    identical and allowing DISTINCT to remove what would otherwise be
    duplicates.
    > You can use the DISTINCT keyword after the SELECT command.
    >
    > [email]bcr07548creighton.edu[/email] wrote:
    > > In my database, I have a table of event/activity listings (in a table
    > > called "listing" for example) that are displayed on a web site. Each
    > > listing may have multiple attributes associated with it for things like
    > > the time of day when the activity takes place - for example, "morning",
    > > "afternoon", "evening" or any combination of the three. Since each
    > > listing may have more than one time of day, the possible times of day
    > > are stored in a table and a third table contains unique pairs of
    > > primary keys, "connecting" the listing table and the time of day table.
    > >
    > > But now how would I write a query to select rows from "listing" that
    > > have certain times of day associated with them? I tried a select
    > > statement similar to "SELECT * FROM listing, connectingTable WHERE
    > > connectingTable.listingId=listing.id AND
    > > connectingTable.keyFromTimeOfDayTable IN ('1','3')" but since this does
    > > a join that duplicates each posting row for each time of day associated
    > > with it, I get the same posting back multiple times. If a post has
    > > three times associated with it, I get three more or less identical
    > > copies of that row back in the results. I know that's what the query
    > > is supposed to do but that's not what I am after. Any ideas?
    > >
    > > -Brandon
    > >
    bcr07548@creighton.edu Guest

  4. #4

    Default Re: SQL Query Against Normal Formal Tables

    <bcr07548creighton.edu> wrote in message
    news:1136963611.719702.219500g47g2000cwa.googlegr oups.com...
    > If a posting has three times of day associated with it and I do the join,
    > I
    > get three results back that are more or less the same except for the
    > timeOfDayDescription column that was added during the join. However,
    > it is that added column that keeps those three rows from being
    > identical and allowing DISTINCT to remove what would otherwise be
    > duplicates.
    Try something like this:

    SELECT l.* FROM listing AS l
    WHERE l.id IN (SELECT c.listingId FROM connectingTable AS c WHERE
    c.keyFromTimeOfDayTable IN ('1','3'))

    NB: Subqueries require MySQL 4.1 or later.

    Regards,
    Bill K.


    Bill Karwin Guest

Similar Threads

  1. (Repost) SQL Query Against Normal Form Tables
    By bcr07548@creighton.edu in forum MySQL
    Replies: 0
    Last Post: January 6th, 08:21 PM
  2. Query 5 Tables
    By Speegs in forum Dreamweaver AppDev
    Replies: 1
    Last Post: March 18th, 08:11 PM
  3. SQL query with 3 tables.
    By Craig Mason in forum PHP Development
    Replies: 0
    Last Post: May 19th, 01:33 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