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

This results in:
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:
Instead SqlServer2005 gives Martin in all the rows or a bunch of "null"...


Thanks in advance, suggestions are more than welcome!
- ManyTimes

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]

you need to have an identity on each table and link it.. use the referential integrity...

You cannot do it. You must return three result sets.

pootle flump ur codings are working excelent.

Thanks for the response.

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.

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