Query help

My table are

Customer: customerId ,name

Order: orderId, customerId, product,date

I want to display all of the customer which have order or not. I want display name, product,date . If the customer do not order I want display only customer name.For example:

Name Product Date

John Video 09/20/2007

Mary -- ----

How can I write sql or sp?

I suggest you do some reading on SQL and joins in particular as this is something that you should learn so you can write these queries yourself.

DECLARE @.CUSTOMERTABLE (customeridint IDENTITY(1,1),name varchar(20))DECLARE @.ORDERSTABLE (orderidint IDENTITY(1,1), customeridint, productvarchar(20), orderdatedatetime)INSERT @.CUSTOMERVALUES ('Fred')INSERT @.CUSTOMERVALUES ('Joe')INSERT @.ORDERSVALUES (1,'Video',GetDate())SELECT c.name, o.product, o.orderdateFROM @.CUSTOMER cLEFTOUTER JOIN @.ORDERS oON o.customerid = c.customerid

|||

use left join instead of inner join

select cust.customerId ,cust.name,ord.orderId, ord.customerId, ord.product,ord.date from Customer cust left outer join orders ord on

cust.CustomerId = ord.CusomerId

|||

Hi,

I think you have to create a cross-tab query, it's ilttle tricky but interesting.

Check these following links

http://www.databasejournal.com/features/mssql/article.php/3521101

http://www.oreillynet.com/pub/a/network/2004/12/17/crosstab.html

or you can adopt the following solution

http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1131829,00.html

Regards,

Sandeep

|||

ASP.NET Dev:

I think you have to create a cross-tab query,

That's not necessary as they aren't pivoting any data (or at least that doesn't appear to be the case based on their description).

|||

Ozo:

How can I write sql or sp?

you can write sql as..

select customer.name, order.product, order.orderdate from customer left outer join order on customer.customerid = order.customerid

and sp as..

CREATE PROCEDURE [dbo].[usp_CustomerOrder]
AS
BEGIN
SET NOCOUNT ON
select customer.name, order.product, order.orderdate from customer leftouter join order on customer.customerid = order.customerid
END

|||

Tahnk you for your helping.

|||

Ozo:

Tahnk you for your helping.

You should also mark all the posts that helped by using the "Mark As Answer" link so that future readers with the same problem will know which methods to use.

|||

I have a new question I want to display the latest order from the customer .Can you help me?

|||

Yes, but please start a new question if you have something else to ask as it helps keep the forum tidy and easier to search.

Query for multiple values

I was able to create a query that returns data based on a single order
number. Now I need to create a query that returns data based on
multiple order numbers. Obviously, when I run this query, it prompts me
for an order number. How do I change this so I can enter multiple order
numbers?
SELECT ORDNUMBE, CUSTNAME
FROM SOP10100
WHERE (ORDNUMBE = @.ordnumbe)
Thanks!http://www.sommarskog.se/arrays-in-sql.html
<2retread@.gmail.com> wrote in message
news:1126021459.621452.89010@.z14g2000cwz.googlegroups.com...
>I was able to create a query that returns data based on a single order
> number. Now I need to create a query that returns data based on
> multiple order numbers. Obviously, when I run this query, it prompts me
> for an order number. How do I change this so I can enter multiple order
> numbers?
> SELECT ORDNUMBE, CUSTNAME
> FROM SOP10100
> WHERE (ORDNUMBE = @.ordnumbe)
> Thanks!
>|||See if this helps:
Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
AMB
"2retread@.gmail.com" wrote:

> I was able to create a query that returns data based on a single order
> number. Now I need to create a query that returns data based on
> multiple order numbers. Obviously, when I run this query, it prompts me
> for an order number. How do I change this so I can enter multiple order
> numbers?
> SELECT ORDNUMBE, CUSTNAME
> FROM SOP10100
> WHERE (ORDNUMBE = @.ordnumbe)
> Thanks!
>|||CREATE PROCEDURE Foobar ( <other parameters>, IN p1 INTEGER, IN p2
INTEGER, .. IN pN INTEGER) -- default missing values to NULLs
BEGIN
SELECT foo, bar, blah, yadda, ...
FROM Floob
WHERE my_col
IN (SELECT DISTINCT parm
FROM (VALUES (p1), (p2), .., (pN)) AS ParmList(parm)
WHERE parm IS NOT NULL
AND <other conditions> )
AND <more predicates>;
<more code>;
END;
In SQL Server, the VALUES() table constructor has to be faked with
(SELECT p1 UNION SELECT p2.. SELECT pn) AS Parmlist (p).|||On 6 Sep 2005 20:19:28 -0700, --CELKO-- wrote:

>CREATE PROCEDURE Foobar ( <other parameters>, IN p1 INTEGER, IN p2
>INTEGER, .. IN pN INTEGER) -- default missing values to NULLs
>BEGIN
>SELECT foo, bar, blah, yadda, ...
> FROM Floob
> WHERE my_col
> IN (SELECT DISTINCT parm
> FROM (VALUES (p1), (p2), .., (pN)) AS ParmList(parm)
> WHERE parm IS NOT NULL
> AND <other conditions> )
> AND <more predicates>;
><more code>;
>END;
>In SQL Server, the VALUES() table constructor has to be faked with
>(SELECT p1 UNION SELECT p2.. SELECT pn) AS Parmlist (p).
Hi Joe,
And how exactly is your query better than
SELECT foo, bar, blah, yadda, ...
FROM Floob
WHERE my_col
IN (p1, p2, .., pN)
AND <more predicates>;
?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I usually wind up adding some CAST(), UPPER() and other procedures to
the SELECTs or in theVALUES() list.|||On 7 Sep 2005 12:49:10 -0700, --CELKO-- wrote:

>I usually wind up adding some CAST(), UPPER() and other procedures to
>the SELECTs or in theVALUES() list.
Hi Joe,
Assuming that you meant "functions", not "procedures", there's still no
reason to make it so complicated:
WHERE my_col IN (UPPER(p1), CAST(p2 AS verchar(10), ..., LOWER(pN))
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> Assuming that you meant "functions", not "procedures", there's still no r
eason to make it so complicated: <<
Opps! I grant that you can do a lot of edit work with CASE epxrssions,
too:
CASE WHEN @.p1 NOT BETWEEN 1 AND 10 THEN NULL ELSE @.p1 END
But it is hard to do things that involve another table, such as
translating codes in the parameters to the target table format::
(SELECT shoesize_eur
FROM ShoeSizes.
WHERE UPPER (shoesize_usa) = @.p1)
or multiple-parameter look-ups:
(SELECT address_nbr
FROM StandardAddresseNumbers AS A
WHERE CAST (x_ord AS INTEGER) = @.p1
AND CAST (y_ord AS INTEGER) = @.p2)|||On 8 Sep 2005 07:57:01 -0700, --CELKO-- wrote:

>Opps! I grant that you can do a lot of edit work with CASE epxrssions,
>too:
>CASE WHEN @.p1 NOT BETWEEN 1 AND 10 THEN NULL ELSE @.p1 END
>But it is hard to do things that involve another table, such as
>translating codes in the parameters to the target table format::
>(SELECT shoesize_eur
> FROM ShoeSizes.
>WHERE UPPER (shoesize_usa) = @.p1)
>or multiple-parameter look-ups:
>(SELECT address_nbr
> FROM StandardAddresseNumbers AS A
> WHERE CAST (x_ord AS INTEGER) = @.p1
> AND CAST (y_ord AS INTEGER) = @.p2)
Hi Joe,
Assuming that we're still discussing what can and can't be placed in an
IN (expression, expression, ...) string, I still fail to see how CASE
expressions and subqueries like the examples you posted would introduce
the need to move from IN with a list of expressions to IN with a
subquery, like the one you posted at the start of this thread.
I don't have the full SQL-92 standard at my disposal, but Books Online
says this:
Syntax
test_expression [ NOT ] IN
(
subquery
| expression [ ,...n ]
)
Arguments
(snip)
expression [,...n]
Is a list of expressions to test for a match. All expressions must be of
the same type as test_expression.
There are no further requirements for the expressions in the list of
expressions. So you should bne able to just slam the subqueries in
there.
Of course, the end result would be quite complicated - but adding an
extra layer of complexity by using a subquery with SELECT .. FROM VALUES
(...) would make it even more complicated, not less so!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

query Execution time-out settings

In order to stop some occasional blocking issues could I set the "query
execution time-out settings" to 30 seconds for example. I understand it will
kill the queries that exceed 30 seconds, but I would rather have that then t
o
have everyone "lockup". I also understand that blocking is caused by some
ineffecient queries, however untill we identify the problematic queries I
would like to kill the blocking automatically.
Thanks for any help.
JamesOf course you can make such a change.
Please recognize that several activities, including reporting, usually
require longer running queries, and your proposed change may cause those
other activities and reports to mal-function.
Using Profiler, you should be able to identify the blocking queries
relatively easily.
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"James" <James@.discussions.microsoft.com> wrote in message
news:11D15332-D7BC-4694-A3BB-15EB8F428406@.microsoft.com...
> In order to stop some occasional blocking issues could I set the "query
> execution time-out settings" to 30 seconds for example. I understand it
> will
> kill the queries that exceed 30 seconds, but I would rather have that then
> to
> have everyone "lockup". I also understand that blocking is caused by some
> ineffecient queries, however untill we identify the problematic queries I
> would like to kill the blocking automatically.
> Thanks for any help.
> James|||Thanks Arnie for your reply
Do you know if this action will definitely kill blocking?
James
"Arnie Rowland" wrote:

> Of course you can make such a change.
> Please recognize that several activities, including reporting, usually
> require longer running queries, and your proposed change may cause those
> other activities and reports to mal-function.
> Using Profiler, you should be able to identify the blocking queries
> relatively easily.
> --
> Arnie Rowland
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "James" <James@.discussions.microsoft.com> wrote in message
> news:11D15332-D7BC-4694-A3BB-15EB8F428406@.microsoft.com...
>
>

Query detail as a field.

I have two tables(Order and OrderDetail) of master-detail relationship. I have a nchar field in the detail table called itemno. I want to query like:

Select Order.OrderNo, Order.Date, SUM(OrderDetail.ItemNo) as ItemNos,....

From Order Inner Join OrderDetail on Order.OrderID=OrderDetail.OrderID

Where Order.OrderID=10

so that the resulting field ItemNos will become a string in format "ItemNo01, ItemNo02, ItemNo03,...."

How can I write this query in T-SQL?

Thanks

write a UDF

something on the lines of

CREATE FUNCTION [dbo].[returnItemNoFormat]
(

@.ItemSum int

)
RETURNS string
AS
BEGIN

/*
DO ALL YOUR FORMATTING IN HERE AND THEN RETURN THE STRING
*/

RETURN @.RTN

END

then your query should look like:

Select

Order.OrderNo,
Order.Date,

dbo.returnItemNoFormat(SUM(OrderDetail.ItemNo)) as ItemNos,....

From Order

Inner Join OrderDetail on Order.OrderID=OrderDetail.OrderID

Where Order.OrderID=10

query based on the top MTD sales

hello,
I need to write a query based on the top MTD sales in the series of each fabrics within series of Sales Group and Prod Group

Order by: Sales Group (alphabetical ord) , Prod Group (alphabetical ord) , sort Fabric Group based on the TOP MTD sales

Sales Gr: Active
Prod gr: Adult, Girls, Plus, LG
Fabric Gr: 1,2,3,4,5,6,7,8,...

Sales Gr: Dance
Prod gr: Adult, Girls, Plus, LG
Fabric Gr: 1,2,3,4,5,6,7,8,...

Sales Gr: Yoga
Prod gr: Adult, Girls, Plus
Fabric Gr: 1,2,3,4,5,6,7,8,...

Thank youhello,
I need to write a query based on the top MTD sales in the series of each fabrics within series of Sales Group and Prod Group

Order by: Sales Group (alphabetical ord) , Prod Group (alphabetical ord) , sort Fabric Group based on the TOP MTD sales
Each Fabric group has to be orderd by the Top MTD.

Sales Gr: Active
Prod gr: Adult, Girls, Plus, LG
Fabric Gr: a,b,c,d,e,f,g...
StyleNum: 1,2,3,4,5,6...

Sales Gr: Dance
Prod gr: Adult, Girls, Plus, LG
Fabric Gr: a,b,c,d,e,f,g...
StyleNum: 1,2,3,4,5,6...

Sales Gr: Yoga
Prod gr: Adult, Girls, Plus
Fabric Gr: a,b,c,d,e,f,g...
StyleNum: 1,2,3,4,5,6...|||you need to post some transaction data and the expected output of the query for us to understand where exactly the problem is.|||Hi

I'm afraid your description is a little breathless and difficult to follow. I suspect this is why you have not yet had a response. Please could you write in natural English what purpose the query is to serve?

Ta|||Hello,
Attached is a data file in which I need a sort as I said below.

Thank you|||Query has to show data in alphabetical order for each group (Sales Group, Product Group). Then the Fabric Group has to be at the highest order first based on the MDT Sales and then the Fabric Group has to be grouped by description itself.
Then style number and a color code.

Thank you

Sales Group Product Group FabricGroup STYLE NUM Retail Price WTD_Unit WTD_Sale WTD Avg Sales MTD_Unit MTD_Sale MTD_Avg_Sales YTD_Units YTD_Sales YTD_Avg_Sales
ADULT ACTIVE SUPPLEX 1561 $30.00 25 $716.40 $28.66 101 $2,975.40 $29.46 626 $17,896.50 $28.59
ADULT ACTIVE TRIGROUP 1677 $75.00 6 $344.34 $57.39 39 $2,282.02 $58.51 24 $1,287.60 $53.65
ADULT ACTIVE TRIGROUP 1677 $75.00 6 $344.34 $57.39 39 $2,282.02 $58.51 100 $5,755.44 $57.55
ADULT ACTIVE TRIGROUP 1677 $75.00 2 $117.58 $58.79 34 $2,009.67 $59.11 38 $2,125.20 $55.93
ADULT ACTIVE TRIGROUP 1677 $75.00 2 $117.58 $58.79 34 $2,009.67 $59.11 126 $7,229.39 $57.38
ADULT ACTIVE SUPPLEX 1562 $31.50 11 $343.35 $31.21 65 $1,997.73 $30.73 585 $17,091.59 $29.22
ADULT ACTIVE SUPPLEX 1561 $30.00 8 $223.20 $27.90 35 $1,024.80 $29.28 154 $4,335.60 $28.15
ADULT ACTIVE COTN LYCRA 8150 $21.00 8 $168.00 $21.00 49 $1,019.34 $20.80 404 $8,098.44 $20.05
ADULT ACTIVE YOGA CVC 5720 $33.00 11 $327.69 $29.79 31 $935.55 $30.18 220 $6,721.77 $30.55
ADULT ACTIVE SUPPLEX 5145 $39.00 6 $221.91 $36.99 23 $873.99 $38.00 212 $7,781.67 $36.71
ADULT ACTIVE SUPPLEX 7360 $23.00 14 $318.78 $22.77 39 $868.48 $22.27 178 $3,822.37 $21.47
ADULT ACTIVE SUPPLEX 1561 $30.00 6 $179.40 $29.90 29 $859.20 $29.63 35 $1,037.40 $29.64
ADULT ACTIVE TRIGROUP 1847 $48.00 2 $95.04 $47.52 17 $797.76 $46.93 25 $1,168.32 $46.73
ADULT ACTIVE YOGA CVC 8298 $42.00 3 $115.50 $38.50 19 $761.46 $40.08 177 $6,994.68 $39.52
ADULT ACTIVE SUPPLEX 7111 $36.00 7 $232.92 $33.27 22 $752.76 $34.22 98 $3,303.36 $33.71
ADULT ACTIVE TRIGROUP 1672 $48.00 0 $0.00 $0.00 18 $715.82 $39.77 74 $2,806.50 $37.93
ADULT ACTIVE TRIGROUP 1671 $44.00 2 $69.98 $34.99 19 $660.61 $34.77 82 $2,680.23 $32.69
ADULT ACTIVE STRETCH COTN 8202 $17.00 13 $209.95 $16.15 39 $635.97 $16.31 103 $1,633.70 $15.86
ADULT ACTIVE COTN LYCRA 8379 $36.00 2 $71.28 $35.64 19 $635.76 $33.46 175 $5,721.48 $32.69
ADULT ACTIVE YOGA CVC 5369 $36.00 5 $177.12 $35.42 18 $626.40 $34.80 310 $10,296.72 $33.22
ADULT ACTIVE COTN LYCRA 8166 $30.00 0 $0.00 $0.00 22 $625.20 $28.42 99 $2,781.60 $28.10
ADULT ACTIVE TRIGROUP 1845 $75.00 0 $0.00 $0.00 8 $597.00 $74.63 19 $1,401.00 $73.74
ADULT ACTIVE SUPPLEX 1561 $30.00 3 $90.00 $30.00 20 $596.40 $29.82 28 $817.20 $29.19
ADULT DANCE SUPPLEX 9963 $20.00 0 $0.00 $0.00 3 $35.49 $11.83 3 $35.49 $11.83
ADULT DANCE SUPPLEX 9963 $20.00 0 $0.00 $0.00 3 $35.49 $11.83 10 $193.60 $19.36
ADULT DANCE SUPPLEX 9963 $20.00 0 $0.00 $0.00 3 $35.49 $11.83 21 $293.65 $13.98
ADULT DANCE NYLON 9090 $15.50 1 $8.81 $8.81 4 $35.42 $8.86 4 $35.42 $8.86
ADULT DANCE NYLON 9090 $15.50 1 $8.81 $8.81 4 $35.42 $8.86 13 $188.79 $14.52
ADULT DANCE SATIN TOUCH 2809 $36.00 0 $0.00 $0.00 1 $35.28 $35.28 6 $207.36 $34.56
ADULT DANCE SATIN TOUCH 2812 $36.00 0 $0.00 $0.00 1 $35.28 $35.28 14 $483.48 $34.53
ADULT DANCE NYLON 9090 $15.50 0 $0.00 $0.00 4 $35.24 $8.81 17 $244.90 $14.41
ADULT DANCE NYLON 9090 $15.50 0 $0.00 $0.00 4 $35.24 $8.81 18 $152.02 $8.45
ADULT DANCE BODY SCULPT 2813 $35.00 0 $0.00 $0.00 1 $35.00 $35.00 9 $300.30 $33.37
ADULT DANCE BODY SCULPT COLLECTION (2006) 2813 $35.00 0 $0.00 $0.00 1 $35.00 $35.00 11 $359.45 $32.68|||Dupe post
http://www.dbforums.com/showthread.php?t=1217893

I still don't get it. There is a sticky at the top of this forum. It asks you to:
Post table DDL
Sample data in DML format
Expected results

It also explains how to do any of these if you don't know how. Please could you do this?|||FYI (not the OP) - this is also live in SQL Server:
http://www.dbforums.com/showthread.php?t=1217908

:D|||SalesGroup Text 255
ProductGroup Text 255
FabricGroup Text 255
TYPEYR Text 5
STYLE_NUM Text 50
STYLE_DESC Text 255
COLORCODE Text 3
COLORD Text 255
Retail Price Currency 8
WTD_Units_05_25_2006 Double 8
WTD_Sales_05_25_2006 Currency 8
WTD_Avg_Sales_05_25_2006 Currency 8
MTD_Units_May 2006 Double 8
MTD_Sales_May 2006 Currency 8
MTD_Avg_Sales_May 2006 Currency 8
YTD_Units_2006 Double 8
YTD_Sales_2006 Currency 8
YTD_Avg_Sales_2006 Currency 8

SalesGroup (In Alphabetical Order - Adult, Girls, Plus, Mens, Irrs),
for each Sales gr ther are the
ProductGroup (Alphabetical Order - Active, Dace, Legwr, Prvt),
For each Sales Gr and a Prod Group there are the
FabricGroups (has to apear as the highest total first for each FabricGroup based on the MTD Sales (Suplex, Cotton, Cotton LCRY)
and then it has to group the FabricGroup together. (If I have 10 entries for the Supplex and it is at the highest total, then the highest total has to be first and the rest apper after)
StyleID (1,2,3,4,5,6,7..),
Color (01,02,03,04,05),
RetailAmount,
WeeklySales,
MonthlySales
YTDSales

Thank you so much.|||the thing to do when there's a cross post is not to put pointers from one to the other, but to merge the threads, as i have done here

that the merged threads may be more confusing than separate threads is not our problem but the original poster's|||the thing to do when there's a cross post is not to put pointers from one to the other, but to merge the threads, as i have done hereLol - ya :D The pointers were more to flag up the posts to the Orange users than to create some complex web of a thread.

Although I do like to alternate my responses from one thread to another when I do get involved. That's just mischief though.