Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2010
    Location
    Norway
    Posts
    45

    Unanswered: One query against three tables... Return what I want.

    Hello

    I've got a little "dilemma", trying to make it "all" happen in one query.
    I've attached a little picture...

    Three tables:
    Table 1 - The user table, stores name and a ID (Primary Key/PK)
    Table 2 - Stores the number of phones the person has
    Table 3 - Stores a telephone number which refers back to a phone. (Ignore the fact that a phonenumber could be the PK itself, ... due to I just made this scenario up... )

    A user can have multiple phones, a phone can have multiple numbers (in this scenario).

    Now I want to create a query which results like the picture shows.
    Just all those 3 queries into one bigger query, selecting only one "name" from table 1, selecting all his phones in table 2 and all of those phones' phonenumbers in table 3.
    The problem isnt to get values from three tables, but to optimize the outcome, without duplicate values...well, read on... :P

    Code:
    SELECT DISTINCT tbl1.Name, tbl2.ID, tbl2.Phones
    FROM         tbl1 INNER JOIN
                          tbl2 ON tbl1.ID = tbl2.nID AND tbl1.Name = 'Martin'
    This results in:
    Code:
    Martin    1 Nokia
    Martin    2 Sony
    Martin    4 Ipad
    As you can see, Martin has already returned three times, I do not want that, plus I also want to query the third table, without anything repeating at all. (Martin cannot have two Nokia phones, nor two of the same telephone number per phone, but two different phones can have the same number, while a phone can also have multiple different telephone numbers... lol )

    So I basically want it to return on this format:
    Code:
    Martin            //Martin takes up only one row and one column
    1 Nokia           //From here on and next three rows, phones are returned
    2 Sony
    4 Ipad
    1  1  123456   //Pk, the phoneID and the phone numbers
    2  1  234561
    3  2  124411
    4  4  145141
    Instead SqlServer2005 gives Martin in all the rows or a bunch of "null"...


    Thanks in advance, suggestions are more than welcome!
    - ManyTimes
    Attached Thumbnails Attached Thumbnails HelpSQL.jpg  
    Last edited by ManyTimes; 07-08-10 at 21:42.

  2. #2
    Join Date
    Jul 2010
    Posts
    6
    it might help you...
    [code]
    Select from tb1 a inner join(tbl2 b inner join tb3 on b.pid=cpid) on a.nid=b.nid
    [\code]

  3. #3
    Join Date
    Jul 2010
    Posts
    6
    you need to have an identity on each table and link it.. use the referential integrity...

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You cannot do it. You must return three result sets.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jul 2010
    Location
    Norway
    Posts
    45
    Thanks for the response.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, of course he CAN do that, in a single result set. It's just going to require hackish coding.

    It looks like he is creating a pseudo-XML output format, so he might be able to use some of the XML functionality to accomplish this.

    WHY he would want to do this....well I'm probably in complete agreement with you on that, pootle.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What you are describing sounds like group supression (where you suppress repeating columns at the left side of a report). If that's what you're thinking, this is a presentaton issue so it should be handled at the client instead of at the server. Group supression is handled by nearly every reporting engine that I've ever seen, so doing it in code is almost always wasted effort.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

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