Professional Web Applications Themes

Trying to figure out a Query - ASP Database

Hi, I have two tables. One contains Titles of Music. The other one contains Scores. The relationship is based as follow: Titles!TitlesID is linked with Scores!TitlesID (Many scores can exist for the same title). In the Scores table, there is also a field call "VisibleForWeb" (True/False). My problem is this one. I want to show only the titles that are visible for the web, and I don't want duplicate names (e.g.: I have 30 Agnus Dei different scores, but only 20 are allowed to be visible on the Web). Hence, my SQL should read something like: Select Titles in "Titles" ...

  1. #1

    Default Trying to figure out a Query

    Hi,

    I have two tables. One contains Titles of Music. The other one contains
    Scores. The relationship is based as follow:
    Titles!TitlesID is linked with Scores!TitlesID (Many scores can exist for
    the same title).

    In the Scores table, there is also a field call "VisibleForWeb"
    (True/False).

    My problem is this one. I want to show only the titles that are visible for
    the web, and I don't want duplicate names (e.g.: I have 30 Agnus Dei
    different scores, but only 20 are allowed to be visible on the Web).

    Hence, my SQL should read something like: Select Titles in "Titles" when in
    "Scores" it's ok to show it.

    It may be simple, but I'm lost...

    Guy


    Guy Guest

  2. #2

    Default Re: Trying to figure out a Query

    I can add that I have already a query that gives me only the scores that are
    visible for the web. From that query, I would have like to have a list of
    Titles with no duplicates in it.

    Guy

    "Guy Verville" <net> a écrit dans le message de news:
    %phx.gbl... 


    Guy Guest

  3. #3

    Default Re: Trying to figure out a Query

    Guy Verville wrote: 

    I suspect you are using Access, but please don't make us guess. Always tell
    us the type and version of database you are using.
    In this example, I am going to make some guesses as to your fieldnames:

    One way to do it is to use the DISTINCT keyword:

    Select DISTINCT t.Titles FROM Titles t INNER JOIN
    Scores s ON t.TitlesID = s.TitlesID
    WHERE s.VisibleForWeb = -1

    Another way is to use WHERE EXISTS:

    SELECT Titles FROM Titles t
    WHERE EXISTS (SELECT * FROM Scores s
    WHERE s.TitlesID = t.TitlesID AND VisibleForWeb = -1)

    HTH,
    Bob Barrows


    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Guest

  4. #4

    Default Re: Trying to figure out a Query

    Thank you very much. Yes, it was Access.

    Guy

    "Bob Barrows [MVP]" <SPAMcom> a écrit dans le message de
    news: phx.gbl... 
    >
    > I suspect you are using Access, but please don't make us guess. Always
    > tell
    > us the type and version of database you are using.
    > In this example, I am going to make some guesses as to your fieldnames:
    >
    > One way to do it is to use the DISTINCT keyword:
    >
    > Select DISTINCT t.Titles FROM Titles t INNER JOIN
    > Scores s ON t.TitlesID = s.TitlesID
    > WHERE s.VisibleForWeb = -1
    >
    > Another way is to use WHERE EXISTS:
    >
    > SELECT Titles FROM Titles t
    > WHERE EXISTS (SELECT * FROM Scores s
    > WHERE s.TitlesID = t.TitlesID AND VisibleForWeb = -1)
    >
    > HTH,
    > Bob Barrows
    >
    >
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >[/ref]


    Guy Guest

Similar Threads

  1. Cant figure this one out!
    By Mrcool1804 in forum Macromedia Flash Player
    Replies: 0
    Last Post: February 26th, 03:43 AM
  2. Can't figure out my query
    By jkgiven in forum Macromedia ColdFusion
    Replies: 3
    Last Post: July 12th, 08:13 PM
  3. Replies: 2
    Last Post: September 30th, 04:33 PM
  4. Can someone figure it out?
    By maxminas webforumsuser@macromedia.com in forum Macromedia Fireworks
    Replies: 5
    Last Post: August 19th, 09:28 AM
  5. trying to figure out CSS
    By Murray *TMM* in forum Macromedia Dreamweaver
    Replies: 1
    Last Post: July 17th, 07:59 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