Help with Query

[phprockskasbah]

I am working on the constraints of an existing database.

The way the products are set up when they have options such as color, size, etc makes it hard to query. These values are written always as option and value and there can be several per product order. So if I query them out, I get many rows. Is there any way to subroutine this or work around it?

The problem I have is that the last 2 columns can have anywhere from 1 to 4 entries causing the results to have 1-4 rows.

example...

SELECT
date_purch,
order_no,
customer,
email,
product_model,
product_price,
product_option,
product_option_value

FROM
ordertable

order by customer


returns

2006-09-20 | 00001 | customer cust@cust.com | Model# | Opt Text | opt nameon tshirt
2006-09-20 | 00001 | customer cust@cust.com | Model# | Opt Color | opt r 255 g 255 b 255
2006-09-20 | 00001 | customer cust@cust.com | Model# | Opt 2nd Color | opt r0 g0 b0
2006-09-20 | 00001 | customer cust@cust.com | Model# | Opt Comments | please ship to my house
2006-09-20 | 00002 | customer cust@cust.com | Model# | Opt Text | opt nameon mug
2006-09-20 | 00002 | customer cust@cust.com | Model# | Opt Color | opt r 150 g 57 b 125
2006-09-20 | 00002 | customer cust@cust.com | Model# | Opt 2nd Color | opt r20 g130 b55
2006-09-20 | 00002 | customer cust@cust.com | Model# | Opt Comments | gift for uncle ship to his house
2006-09-20 | 00003 | customer cust@cust.com | Model# | Opt Text | opt nameonmousepad
2006-09-20 | 00003 | customer cust@cust.com | Model# | Opt Color | opt r 150 g 57 b 125
2006-09-20 | 00003 | customer cust@cust.com | Model# | Opt 2nd Color | opt r20 g130 b55
2006-09-20 | 00003 | customer cust@cust.com | Model# | Opt Comments | can you use arial instead of times new roman

it would be cool if this could be cleaned up to look like

2006-09-20 | 00001 | customer cust@cust.com | Model# | Opt Text | opt nameon tshirt | Opt Color | opt r 255 g 255 b 255 | Opt 2nd Color | opt r0 g0 b0 | Opt Comments | please ship to my house

2006-09-20 | 00002 | customer cust@cust.com | Model# | Opt Text | opt nameon mug | Opt Color | opt r 150 g 57 b 125 | Opt 2nd Color | opt r20 g130 b55 | Opt Comments | gift for uncle ship to his house

2006-09-20 | 00003 | customer cust@cust.com | Model# | Opt Text | opt nameonmousepad | Opt Color | opt r 150 g 57 b 125 | Opt 2nd Color | opt r20 g130 b55 | Opt Comments | can you use arial instead of times new roman



Any help would be greatly appreciated. I am going square eyed staring at it.

[jezemine]

This design breaks normalization.

You ought to have a separate table for product options (called ProductOptions, say). ProductOptions should have a fk back to OrderTable.order_no (assuming that's your pk).

That way, for each order you you only have one row in OrderTable.

[jezemine]

For that matter, you should have a separate products table also. an order is not a product, so orders and products should be in separate tables.

[phprockskasbah]

thank you for responding... would it help to know these items are on 3 different tables and i have joined them with an inner join. this is the real structure.....

SELECT
ot.date_purch,
ot.order_no,
ot.customer,
ot.email,
po.product_model,
po.product_price,
poa.product_option,
poa.product_option_value

ot - order table
po - products ordered
poa - products ordered attributes

[jezemine]

take a look at "cross-tab reports" in BOL.

or if you are on 2005 you can use the new PIVOT/UNPIVOT syntax:

http://www.databasejournal.com/featu...le.php/3521101

[phprockskasbah]

thanks... i am closer....yet still have trouble

2006-09-20 | 00001 | customer cust@cust.com | Model# | Opt Text | opt nameon tshirt
2006-09-20 | 00001 | customer cust@cust.com | Model# | Opt Color | opt r 255 g 255 b 255
2006-09-20 | 00001 | customer cust@cust.com | Model# | Opt 2nd Color | opt r0 g0 b0
2006-09-20 | 00001 | customer cust@cust.com | Model# | Opt Comments | please ship to my house
2006-09-20 | 00002 | customer cust@cust.com | Model# | Opt Text | opt nameon mug
2006-09-20 | 00002 | customer cust@cust.com | Model# | Opt Color | opt r 150 g 57 b 125
2006-09-20 | 00002 | customer cust@cust.com | Model# | Opt 2nd Color | opt r20 g130 b55
2006-09-20 | 00002 | customer cust@cust.com | Model# | Opt Comments | gift for uncle ship to his house
2006-09-20 | 00003 | customer cust@cust.com | Model# | Opt Text | opt nameonmousepad
2006-09-20 | 00003 | customer cust@cust.com | Model# | Opt Color | opt r 150 g 57 b 125
2006-09-20 | 00003 | customer cust@cust.com | Model# | Opt 2nd Color | opt r20 g130 b55
2006-09-20 | 00003 | customer cust@cust.com | Model# | Opt Comments | can you use arial instead of times new roman

now looks like

date | order_id | Customer | model | pers_text | pers_color | pers_option

2006-09-20 | 00001 | customer cust@cust.com | Model# | opt nameon tshirt | null | null
2006-09-20 | 00001 | customer cust@cust.com | Model# | null | opt r 255 g 255 b 255 | null
2006-09-20 | 00001 | customer cust@cust.com | Model# | null | null | opt r0 g0 b0
2006-09-20 | 00002 | customer cust@cust.com | Model# | opt nameon mug | null | null
2006-09-20 | 00002 | customer cust@cust.com | Model# | null | opt r 150 g 57 b 125 | null
2006-09-20 | 00002 | customer cust@cust.com | Model# | null | null | opt r20 g130 b55
2006-09-20 | 00003 | customer cust@cust.com | Model# | opt nameonmousepad | null | null
2006-09-20 | 00003 | customer cust@cust.com | Model# | null | opt r 150 g 57 b 125 | null
2006-09-20 | 00003 | customer cust@cust.com | Model# | null | null | opt r20 g130 b55

[phprockskasbah]

if i group by order number i get

2006-09-20 | 00001 | customer cust@cust.com | Model# | opt nameon tshirt | null | null
2006-09-20 | 00002 | customer cust@cust.com | Model# | opt nameon mug | null | null
2006-09-20 | 00003 | customer cust@cust.com | Model# | opt nameonmousepad | null | null

[phprockskasbah]

this is my real query...

SELECT
orders.date_purchased,
orders.orders_id,
orders.customers_name,
orders.customers_email_address,
orders_products.products_model,
(case orders_products_attributes.products_options when "Personalized Text" then orders_products_attributes.products_options_values else null end) AS Personalized_Text,
(case orders_products_attributes.products_options when "Personalized Color" then orders_products_attributes.products_options_values else null end) AS Personalized_Color,
(case orders_products_attributes.products_options when "Personalized Option" then orders_products_attributes.products_options_values else null end) AS Personalized_Option

FROM orders

inner join orders_products on orders_products.orders_id=orders.orders_id
inner join orders_products_attributes on orders_products_attributes.orders_products_id=orde rs_products.orders_products_id

where orders_products.products_model like '%-Pers-%' and orders.date_purchased > curdate()-86400 and orders.date_purchased >= DATE_SUB(curdate(),INTERVAL 62 day)

group by orders.orders_id

order by orders.orders_id, orders_products.products_model

[phprockskasbah]

i have tried everything...i am about to give up. is this even possible?????

[jezemine]

sorry, I just don't have time to look at this now. anyone else care to take a look?

[jezemine]

take a look at this link:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73951

[phprockskasbah]

I AM SOOOO THANKFUL!! I TOTALLY FORGOT ABOUT THE MAX() OPTION.

SELECT
orders.date_purchased,
orders.orders_id,
orders.customers_name,
orders.customers_email_address,
orders_products.products_model,
MAX(CASE orders_products_attributes.products_options = 'Personalized Text' THEN orders_products_attributes.products_options_values END) Personalized_Text,
MAX(CASE orders_products_attributes.products_options = 'Personalized Color' THEN orders_products_attributes.products_options_values END) Personalized_Color,
MAX(CASE orders_products_attributes.products_options = 'Personalized Option' THEN orders_products_attributes.products_options_values END) Personalized_Option

FROM orders

inner join orders_products on orders_products.orders_id=orders.orders_id
inner join orders_products_attributes on orders_products_attributes.orders_products_id=orde rs_products.orders_products_id

where orders_products.products_model like '%-Pers-%' and orders.date_purchased > curdate()-86400 and orders.date_purchased >= DATE_SUB(curdate(),INTERVAL 62 day)

group by orders.orders_id

order by orders.orders_id, orders_products.products_model

THANK YOU AGAIN FOR YOUR TIME AND PERSERVERANCE... I HAVE BEEN ON THIS WAY TOO LONG.

[jezemine]

you bet. now do ME a favor and check out my site: http://www.elsasoft.org