Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Posts
    3

    Unanswered: question on join

    I have a database with a many to many relation ...

    table REFERENCE contains data about library items
    table TOPIC contains the topics covered by these items
    table REFERENCE_TOPIC contains the relation between the items in both tables.

    suppose i want all the fields from the table REFERENCE but only where the topic is a certain value (i'm doing an ASP page, so it the querystring passed by the page)

    would this select statement do the job?

    "SELECT * FROM REFERENCE WHERE ([REFERENCE_TOPIC].ref_id=[REFERENCE].ref_id AND [REFERENCE_TOPIC].topic_id=" & Request.QueryString("topic_id")

    i'm gonaa try ... hope to get some feed back

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    There are many methods to accomplish this.

    1)
    Select R.*
    From REFERENCE R
    INNER JOIN
    REFERENCE_TOPIC RT
    ON R.ID = RT.R_ID
    INNER JOIN
    TOPIC T ON
    T.ID = RT.T_ID AND T.value = 'x'

    2)
    Select R.*
    From REFERENCE R
    where R.ID IN
    (Select RT.R_ID
    From REFERENCE_TOPIC RT
    Where RT.T_ID IN
    (Select T.ID
    From TOPIC T
    Where T.value ='X'))
    Last edited by r123456; 07-19-04 at 23:37.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

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