Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Location
    canada, germany
    Posts
    2

    Cool Unanswered: how do i query a m to n (many to many) relationship?

    hey guys

    i have the following problem:

    table 1(movie) contains:
    movieID movie
    ---------------------------
    00128 bad boys

    table2(actor)
    actorID actor
    ------------------------------------
    004 will smith
    005 martin lawrence

    table3(actorConnect)
    acConID movieID actorID
    ------------------------------------
    0011 00128 004
    0012 00128 005


    how do i code, if i want the following output:

    movie actor actor
    --------------------------------------------------
    bad boys will smith martin lawrence

    thanks in advance

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    presumably you know how to join the tables to get this result, yes?

    movie actor
    bad boys will smith
    bad boys martin lawrence

    "flattening" (or denormalizing) the result set is purely cosmetic, and is best done by your scripting language

    there is no simple sql that will do it for you, except in sybase ase (LIST) or mysql version 4.1 (GROUP_CONCAT)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2004
    Location
    canada, germany
    Posts
    2

    Arrow Re: how do i query a m to n (many to many) relationship?

    ok, thanks

    i will try

Posting Permissions

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