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 Help

Hi All,

I want to display the data in vertical format though the data is stored horizontally in the datatable.Suppose i have table with five columns-id.a,b,c,d.If i use a select statement that will give me the data in the format-ID A B C D

but now i want to display the data as

ID A

ID B

ID C

ID D

Any help on this pls??I m thinking to use the self join but would it take a long time??

Thanks

select id,a

union

select id,b

union

select id,c

union

select id,d

|||It sounds to me more like you require a pivot query rather than a union. If you have Sql Server 2005 you can do pivot queries but, unfortunately, not in the older versions. There a good article you can readhere that explains how to do pivot queries.|||

You can do pivot tables in Sql Server 2000. Just search for "pivot tables" in the Books Online. The example there was greatly helpful to me with the same issue.

Hope this helps

query for this purpose

We have a worktran table
WoNo Jobno Status
1 01 Fullfill
1 02 Pending
2 01 Fullfill
2 02 Fullfill
We need to display those WoNo having all its rows status=fulfill
In the example above, it should display only WoNo 2 because it contain
all Fullfill status
We tried - select distinct WoNo from worktran where status='Fullfill'
But it does not make sure that all of the rows for certian WoNo have
status='Fulfill'
Thanks in advance.Hello, MadhavC
You can use one of these queries:
SELECT WoNo FROM worktran
GROUP BY WoNo
HAVING COUNT(*)=SUM(CASE WHEN Status='Fulfill' THEN 1 ELSE 0 END)
SELECT DISTINCT WoNo FROM worktran a
WHERE NOT EXISTS (
SELECT * FROM worktran b
WHERE a.WoNo=b.WoNo
AND b.WoNo<>'Fulfill'
)
You should also take a look at the following article, by Joe Celko,
regarding Relational Division:
http://www.dbazine.com/ofinterest/oi-articles/celko1
Razvan|||Thanks for your reply it worked.

Query Editor: how to display individual row vertically

In query editor I displayed a single row from a table. The row is so long that I need to scroll horizontally back and forth to check out it's fields. Using t-sql (or otherwise) can I display the row like this: (vertically)

\

Field Name 1: < data value 1>

Field Name 2: < data value 2>

Field Name 3: < data value 3>

Field Name 4: < data value 4>

etc.

TIA,

barkingdog

Dog:

If you are using SQL Server 2005, you ought to be able to use an UNPIVOT

Dave

|||

Dog:

That SQL 2005 code might look something like this:

-- -
-- In order for UNPIVOT to work, the data must be homogeneous.
-- For this reason, all of the fields are transformed into a
-- varchar (40) string.
--
-- Also note the ISNULL function on field 3. If this is not
-- done, this row will not be displayed.
-- -
select convert (varchar (25), FieldName + ':') as FieldName,
FieldValue
from ( select convert (varchar (40), 'This is a test.')
as [Field Name 1],
convert (varchar (40), 'This is the 2nd field.')
as [Field Name 2],
convert (varchar (40), isnull (null, '[Null]'))
as [Field Name 3],
convert (varchar (40), 45.27)
as [Field Name 4]
) x
unpivot (FieldValue for FieldName
in ( [Field Name 1], [Field Name 2],
[Field Name 3], [Field Name 4]
)
) as xx

--
-- Sample Output:
--

-- FieldName FieldValue
-- - -
-- Field Name 1: This is a test.
-- Field Name 2: This is the 2nd field.
-- Field Name 3: [Null]
-- Field Name 4: 45.27

-- (4 row(s) affected)

|||

If you are using SQL 2000 you might try something like:

select 'Field Name 1:' as FieldName,
'This is a test.' as fieldValue
union all
select 'Field Name 2:',
'This is the 2nd field.'
union all
select '...', ' '
union all
select 'Field Name N: ', 'Nth piece of data'

--
-- Sample Output:
--

-- FieldName fieldValue
-- -
-- Field Name 1: This is a test.
-- Field Name 2: This is the 2nd field.
-- ...
-- Field Name N: Nth piece of data

-- (4 row(s) affected)

Query DateTime DataType for Current or Future Events

I have a sql server express 2005 database with a table named Events with a column named Date (datetime datatype). I want a query that will display all rows that are either current or future but not past. I suspect there is a simple way of doing this. As a Newbie searching archived threads this is what I have come up with so far. I determine the number of days from present:

SELECTDATEDIFF(day, Date,GETDATE())AS NumberOfDays

FROMEvents

This yields number of days from present with positive numbers in the past and negative numbers in the future. Thus setting a WHERE clause to <= 0 would limit my results to present or future events. Something like this:

SELECT*

FROM Events

WhereDATEDIFF(day, Date,GETDATE())AS NumberOfDays<= 0

The error message states: "Incorrect syntax near the keyword 'AS'"

This feels like a clumsy way to approach this problem, but I have to start where I am.

Any suggestions on how to proceed will be greatly appreciated.

SELECT*FROM EventsWhereDATEDIFF(day, Date,GETDATE())<= 0

or

select*FROM EventsWHERE Date>=GETDATE()

|||Thanks limno. Exactly what I needed.

Query based Textbox

Hello Everyone,

How do you display a dataset query result in a textbox?

Thanks for the easy answer...

drag and drop the textbox

set the 'datasetname' on the textbox to whatever your dataset is called

then in the expression editor you can access the result

boink

actually thinking about it, you probably don't need to do the 'datasetname' bit

just right click on the text box and edit expression

then in the panes at the bottom (left hand side) you can see all the cols in your dataset|||

Thank you for the quick reply...I will try this and see if it works...

I have a Parameter that uses a ClientID as the selected value and I tried to have a textbox display the User Parameter Choice but it only diplayed the ID and I want the corresponding text (Client Name Value). But I can not find the correct expression.

I will post a follow-up to this and mark you as answered or not...

Thanks!

|||

Not working...I can not select a Dataset for the textbox

Any other ideas?

|||=Parameters!ClientID.Label|||

Got the following error:

The Value expression used in textbox ‘Client’ returned a data type that is not valid.

|||Is it a multi-value parameter?|||

yes

|||Try this: =Join(Parameters!ClientID.Label, ", ")|||

Great!

That was the ticket!

Thank You!

Query based Textbox

Hello Everyone,

How do you display a dataset query result in a textbox?

Thanks for the easy answer...

drag and drop the textbox

set the 'datasetname' on the textbox to whatever your dataset is called

then in the expression editor you can access the result

boink

actually thinking about it, you probably don't need to do the 'datasetname' bit

just right click on the text box and edit expression

then in the panes at the bottom (left hand side) you can see all the cols in your dataset|||

Thank you for the quick reply...I will try this and see if it works...

I have a Parameter that uses a ClientID as the selected value and I tried to have a textbox display the User Parameter Choice but it only diplayed the ID and I want the corresponding text (Client Name Value). But I can not find the correct expression.

I will post a follow-up to this and mark you as answered or not...

Thanks!

|||

Not working...I can not select a Dataset for the textbox

Any other ideas?

|||=Parameters!ClientID.Label|||

Got the following error:

The Value expression used in textbox ‘Client’ returned a data type that is not valid.

|||Is it a multi-value parameter?|||

yes

|||Try this: =Join(Parameters!ClientID.Label, ", ")|||

Great!

That was the ticket!

Thank You!

query analyzer2

hey all,
if i declare a variable and set it is there a way i can get it to display on
the messages window or the grid?
thanks,
rodchar
The grid:
SELECT @.varname
The messages windows
PRINT @.varname
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:12D27260-BADD-4F0F-BBCB-1E1ABC2C17C4@.microsoft.com...
> hey all,
> if i declare a variable and set it is there a way i can get it to display on
> the messages window or the grid?
> thanks,
> rodchar
|||sorry bout that, i figured it out
declare @.myVar datetime
set @.myVar = getdate()
select @.myVar
thanks,
rodchar
"rodchar" wrote:

> hey all,
> if i declare a variable and set it is there a way i can get it to display on
> the messages window or the grid?
> thanks,
> rodchar
|||cool i didn't know about the print function.
"Tibor Karaszi" wrote:

> The grid:
> SELECT @.varname
> The messages windows
> PRINT @.varname
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "rodchar" <rodchar@.discussions.microsoft.com> wrote in message
> news:12D27260-BADD-4F0F-BBCB-1E1ABC2C17C4@.microsoft.com...
>
|||Describe the procedure that will let the user tell Windows to open any file with an .RPT extension with Notepad.
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||Start Notepad, File, Open, Files of type: all files, select the file, OK. You can also change the
file extension in QA, Tools, Options.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Glennette Adams" <gadams6822@.vccs.edu> wrote in message
news:eSfm$9X2FHA.2472@.TK2MSFTNGP12.phx.gbl...
> Describe the procedure that will let the user tell Windows to open any file with an .RPT extension
> with Notepad.
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...