Need help with what seems like a pretty standard commerce solution.



Example tables:



ProdA, ProdB, ProdC, ProdD - four product tables

ProdID - common table with product IDs



The relationship between the ProdID table and the four product tables is the

Product_ID field.  The Product_ID field is the primary key/identity integer

column in the ProdID table.  The four product tables store product-specific

information.



When a user adds an item to their cart, the Product_ID is added to their

cart cookie.



To display a customer's cart contents, I use the following SQL Statement:



SELECT p.product_id, ISNULL(a.field1, ISNULL(b.field3, ISNULL(c.field2,

ISNULL(d.field1, 'Description Unavailable')))) AS description, p.price

FROM ProdID p

	LEFT OUTER JOIN ProdA a ON p.product_id = a.product_id

	LEFT OUTER JOIN ProdB b ON p.product_id = b.product_id

	LEFT OUTER JOIN ProdC c ON p.product_id = c.product_id

	LEFT OUTER JOIN ProdD d ON p.product_id = d.product_id

WHERE p.product_id IN (...list of product ids...)



The result of the statement above is a three column list showing the product

ID, product description and price.



So my question is:  Is there a better way to do this?  (This example is

dummied down a little - in reality, there is no common "description" field

in each product table.  Descriptions are sometimes made up of a

concatenation of various fields.)





SQL gurus?







Thanks,





Alvin

You should look at using a view for this.  Create a view combining the

information from all the tables and query the view.



=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

=3D=3D=3D=3D=3D=3D=3D=3D=3D

Kyle M. Burns, MCSD

ECommerce Technology Manager

Centra Credit Union

kburns@c...







-----Original Message-----

From: Alvin Ling [mailto:alvin.ling@i...]

Sent: Tuesday, August 21, 2001 2:18 PM

To: ASP Databases

Subject: [asp_databases] SQL Statement for displaying cart contents





Need help with what seems like a pretty standard commerce solution.



Example tables:



ProdA, ProdB, ProdC, ProdD - four product tables

ProdID - common table with product IDs



The relationship between the ProdID table and the four product tables 

is the

Product_ID field.  The Product_ID field is the primary key/identity 

integer

column in the ProdID table.  The four product tables store 

product-specific

information.



When a user adds an item to their cart, the Product_ID is added to 

their

cart cookie.



To display a customer's cart contents, I use the following SQL 

Statement:



SELECT p.product_id, ISNULL(a.field1, ISNULL(b.field3, ISNULL(c.field2,

ISNULL(d.field1, 'Description Unavailable')))) AS description, p.price

FROM ProdID p

	LEFT OUTER JOIN ProdA a ON p.product_id =3D a.product_id

	LEFT OUTER JOIN ProdB b ON p.product_id =3D b.product_id

	LEFT OUTER JOIN ProdC c ON p.product_id =3D c.product_id

	LEFT OUTER JOIN ProdD d ON p.product_id =3D d.product_id

WHERE p.product_id IN (...list of product ids...)



The result of the statement above is a three column list showing the 

product

ID, product description and price.



So my question is:  Is there a better way to do this?  (This example is

dummied down a little - in reality, there is no common "description" 

field

in each product table.  Descriptions are sometimes made up of a

concatenation of various fields.)





SQL gurus?







Thanks,





Alvin