|
Author |
Message |
raymondswisherhotmailcom
|
Posted: Tue Oct 05 15:31:32 CDT 2004 |
Top |
SQL Server Developer >> split data
Hi,
I have two tables. One table (table 1) just has data for look up.
ID Product_Name
C01 product 1
C02 product 2
B01 product 3
.......................
The other table (table 2) has the data that need to get product name using
the ID. However, the IDs in this table are combo ID.
ID QTY
C01 C02 3
C01 B01 2
C01 C02 B01 1
My question is how can I using the combo ID to get the product name. What I
want is:
ID QTY Product_name
C01 C02 3 product 1 product 2
C01 B01 2 product 1 product 3
C01 C02 B01 1 product 1 product 2 product 3
The length of each ID is fixed.
Thanks in advance!
SQL Server260
|
|
|
|
|
Anith
|
Posted: Tue Oct 05 15:31:32 CDT 2004 |
Top |
SQL Server Developer >> split data
You have a few problems here.
>> The other table (table 2) has the data that need to get product name
>> using the ID. However, the IDs in this table are combo ID.
Based on the narrative, a column in Table2 has multiple values concatenated
into one ( so called combo ID ) which forms a repeating group. Such
representations make data manipulation difficult, not to mention the
complexities involved in expressing meaningful queries. In short, your
schema violates several fundamental data management principles including the
first normal form which is a mandatory requirement for a table in a
relational database. Anything that follows from such a flawed design is
bound to unnecessary complexities & subsequent failure.
A better way of data representation would be like: Table2 ( id, qty )
ID qty -- pk ( id, qty)
------------
C01 3
C02 3
C01 2
B01 2
C01 1
C02 1
B01 1
Such a representation would make it much easier for the user to express
meaningful queries using relational operators.
>> My question is how can I using the combo ID to get the product name.
In general, concatenating values from different rows need to rely on some
sequence of the values. As such SQL Server does not have an inbuilt operator
which can take up the column name and some predefined sequence to return a
concatenated list of values in that column. Moreover any t-SQL methods will
have to rely on looping through values either explicitly or internally (
which in all cases violate data independence ). And not to mention, the
performance impact on some solutions involving larger set of values could be
worse as well.
So concatenation of row values are best handled on the client side
programming languages. You can retrieve the resultset to the client, loop
through the values and generate the display format you need.
--
Anith
|
|
|
|
|
David
|
Posted: Tue Oct 05 16:17:03 CDT 2004 |
Top |
SQL Server Developer >> split data
Well, we'll ignore the fact you're committing some serious normalization
breakage with that ID key! :p
Try this:
select t2.id, t2.qty, t1.product_name from table1 t1, table2 t2
where SUBSTRING ( t2.id , 1, 3 ) = t1.intidtable1.product_name,
> Hi,
>
> I have two tables. One table (table 1) just has data for look up.
> ID Product_Name
> C01 product 1
> C02 product 2
> B01 product 3
> .......................
> The other table (table 2) has the data that need to get product name using
> the ID. However, the IDs in this table are combo ID.
> ID QTY
> C01 C02 3
> C01 B01 2
> C01 C02 B01 1
>
> My question is how can I using the combo ID to get the product name. What I
> want is:
> ID QTY Product_name
> C01 C02 3 product 1 product 2
> C01 B01 2 product 1 product 3
> C01 C02 B01 1 product 1 product 2 product 3
>
> The length of each ID is fixed.
>
> Thanks in advance!
>
>
>
>
|
|
|
|
|
DavidWClary
|
Posted: Tue Oct 05 16:31:03 CDT 2004 |
Top |
SQL Server Developer >> split data
Umm... ok, not sure what brain fart I had there... last line is off.
select t2.id, t2.qty, t1.product_name from table1 t1, table2 t2
where SUBSTRING ( t2.id , 1, 3 ) = t1.product_name
Sorry about that.
> Well, we'll ignore the fact you're committing some serious normalization
> breakage with that ID key! :p
>
> Try this:
>
> select t2.id, t2.qty, t1.product_name from table1 t1, table2 t2
> where SUBSTRING ( t2.id , 1, 3 ) = t1.product_name
>
>
>
>
> > Hi,
> >
> > I have two tables. One table (table 1) just has data for look up.
> > ID Product_Name
> > C01 product 1
> > C02 product 2
> > B01 product 3
> > .......................
> > The other table (table 2) has the data that need to get product name using
> > the ID. However, the IDs in this table are combo ID.
> > ID QTY
> > C01 C02 3
> > C01 B01 2
> > C01 C02 B01 1
> >
> > My question is how can I using the combo ID to get the product name. What I
> > want is:
> > ID QTY Product_name
> > C01 C02 3 product 1 product 2
> > C01 B01 2 product 1 product 3
> > C01 C02 B01 1 product 1 product 2 product 3
> >
> > The length of each ID is fixed.
> >
> > Thanks in advance!
> >
> >
> >
> >
|
|
|
|
|
Joe
|
Posted: Tue Oct 05 16:52:36 CDT 2004 |
Top |
SQL Server Developer >> split data
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Your personal narrative makes no sense. What the heck is a
"combo id"? I do not remember ever hearing that term before in any SQL
book, RDBMS book or at an ANSI meeting.
My unsupported guess is that you are concatenating strings in a column
for the purpose of destroying First Normal Form and your data integrity.
Stop that right now, young lady, he said in his best stern parent voice
:)!!
Maybe you are trying to make some kind of packages from items. Hey,
everyone else is guessing, so why not me? So let's start with real DDL:
CREATE TABLE Products
(sku CHAR(3) NOT NULL PRIMARY KEY
CHECK (sku LIKE '[A-Z][0-9][0-9]'),
product_name CHAR(15) NOT NULL,
...);
CREATE TABLE Packages
(package_sku CHAR(4) NOT NULL PRIMARY KEY
CHECK (package_sku LIKE 'P[A-Z][0-9][0-9]'),
sku CHAR(3) NOT NULL
REFERENCES Products(sku)
ON UPDATE CASCADE
ON DELETE CASCADE,
qty INTEGER NOT NULL CHECK(qty > 0),
PRIMARY KEY (package_sku, sku),
...);
So the "Super DeLuxe Gift Box" might be seven "C01", two "C02" and one
"B01". We find if we can make such a thing with a relational division.
The package_sku code will need a validation rule, so I decided that they
start with 'P'.
Was I close? Want to try again with usable specs?
--CELKO--
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
|
|
|
|
|
|
|