Newbie Question

Table 1
Order Number
1234
4321
5601
Table 2
Order Number
1234
4321
How do I Get SQL to Return that the Order number 5601 does not match any Order number in Table 2:Try:

SELECT Table1.OrderNumber
FROM Table1 LEFT JOIN Table2 ON Table1.OrderNumber = Table2.OrderNumber
WHERE Table2.OrderNumber Is Null

Newbie *easy/stupid?* question

Hi everyone,
I'm just learning sql and I'm trying to do the following to the pubs database:
For each order that include the books with title_id PC1035 or BU1032, list
the order number, order date, along with title_id, title, and a quantity of
each book included in order. Put the list in order of order date (in
descending order), then title in alphabetical order.
I've never written a query like that (I've done basic queries), but if
anyone can help it'd be appreciated.
Thank you,
Kristen.
Kristen
The following query should help you out:
SELECT s.ord_num, s.ord_date, s.title_id, t.title, s.qty
FROM pubs.dbo.sales s JOIN pubs.dbo.titles t
ON s.title_id = t.title_id
WHERE s.title_id = 'PC1035'
OR s.title_id = 'BU1032'
ORDER BY s.ord_date DESC, t.title ASC
- Peter Ward
WARDY IT Solutions
"Kristen" wrote:

> Hi everyone,
> I'm just learning sql and I'm trying to do the following to the pubs database:
> For each order that include the books with title_id PC1035 or BU1032, list
> the order number, order date, along with title_id, title, and a quantity of
> each book included in order. Put the list in order of order date (in
> descending order), then title in alphabetical order.
> I've never written a query like that (I've done basic queries), but if
> anyone can help it'd be appreciated.
> Thank you,
> Kristen.

Newbie *easy/stupid?* question

Hi everyone,
I'm just learning sql and I'm trying to do the following to the pubs databas
e:
For each order that include the books with title_id PC1035 or BU1032, list
the order number, order date, along with title_id, title, and a quantity of
each book included in order. Put the list in order of order date (in
descending order), then title in alphabetical order.
I've never written a query like that (I've done basic queries), but if
anyone can help it'd be appreciated.
Thank you,
Kristen.Kristen
The following query should help you out:
SELECT s.ord_num, s.ord_date, s.title_id, t.title, s.qty
FROM pubs.dbo.sales s JOIN pubs.dbo.titles t
ON s.title_id = t.title_id
WHERE s.title_id = 'PC1035'
OR s.title_id = 'BU1032'
ORDER BY s.ord_date DESC, t.title ASC
- Peter Ward
WARDY IT Solutions
"Kristen" wrote:

> Hi everyone,
> I'm just learning sql and I'm trying to do the following to the pubs datab
ase:
> For each order that include the books with title_id PC1035 or BU1032, list
> the order number, order date, along with title_id, title, and a quantity o
f
> each book included in order. Put the list in order of order date (in
> descending order), then title in alphabetical order.
> I've never written a query like that (I've done basic queries), but if
> anyone can help it'd be appreciated.
> Thank you,
> Kristen.

Newbie *easy/stupid?* question

Hi everyone,
I'm just learning sql and I'm trying to do the following to the pubs database:
For each order that include the books with title_id PC1035 or BU1032, list
the order number, order date, along with title_id, title, and a quantity of
each book included in order. Put the list in order of order date (in
descending order), then title in alphabetical order.
I've never written a query like that (I've done basic queries), but if
anyone can help it'd be appreciated.
Thank you,
Kristen.Kristen
The following query should help you out:
SELECT s.ord_num, s.ord_date, s.title_id, t.title, s.qty
FROM pubs.dbo.sales s JOIN pubs.dbo.titles t
ON s.title_id = t.title_id
WHERE s.title_id = 'PC1035'
OR s.title_id = 'BU1032'
ORDER BY s.ord_date DESC, t.title ASC
- Peter Ward
WARDY IT Solutions
"Kristen" wrote:
> Hi everyone,
> I'm just learning sql and I'm trying to do the following to the pubs database:
> For each order that include the books with title_id PC1035 or BU1032, list
> the order number, order date, along with title_id, title, and a quantity of
> each book included in order. Put the list in order of order date (in
> descending order), then title in alphabetical order.
> I've never written a query like that (I've done basic queries), but if
> anyone can help it'd be appreciated.
> Thank you,
> Kristen.

Newbie - Changing ordered list

Hi,

I would like to programmatically move rows up or down an ordered list. I
have created a column to ORDER BY and filled with ascending integer values.
Swapping the column values with the row above or the row below changes the
row position OK but things start getting complicated when rows are deleted
or new rows are added (e.g. what column value to assign to the new row). Is
there a simple way of doing this?

ThanksJackT (turnbull.jack@.ntlworld.com) writes:
> I would like to programmatically move rows up or down an ordered list. I
> have created a column to ORDER BY and filled with ascending integer
> values. Swapping the column values with the row above or the row below
> changes the row position OK but things start getting complicated when
> rows are deleted or new rows are added (e.g. what column value to assign
> to the new row). Is there a simple way of doing this?

I am sorry, but you need to explain a lot more of what you are doing.
Are you moving rows in a table, or are they rows in a screen form?

Generally, for many types of question in this newsgroup it is a good
idea to include:

o CREATE TABLE statements for the involved tables.
o INSERT statements with sample data.
o The desired result with the sample data.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland,

CREATE TABLE [CategoryList] (
[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryIndex] [int] NULL ,
[CategoryName] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
INSERT INTO [CategoryList] (CategoryName, CategoryIndex)
VALUES ('First',0)
INSERT INTO [CategoryList] (CategoryName, CategoryIndex)
VALUES ('Second',1)
INSERT INTO [CategoryList] (CategoryName, CategoryIndex)
VALUES ('Third',2)
INSERT INTO [CategoryList] (CategoryName, CategoryIndex)
VALUES ('Fourth',3)
GO
SELECT * FROM [CategoryList] ORDER BY CategoryIndex
GO

Gives Output

1 0 First
2 1 Second
3 2 Third
4 3 Fourth

UPDATE [CategoryList]
SET [CategoryIndex]=2 WHERE [CategoryID] = 2
UPDATE [CategoryList]
SET [CategoryIndex]=1 WHERE [CategoryID] = 3
SELECT * FROM dbo.CategoryList ORDER BY CategoryIndex
GO

Gives Output

1 0 First
3 1 Third
2 2 Second
4 4 Fourth

Is this the best method of setting up a list so you can swap the ordering
programmatically?

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns953BACDF25089Yazorman@.127.0.0.1...
> JackT (turnbull.jack@.ntlworld.com) writes:
> > I would like to programmatically move rows up or down an ordered list. I
> > have created a column to ORDER BY and filled with ascending integer
> > values. Swapping the column values with the row above or the row below
> > changes the row position OK but things start getting complicated when
> > rows are deleted or new rows are added (e.g. what column value to assign
> > to the new row). Is there a simple way of doing this?
> I am sorry, but you need to explain a lot more of what you are doing.
> Are you moving rows in a table, or are they rows in a screen form?
> Generally, for many types of question in this newsgroup it is a good
> idea to include:
> o CREATE TABLE statements for the involved tables.
> o INSERT statements with sample data.
> o The desired result with the sample data.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||JackT (turnbull.jack@.ntlworld.com) writes:
> UPDATE [CategoryList]
> SET [CategoryIndex]=2 WHERE [CategoryID] = 2
> UPDATE [CategoryList]
> SET [CategoryIndex]=1 WHERE [CategoryID] = 3
> SELECT * FROM dbo.CategoryList ORDER BY CategoryIndex
> GO
> Gives Output
> 1 0 First
> 3 1 Third
> 2 2 Second
> 4 4 Fourth
> Is this the best method of setting up a list so you can swap the ordering
> programmatically?

OK, so what you basically after is a sorter value who tells you in
which order to present the data?

I can't think of any radically different way of doing this, although
some varitions are possible, for instance using 100, 200 etc as values
initially. On the other hand, having a contiguous series, can actually
make it easier to maintain the list.

Insert a value at point n:

BEGIN TRANSACTION

UPDATE CategoryList SET CategoryIndex = CategoryIndex + 1
WHERE CategoryIndex >= @.Indexfornew

INSERT CategoryList(CategoryName, CategoryIndex)
VALUES (@.newname, @.Indexfornew)

COMMIT TRANSACTION

Delete an entry:

BEGIN TRANSACTION

SELECT @.indexforold = CategoryIndex FROM CategoryList
WHERE CategoryID = @.idtodelete

DELETE CategoryIndex FROM CategoryList WHERE CategoryID = @.idtodelete

UPDATE CategoryList SET CategoryIndex = CategoryIndex - 1
WHERE CategoryIndex > @.indexforold

COMMIT TRANSACTION

Move an entry to position @.n:

BEGIN TRANSACTION

SELECT @.currentindex = CategoryIndex FROM CategoryList
WHERE CategoryID = @.idtomove

UPDATE CategoryIndex SET CategoryIndex = 10000000
WHERE CategoryID = @.idtomove

IF @.n > @.currentindex
BEGIN
UPDATE CategoryList SET CategoryIndex = CategoryIndex - 1
WHERE CategoryIndex > @.currentindex AND CategoryIndex <= @.n
END
ELSE
BEGIN
UPDATE CategoryList SET CategoryIndex = CategoryIndex + 1
WHERE CategoryIndex < @.currentindex AND CategoryIndex >= @.n
END

UPDATE CategoryIndex SET CategoryIndex = 10000000
WHERE CategoryID = @.n

COMMIT TRANSACTION

Here I have assumed that you don't to have ties, and thus a UNIQUE
constraint on the index column is a good idea.

All the above is untested - you should have some fun too! :-)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland,

This looks pretty much what I'm after. I'll try it out ASAP and tune as
required. Grateful thanks.

Jack

Newbe Question - How to install SQL 2000 Cluster

Good day,
I'm looking for a document that shows the steps needed in order to install
SQL 2000 on a windows 2003 cluster.
Thanks in advance
Oren Zippori
Great webcast on the subject:
http://support.microsoft.com/default...lurb061002.asp
http://www.sql-server-performance.co...stall_main.asp
has great information for Windows 2000, just add Q817064 and then 301600 for
MSDTC support and you are good to go.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://www.msmvps.com/clustering - Blog
"Oren Zippori" <orenzp@.hotmail.com> wrote in message
news:ezlb3woxEHA.3416@.TK2MSFTNGP09.phx.gbl...
> Good day,
> I'm looking for a document that shows the steps needed in order to install
> SQL 2000 on a windows 2003 cluster.
> Thanks in advance
> Oren Zippori
>
|||Thanks for the Info Rodney.
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:%238D7pMpxEHA.2540@.TK2MSFTNGP15.phx.gbl...
> Great webcast on the subject:
> http://support.microsoft.com/default...lurb061002.asp
> http://www.sql-server-performance.co...stall_main.asp
> has great information for Windows 2000, just add Q817064 and then 301600
> for MSDTC support and you are good to go.
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering
> http://www.msmvps.com/clustering - Blog
> "Oren Zippori" <orenzp@.hotmail.com> wrote in message
> news:ezlb3woxEHA.3416@.TK2MSFTNGP09.phx.gbl...
>