Is this possible? -- sql server programming

Renjith
6/2/2005 11:52:05 PM

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

[quoted text, click to view]

Leo Leong
6/2/2005 11:56:01 PM

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

[quoted text, click to view]

Chris Botha
6/3/2005 8:49:19 AM

[quoted text, click to view]

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.

[quoted text, click to view]
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.
[quoted text, click to view]
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.

Chris Botha
6/3/2005 9:02:07 AM

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.

[quoted text, click to view]
ClientCities and four rows in ClientProducts, so this is a means of listing
Cities and Products on as few lines as possible.
[quoted text, click to view]

Chris Botha
6/3/2005 9:16:19 AM

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.

[quoted text, click to view]
ClientCities and four rows in ClientProducts, so this is a means of listing
Cities and Products on as few lines as possible.
[quoted text, click to view]

Michael C#
6/3/2005 10:29:48 AM

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
[quoted text, click to view]

--CELKO--
6/3/2005 12:42:15 PM

[quoted text, click to view]

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.

Hugo Kornelis
6/3/2005 9:37:06 PM

[quoted text, click to view]

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
--

Hugo Kornelis
6/3/2005 9:45:14 PM

[quoted text, click to view]

Hi Chris,

Assuming the ordering doesn't matter, then you could join on same
ranking withing alphabetic ordering for each client. The query would
become something like:

SELECT c.ClientId,
COALESCE(cc.City, '') AS City,
COALESCE(cp.Product, '') AS Product
FROM Clients AS c
INNER JOIN (SELECT ClientId, City,
(SELECT COUNT(*)
FROM ClientCities AS cc2
WHERE cc2.ClientId = cc1.ClientId
AND cc2.City <= cc1.City) AS Rank
FROM ClientCities AS cc1) AS cc
FULL OUTER JOIN (SELECT ClientId, Product,
(SELECT COUNT(*)
FROM ClientProducts AS cp2
WHERE cp2.ClientId = cp1.ClientId
AND cp2.Product <= cp1.Product) AS Rank
FROM ClientProducts AS cp1) AS cp
ON cp.ClientId = cc.ClientId
AND cp.Rank = cc.Rank
ON c.ClientId = COALESCE(cc.ClientId, cp.ClientId)
ORDER BY c.ClientId,
COALESCE(cc.Rank, cp.Rank)

This is untested, since you didn't provide the CREATE TABLE and INSERT
statements needed to reproduce your test data on my end. Please do
include those in future posts - see www.aspfaq.com/5006.

Best, Hugo
--

Chris Botha
6/4/2005 1:16:36 PM

Hi Michael, thanks a lot for your effort. Actually the plot is a lot thicker
than my example, there are 15 tables involved in total, and with more than 1
relation between the tables. Posting my example I was hoping for a relative
simple solution, and in the end I generated the result in the dotnet app. I
saved your post though for future reference.

Thanks again.

[quoted text, click to view]

Chris Botha
6/4/2005 1:22:42 PM

Hi Hugo, thanks for your post. As I mentioned in my reply to Michael above,
I was hoping for something simple and in the end did it in the dotnet app,
as in the real scenario there are a whole bunch of tables involved. I saved
your post for future reference though.

Thanks again.

[quoted text, click to view]
ClientCities and four rows in ClientProducts, so this is a means of listing
Cities and Products on as few lines as possible.
[quoted text, click to view]

Chris Botha
6/4/2005 1:32:38 PM

[quoted text, click to view]

I meant the problem statement, not the reason behind it. The client wants
it, then they want it.

Thanks for your observation though.

[quoted text, click to view]

Michael C#
6/4/2005 3:33:39 PM

It would be possible to extend this code for more tables; although I'd be
tempted to look at VIEWs at some point for that many tables. You are
correct though, a query like this is more for display purposes than anything
else, and should be done in the front end.

Thanks

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : Is this possible?