|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Is this possible?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. 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_bo***@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. > For instance, can you sell Pears and Apples in LA? Hi Michael, all products are sold in all cities, I just want the shortestlist 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#" <x**@abcdef.com> wrote in message Sounds like what you might need is one of the JOINs, but it's hard to givenews:wKOne.38973$NZ1.12558@fe09.lga... GOT DDL? 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_bo***@AThotmail.com> wrote in message I can write a stored proc to create a temp table and with "while" loopsnews:eJzV629ZFHA.1412@TK2MSFTNGP12.phx.gbl... 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. 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 Show quoteHide quote "Chris Botha" wrote: > 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. Show quoteHide quote "Renjith" <Renj***@discussions.microsoft.com> wrote in message achieve this, but was wondering if there is a select/something simpler.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: > > > I can write a stored proc to create a temp table and with "while" loops > > 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 ClientCities and four rows in ClientProducts, so this is a means of listing> > -------- ---- ------- > > 1 LA Apples > > 1 NY Pears > > 1 Oranges > > 1 Bananas > > Note that in the example above for ClientId = 1 there are two rows in 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.Show quoteHide quote > > > > Hope this makes sense. > > > > Thanks On Thu, 2 Jun 2005 23:52:05 -0700, Renjith wrote:
>Hi Hi renjith,> >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 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) 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 Show quoteHide quote "Chris Botha" wrote: > 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 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. Show quoteHide quote "Leo Leong" <LeoLe***@discussions.microsoft.com> wrote in message achieve this, but was wondering if there is a select/something simpler.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: > > > I can write a stored proc to create a temp table and with "while" loops > > 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 ClientCities and four rows in ClientProducts, so this is a means of listing> > -------- ---- ------- > > 1 LA Apples > > 1 NY Pears > > 1 Oranges > > 1 Bananas > > Note that in the example above for ClientId = 1 there are two rows in 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.Show quoteHide quote > > > > Hope this makes sense. > > > > Thanks 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 Show quoteHide quote "Chris Botha" <chris_s_bo***@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" <LeoLe***@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: >> >> > 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 > > 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. Show quoteHide quote "Michael C#" <ho***@boutdat.com> wrote in message news:OmolJjEaFHA.900@tk2msftngp13.phx.gbl... > 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_bo***@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" <LeoLe***@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: > >> > >> > 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 > > > > > > 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 Show quoteHide quote "Chris Botha" <chris_s_bo***@AThotmail.com> wrote in message news:OLrVjkSaFHA.3808@TK2MSFTNGP14.phx.gbl... > 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. > > "Michael C#" <ho***@boutdat.com> wrote in message > news:OmolJjEaFHA.900@tk2msftngp13.phx.gbl... >> 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_bo***@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" <LeoLe***@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: >> >> >> >> > 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 >> > >> > >> >> > > >> Hope this makes sense. << Actually it does not. A table is a collection of facts with one factper 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. >> Hope this makes sense. << I meant the problem statement, not the reason behind it. The client wants> Actually it does not. it, then they want it. Thanks for your observation though. Show quoteHide quote "--CELKO--" <jcelko***@earthlink.net> wrote in message news:1117827735.505358.301230@g49g2000cwa.googlegroups.com... > >> 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. > On Thu, 2 Jun 2005 21:44:08 -0400, Chris Botha wrote:
>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. Hi Chris,>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. 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 -- (Remove _NO_ and _SPAM_ to get my e-mail address) 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. "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message achieve this, but was wondering if there is a select/something simpler.news:6bc1a19lv5k2mci1n2libjeoaot4kquoo7@4ax.com... > On Thu, 2 Jun 2005 21:44:08 -0400, Chris Botha wrote: > > >I can write a stored proc to create a temp table and with "while" loops > >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 ClientCities and four rows in ClientProducts, so this is a means of listing> >-------- ---- ------- > >1 LA Apples > >1 NY Pears > >1 Oranges > >1 Bananas > >Note that in the example above for ClientId = 1 there are two rows in 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.Show quoteHide quote > > > >Hope this makes sense. > > 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 > -- > > (Remove _NO_ and _SPAM_ to get my e-mail address)  
Other interesting topics
|
|||||||||||||||||||||||
