Hi all, I googled, but didn't find answer to my problem.

Is it possible to execute a single (complex) SQL query and get a merged
result as explained below?

1) a query gives me back as result let's say 3 lines - these are my
clients
2) another query gives me back a list of goods for all of above 3
clients

I need to merge the output so, that each client will be followed by
block of lines, i.e. all goods for this particular client.

Although I can iterate through clients and execute single queries for
each of them, I do wonder whether this can be optimized, so that the
iteration will be performed on server side.

Thanks.

PS: hope this isn't wrong place to ask

This is exactly the best place to ask.

An inner join will "merge" the data based on clientID. The clietn data will
be repeated for each item, but you'll have al  the data. If you send more
details on your data and exatcly what you're looking for we can help with
more detail.

Select *
  from clients
    join items
      on clients.clientid = item.itemid

-Paul Nielsen
SQL Server MVP
www.SQLServerBible.com


<brianfakem***@yahoo.com> wrote in message
Show quoteHide quote

From the description, it sounds like what you want is possible.

For better quality help, please send the table DDL and some sample data in
the form of INSERT statements so that we know what you are working with.
Without that, we are just guessing.

Show quoteHide quote

Arnie Rowland wrote:
Hi, thanks for replies.

Paul, I need to export that data to XML, so I'd like to avoid parsing.
Better iterations than repeated data.
Arnie, well, I can post some sample data and tables' structure once I
am at work. However I'll try to explain the structure as good as I can.

I use 3 tables:
table List
    ID
    ItemRef
    ClientRef
    Date
    Quantity
    ...

table Clients
    ID
    Code
    Name
    Address
    ...

table Items
    ID
    Name
    ...

1) I need to get all clients in the list for specified date:
select ClientRef form List where Date = x group by ClientRef

So, I know I do have let's say 3 clients for this date.

2) I need their name, address, etc. The first query can be extended:
select ClientRef, Code, Name, Address
from (select ClientRef form List where Date = x group by ClientRef) as
a
inner join Clients as b on a.ClientRef = b.ID

Thus I get all relevant client info and I'm ready to prepare the list
of goods for clients who purchased on date x

3) The list of goods for date x can be obtained like this:
select Name, Quantity, Date, ...
from List as a inner join Items as b on a.ItemRef = b.ID
where Date = x

So, I get all items for all clients for date x. Let's say we got 15
records, i.e. 5 for each client.

Now my problem is that I'd like to merge the result of 2) and 3) like
this:

first client form query 2)
all records from query 3) for client one (5 records)

second client from query 2)
all records from query 3) for client two (5 records)

third client from query 2)
all records from query 3) for client three (5 records)

Is above possible with plain SQL?

Thanks a bunch!
brian

SQL question
SQLXML 4.0 "timestamp" updategram question
Identity keys in a One-to-Zero-or-Many relationship
Function results
Incorrect information from a "View"