shipping label query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

shipping label query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-09-03 : 08:13:31

Mahmut writes "Hi Guys,

I have two simple tables, [orders] and [orderdetails].

[orders] has orderid, firstname, lastname.
[orderdetails] has productTitle

I'm trying to build a query like this:

select firstname, lastname

, (select productTitle from orderDetails where orders.orderID = orderDetails.orderID) as productTitle

from orders

Unfortunately, this doesn't work cause sub query returns more than 1 row. I tried to build a tempdb with a detailedContent field that updates itself from orderdetails table, but no luck. Following didn't work as I expected.

update t
set content = content + '&' + orderdetails.productTitle
FROM t inner join orderdetails on t.orderID = orderdetails.orderID

Although I used an inner join here, it only looped through rows of t (not t inner join orderdetails).

All I want to do is have a label like this:

Firstname, Lastname
Address
Shopping Cart Contents
4 of This
5 of That

like the ones amazon prints on boxes. Please tell me if this is possible to do with t-sql or should I use a scripting language ?

Thanks guys,

Mahmut"

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-03 : 15:04:31

In fact your problem is reduced to concatenating-aggregating string values.
A bit tedious it is. And these labels, are they literally paper labels? If so,
where they are supposed to be designed? I'm afraid you need no any
aggregating tricks. The simple INNER JOIN of two tables is quite enough.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-03 : 15:16:13

you need to use a simple report writer, like Access.

write your query to include orders.* linked to orderdetails.

then in the report, the group will be on orderID.

in the group header put in the order information.

in the detail, put in the order details.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources