Is this possible?

I can write a stored proc to create a temp table and with "while" loops achi
eve this, but was wondering if there is a select/something simpler.
As an example, if I have 3 tables, Clients (with ID and name), and ClientCit
ies and ClientProducts. Then I want to list them something like:
ClientId City Product
-- -- --
1 LA Apples
1 NY Pears
1 Oranges
1 Bananas
Note that in the example above for ClientId = 1 there are two rows in Client
Cities and four rows in ClientProducts, so this is a means of listing Cities
and Products on as few lines as possible.
If ClientCities had 6 rows for ClientId = 1, then there would have been 6 ro
ws with products only in the first four rows.
Hope this makes sense.
Thanks.GOT DDL?
Sounds like what you might need is one of the JOINs, but it's hard to give y
ou the proper syntax without your DDL. For instance, can you sell Pears and
Apples in LA? If so, how do you want that listed? If you can post your DD
L and sample data in addition to your expected output you can probably get a
proper answer pretty quickly.
"Chris Botha" <chris_s_botha@.AThotmail.com> wrote in message news:eJzV629ZFH
A.1412@.TK2MSFTNGP12.phx.gbl...
I can write a stored proc to create a temp table and with "while" loops achi
eve this, but was wondering if there is a select/something simpler.
As an example, if I have 3 tables, Clients (with ID and name), and ClientCit
ies and ClientProducts. Then I want to list them something like:
ClientId City Product
-- -- --
1 LA Apples
1 NY Pears
1 Oranges
1 Bananas
Note that in the example above for ClientId = 1 there are two rows in Client
Cities and four rows in ClientProducts, so this is a means of listing Cities
and Products on as few lines as possible.
If ClientCities had 6 rows for ClientId = 1, then there would have been 6 ro
ws with products only in the first four rows.
Hope this makes sense.
Thanks.|||Hi
u can do this using an outer join like
As an example, if I have 3 tables, Clients (with ID and name), and
ClientCities and ClientProducts.
select clientid , cityname , productname
from clients , clientcities , clientproducts
where clients.clientid *= clientcities.clientid
and clients.clientid *= clientproducts.clientid
renjith
"Chris Botha" wrote:

> I can write a stored proc to create a temp table and with "while" loops ac
hieve this, but was wondering if there is a select/something simpler.
> As an example, if I have 3 tables, Clients (with ID and name), and ClientC
ities and ClientProducts. Then I want to list them something like:
> ClientId City Product
> -- -- --
> 1 LA Apples
> 1 NY Pears
> 1 Oranges
> 1 Bananas
> Note that in the example above for ClientId = 1 there are two rows in Clie
ntCities and four rows in ClientProducts, so this is a means of listing Citi
es and Products on as few lines as possible.
> If ClientCities had 6 rows for ClientId = 1, then there would have been 6
rows with products only in the first four rows.
> Hope this makes sense.
> Thanks|||Hi,
I think there is a key missing here.
If clientId is the only key, you are going to form a many-to-many
relationship after you use JOIN to combine the data.
So, you may need to add another between city and product.
Else, the output that you are showing is not a relational result. This is
not RDBMS meant to be.
Leo Leong
"Chris Botha" wrote:

> I can write a stored proc to create a temp table and with "while" loops ac
hieve this, but was wondering if there is a select/something simpler.
> As an example, if I have 3 tables, Clients (with ID and name), and ClientC
ities and ClientProducts. Then I want to list them something like:
> ClientId City Product
> -- -- --
> 1 LA Apples
> 1 NY Pears
> 1 Oranges
> 1 Bananas
> Note that in the example above for ClientId = 1 there are two rows in Clie
ntCities and four rows in ClientProducts, so this is a means of listing Citi
es and Products on as few lines as possible.
> If ClientCities had 6 rows for ClientId = 1, then there would have been 6
rows with products only in the first four rows.
> Hope this makes sense.
> Thanks|||> For instance, can you sell Pears and Apples in LA?
Hi Michael, all products are sold in all cities, I just want the shortest
list listing Cities and Products, so if the Cities table had WA in as well,
in my example table below it should appear on the row having Oranges.
"Michael C#" <xyz@.abcdef.com> wrote in message
news:wKOne.38973$NZ1.12558@.fe09.lga...
GOT DDL?
Sounds like what you might need is one of the JOINs, but it's hard to give
you the proper syntax without your DDL. For instance, can you sell Pears
and Apples in LA? If so, how do you want that listed? If you can post your
DDL and sample data in addition to your expected output you can probably get
a proper answer pretty quickly.
"Chris Botha" <chris_s_botha@.AThotmail.com> wrote in message
news:eJzV629ZFHA.1412@.TK2MSFTNGP12.phx.gbl...
I can write a stored proc to create a temp table and with "while" loops
achieve this, but was wondering if there is a select/something simpler.
As an example, if I have 3 tables, Clients (with ID and name), and
ClientCities and ClientProducts. Then I want to list them something like:
ClientId City Product
-- -- --
1 LA Apples
1 NY Pears
1 Oranges
1 Bananas
Note that in the example above for ClientId = 1 there are two rows in
ClientCities and four rows in ClientProducts, so this is a means of listing
Cities and Products on as few lines as possible.
If ClientCities had 6 rows for ClientId = 1, then there would have been 6
rows with products only in the first four rows.
Hope this makes sense.
Thanks.|||Thanks Renjith, problem with this is it will repeat every product for every
city, so in my example table below it will show 8 lines, LA repeated with
every product, and NY repeated with every product. Adding a 3rd city will
show 12 rows, while it should appear on the row with the Oranges.
"Renjith" <Renjith@.discussions.microsoft.com> wrote in message
news:25524AF2-4771-4864-BE75-F2BEB42588BB@.microsoft.com...
> Hi
> u can do this using an outer join like
> As an example, if I have 3 tables, Clients (with ID and name), and
> ClientCities and ClientProducts.
> select clientid , cityname , productname
> from clients , clientcities , clientproducts
> where clients.clientid *= clientcities.clientid
> and clients.clientid *= clientproducts.clientid
> renjith
>
> "Chris Botha" wrote:
>
achieve this, but was wondering if there is a select/something simpler.
ClientCities and ClientProducts. Then I want to list them something like:
ClientCities and four rows in ClientProducts, so this is a means of listing
Cities and Products on as few lines as possible.
6 rows with products only in the first four rows.|||Hi Leo, sorry, I guess my example is not that good, all of the tables have
Client_ID as a column.
And you are right when you say "the output that you are showing is not a
relational result", in this case it is not, it is taking all cities and all
products for this client and showing them in the shortest list.
"Leo Leong" <LeoLeong@.discussions.microsoft.com> wrote in message
news:B6AAFCA0-4E47-4690-89A3-3E547E6E51F3@.microsoft.com...
> Hi,
> I think there is a key missing here.
> If clientId is the only key, you are going to form a many-to-many
> relationship after you use JOIN to combine the data.
> So, you may need to add another between city and product.
> Else, the output that you are showing is not a relational result. This is
> not RDBMS meant to be.
> Leo Leong
> "Chris Botha" wrote:
>
achieve this, but was wondering if there is a select/something simpler.
ClientCities and ClientProducts. Then I want to list them something like:
ClientCities and four rows in ClientProducts, so this is a means of listing
Cities and Products on as few lines as possible.
6 rows with products only in the first four rows.|||Here's what it looks like you want to do:
SELECT 1 AS ClientID, s1.Cityname, s2.FruitName
FROM
(
SELECT TOP 100 PERCENT CityRank=COUNT(*), c1.Cityname
FROM CITIES c1, CITIES c2
WHERE c1.CityName >= c2.CityName
GROUP BY c1.CityName
ORDER BY CityRank
) s1
FULL OUTER JOIN
(
SELECT TOP 100 PERCENT FruitRank=COUNT(*), f1.Fruitname
FROM FRUITS f1, FRUITS f2
WHERE f1.FruitName >= f2.FruitName
GROUP BY f1.FruitName
ORDER BY FruitRank
) s2
ON s1.CityRank = s2.FruitRank
Which results in the following output on my schema:
1, LA, Apples
1, NY, Bananas
1, NULL, Oranges
1, NULL, Pears
Of course you'll have to modify it to match your schema and to join on your
Clients table.
Enjoy.
of Uniqe items, but all side-by-side
"Chris Botha" <chris_s_botha@.AThotmail.com> wrote in message
news:efaOo5DaFHA.3864@.TK2MSFTNGP10.phx.gbl...
> Hi Leo, sorry, I guess my example is not that good, all of the tables have
> Client_ID as a column.
> And you are right when you say "the output that you are showing is not a
> relational result", in this case it is not, it is taking all cities and
> all
> products for this client and showing them in the shortest list.
>
> "Leo Leong" <LeoLeong@.discussions.microsoft.com> wrote in message
> news:B6AAFCA0-4E47-4690-89A3-3E547E6E51F3@.microsoft.com...
> achieve this, but was wondering if there is a select/something simpler.
> ClientCities and ClientProducts. Then I want to list them something like:
> ClientCities and four rows in ClientProducts, so this is a means of
> listing
> Cities and Products on as few lines as possible.
> 6 rows with products only in the first four rows.
>|||On Thu, 2 Jun 2005 23:52:05 -0700, Renjith wrote:

>Hi
>u can do this using an outer join like
>As an example, if I have 3 tables, Clients (with ID and name), and
>ClientCities and ClientProducts.
>select clientid , cityname , productname
>from clients , clientcities , clientproducts
>where clients.clientid *= clientcities.clientid
>and clients.clientid *= clientproducts.clientid
Hi renjith,
Not only will that produce more rows than the OP asked for, it also uses
a depracated outer join construction.
Please don't create any new code with the =* and *= operators. Please
use the infixed outer join syntax instead. AFAIK, the =* and *= will
already stop working in SQL Server 2005, unless you lower the
compatibility level!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> Hope this makes sense. <<
Actually it does not. A table is a collection of facts with one fact
per row. You want to destroy data and create falsehoods. Put this in
a VIEW or simplely remember any combination of prtoduct and place is
valid. next, this violates the rule that you do display in the front
end and not the database.