A to Z table data

Hello

I am currently using ms sql 2000 and I want to display my table, column of names in alphabetical order. How can I achieve this?

Thanks

Laura

Did you mean to dispaly all column names of a table in alphabetical order? In T-SQL we use 'ORDER BY' clause in query to perform ordering. So let's use such a statement to achieve your request:

select * from syscolumns where id=object_id('myTable') order by name

|||

Hi

Thanks for the reply. Thanks also for the solution.

I was actually thinking about when I had previously created a database using Access. It had a nice easy to use feature that could be used on a column whilst building the database. When you click on the column within the database you can choose to display in assending or decending order. I was looking for such a feature in SQL but so far I have not found it. Does this feature exist? If so where is it?

Thanks

Laura

|||

Sure there is. In SQL we use 'ORDER BY' clause to sort result. You can also sort result returned in Enterprise Manager: Just rigth click a table->choose 'Open Table'->'Query', then in the 'Diagram Pane' add some columns, and you can choose 'Sort Type'&'Sort Order' for each column. Then click 'Run' to execute the query. You can press F1 in 'Diagram Pane' to get more help from SQL2000 Books Online.

a table/column to table/column data check (was "Help Please, SQL something Simpl

Hi all, I am not over familiar with SQL, I am a VB programmer, simply I need to achieve the following within Enterprise Manager.

I have 2 tables, different designs, different number of rows, I simply need to check whether the contents of a column in the first table is in a column in the second table, just simply a table/column to table/column data check for the same data content.

Easy Peasy for you guys, any help would be appreciated.Hi all, I am not over familiar with SQL, I am a VB programmer, simply I need to achieve the following within Enterprise Manager.

I have 2 tables, different designs, different number of rows, I simply need to check whether the contents of a column in the first table is in a column in the second table, just simply a table/column to table/column data check for the same data content.

Easy Peasy for you guys, any help would be appreciated.

Welcome to the forum,ok here is your solution

CREATE TABLE t1(
col1 VARCHAR(20)
,col2 VARCHAR(20)
,col3 VARCHAR(20)
)

CREATE TABLE t2(
col4 VARCHAR(20)
,col5 VARCHAR(20)
)

The sql statement to check content of a col1 is in col4 or not...

SELECT * FROM
t1,t2
WHERE t1.col1=t2.col4|||Many thanks, will give it a try a wee bit later and post result.

Many thanks...|||Hi, this appears to work fine, however if I change the logic to check what is not in table2 it appears to go into a endless loop, any help appreciated.|||Hi, sorry after further testing, I may not have explained myself very well, apologies, I need to access row1 of table1 and then scan column2 in table2, the entire table, for the occurence of the data item in row1 table1.

Many thanks in anticipation.|||Hi, this appears to work fine, however if I change the logic to check what is not in table2 it appears to go into a endless loop, any help appreciated.
Test it ...

insert into t1 values('joy','datta','harry')
insert into t1 values('Pink','datta56','harry2')
insert into t1 values('Sheep','datta5','harry3')
insert into t1 values('Ghree','datta11','harry5')

insert into t2 values('Sheep','datta5')
insert into t2 values('Ghree','datta11')

SELECT * FROM
t1
WHERE t1.col1 NOT IN(SELECT t2.col5 FROM t2)|||Thanks sorted..

A question on database design

Hi everyone,
I'm hoping someone could give me some advice on how to achieve the following.
I'm making a system that stores information on holiday homes. In particular
I need to store information on their bookings. When is a home occupied and
for how long.
I've considered making a Bookings table which would be somthing like:
ID
HomeID
StartDate
EndDate
What I can't see yet is how I'm going to query this table to find all properties
that are available for a given period.
What I want to be able to say is show me all homes that are available between
the 12th of april and the 27th of April.
Can anyone suggest how that query would work? Or is there perhaps a better
way to store this booking information?
Any advice and suggestions would be gratefully received.
Kindest Regards
Simon
> I've considered making a Bookings table which would be somthing like:
> ID
How about BookingID? ID is meaningless and can easily be taken out of
context.

> What I can't see yet is how I'm going to query this table to find all
> properties that are available for a given period.

> What I want to be able to say is show me all homes that are available
> between the 12th of april and the 27th of April.
-- untested, due to lack of DDL, sample data, desired results;
DECLARE @.startDate SMALLDATETIME, @.endDate SMALLDATETIME;
SELECT @.startDate = '20060412', @.endDate = '20060427';
SELECT HomeID, Description
FROM dbo.Homes h
WHERE NOT EXISTS
(
SELECT 1
FROM dbo.Bookings b
WHERE b.HomeID = h.HomeID
AND
(
b.StartDate BETWEEN @.startDate AND @.endDate
OR
b.EndDate BETWEEN @.startDate AND @.endDate
)
);
|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Don't you need a few more things in Bookings?
CREATE TABLE Bookings
(customer_id INTEGER NOT NULL
REFERENCES Customers(customer_id),
property_id INTEGER NOT NULL
REFERENCES Properties (property_id),
start_date DATETIME NOT NULL,
end_date DATETIME,
CHECK (start_date < end_date),
rental_rate DECIMAL (8,2) NOT NULL
CHECK (rental_rate > 0.00),
PRIMARY KEY (customer_id, property_id, start_date));
[vbcol=seagreen]
the 12th of April and the 27th of April. <<
I would build a Calendar table which is discussed in a lot of old
postings.
SELECT @.my_arrival, @.my_depart, P.property_id
FROM Properties AS P
WHERE NOT EXISTS
(SELECT *
FROM Bookings AS B,
Calendar AS C
WHERE C.cal_date
BETWEEN B.start_date AND B.end_date
AND B.property_id = P.property_id
AND cal_date BETWEEN @.my_arrival AND @.my_depart);
Untested.
(SELECT cal_date
FROM Calendar
WHERE cal_date BETWEEN @.my_arrival AND @.my_depart) AS X(cal_date)
|||You should consider recording dates for which a given unit is actually
available. One way is to create "fake" bookings for non-availability
dates - but that can get ugly.
|||On Tue, 17 Jan 2006 07:28:49 -0800, Simon Harvey wrote:
(snip)
>Can anyone suggest how that query would work? Or is there perhaps a better
>way to store this booking information?
Hi Simon,
Depending on your indexes, the query below might run faster than the
query Aaron suggests:
DECLARE @.startDate SMALLDATETIME, @.endDate SMALLDATETIME;
SELECT @.startDate = '20060412', @.endDate = '20060427';
SELECT HomeID, Description
FROM dbo.Homes h
WHERE NOT EXISTS
(
SELECT 1
FROM dbo.Bookings b
WHERE b.HomeID = h.HomeID
AND b.StartDate <= @.endDate
AND b.EndDate >= @.startDate
);
NOTE: I've set the follow-up to just the SQL Server groups. I can't help
with Access syntax anyway.
Hugo Kornelis, SQL Server MVP
|||Hi everyone
[vbcol=seagreen]
context.
Actually, the primary key in my tables is always ID, that way you never have
to think about it. I used to do it the other way, but then I got to thinking,
why call it BookingID? It's obviously a BookingID by virtue of the fact that
its the ID of the Booking table. I always fully qualify column names anyway
so I use Bookings.ID. I've heard some people say that doing this makes things
faster but I don't really care. I just do it for clarity and to avoid conflicts.
Of course if I was dealing with a foregin key in a table then I'd call it
BookingID because that would be implicitly helpful and quite necessary.
[vbcol=seagreen]
Declarative Referential Integrity, data types, etc. in your schema are. Sample
data is also a good idea, along with clear specifications. It is very hard
to debug code when you do not let us see it."
Well that would be difficult considering I don't have any DDL to offer. I
was asking a general queston on how to achieve an end goal. I suggested a
bookings table which was remarkably simple and existed only in my head. I
thought it was simple enough to be clear to those who saw the possible columns.
Thank you to everyone who has given some suggestions. I think I was pretty
much getting to the same conclusion as Aarons suggestion, but thats solidified
it in my mind.
Thanks again
Simon

A question on database design

Hi everyone,
I'm hoping someone could give me some advice on how to achieve the following
.
I'm making a system that stores information on holiday homes. In particular
I need to store information on their bookings. When is a home occupied and
for how long.
I've considered making a Bookings table which would be somthing like:
ID
HomeID
StartDate
EndDate
What I can't see yet is how I'm going to query this table to find all proper
ties
that are available for a given period.
What I want to be able to say is show me all homes that are available betwee
n
the 12th of april and the 27th of April.
Can anyone suggest how that query would work? Or is there perhaps a better
way to store this booking information?
Any advice and suggestions would be gratefully received.
Kindest Regards
Simon> I've considered making a Bookings table which would be somthing like:
> ID
How about BookingID? ID is meaningless and can easily be taken out of
context.

> What I can't see yet is how I'm going to query this table to find all
> properties that are available for a given period.

> What I want to be able to say is show me all homes that are available
> between the 12th of april and the 27th of April.
-- untested, due to lack of DDL, sample data, desired results;
DECLARE @.startDate SMALLDATETIME, @.endDate SMALLDATETIME;
SELECT @.startDate = '20060412', @.endDate = '20060427';
SELECT HomeID, Description
FROM dbo.Homes h
WHERE NOT EXISTS
(
SELECT 1
FROM dbo.Bookings b
WHERE b.HomeID = h.HomeID
AND
(
b.StartDate BETWEEN @.startDate AND @.endDate
OR
b.EndDate BETWEEN @.startDate AND @.endDate
)
);|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Don't you need a few more things in Bookings?
CREATE TABLE Bookings
(customer_id INTEGER NOT NULL
REFERENCES Customers(customer_id),
property_id INTEGER NOT NULL
REFERENCES Properties (property_id),
start_date DATETIME NOT NULL,
end_date DATETIME,
CHECK (start_date < end_date),
rental_rate DECIMAL (8,2) NOT NULL
CHECK (rental_rate > 0.00),
PRIMARY KEY (customer_id, property_id, start_date));
the 12th of April and the 27th of April. <<
I would build a Calendar table which is discussed in a lot of old
postings.
SELECT @.my_arrival, @.my_depart, P.property_id
FROM Properties AS P
WHERE NOT EXISTS
(SELECT *
FROM Bookings AS B,
Calendar AS C
WHERE C.cal_date
BETWEEN B.start_date AND B.end_date
AND B.property_id = P.property_id
AND cal_date BETWEEN @.my_arrival AND @.my_depart);
Untested.
(SELECT cal_date
FROM Calendar
WHERE cal_date BETWEEN @.my_arrival AND @.my_depart) AS X(cal_date)|||You should consider recording dates for which a given unit is actually
available. One way is to create "fake" bookings for non-availability
dates - but that can get ugly.|||On Tue, 17 Jan 2006 07:28:49 -0800, Simon Harvey wrote:
(snip)
>Can anyone suggest how that query would work? Or is there perhaps a better
>way to store this booking information?
Hi Simon,
Depending on your indexes, the query below might run faster than the
query Aaron suggests:
DECLARE @.startDate SMALLDATETIME, @.endDate SMALLDATETIME;
SELECT @.startDate = '20060412', @.endDate = '20060427';
SELECT HomeID, Description
FROM dbo.Homes h
WHERE NOT EXISTS
(
SELECT 1
FROM dbo.Bookings b
WHERE b.HomeID = h.HomeID
AND b.StartDate <= @.endDate
AND b.EndDate >= @.startDate
);
NOTE: I've set the follow-up to just the SQL Server groups. I can't help
with Access syntax anyway.
Hugo Kornelis, SQL Server MVP|||Hi everyone
context.
Actually, the primary key in my tables is always ID, that way you never have
to think about it. I used to do it the other way, but then I got to thinking
,
why call it BookingID? It's obviously a BookingID by virtue of the fact that
its the ID of the Booking table. I always fully qualify column names anyway
so I use Bookings.ID. I've heard some people say that doing this makes thing
s
faster but I don't really care. I just do it for clarity and to avoid confli
cts.
Of course if I was dealing with a foregin key in a table then I'd call it
BookingID because that would be implicitly helpful and quite necessary.
Declarative Referential Integrity, data types, etc. in your schema are. Samp
le
data is also a good idea, along with clear specifications. It is very hard
to debug code when you do not let us see it."
Well that would be difficult considering I don't have any DDL to offer. I
was asking a general queston on how to achieve an end goal. I suggested a
bookings table which was remarkably simple and existed only in my head. I
thought it was simple enough to be clear to those who saw the possible colum
ns.
Thank you to everyone who has given some suggestions. I think I was pretty
much getting to the same conclusion as Aarons suggestion, but thats solidifi
ed
it in my mind.
Thanks again
Simon

A question on database design

Hi everyone,
I'm hoping someone could give me some advice on how to achieve the following
.
I'm making a system that stores information on holiday homes. In particular
I need to store information on their bookings. When is a home occupied and
for how long.
I've considered making a Bookings table which would be somthing like:
ID
HomeID
StartDate
EndDate
What I can't see yet is how I'm going to query this table to find all proper
ties
that are available for a given period.
What I want to be able to say is show me all homes that are available betwee
n
the 12th of april and the 27th of April.
Can anyone suggest how that query would work? Or is there perhaps a better
way to store this booking information?
Any advice and suggestions would be gratefully received.
Kindest Regards
Simon> I've considered making a Bookings table which would be somthing like:
> ID
How about BookingID? ID is meaningless and can easily be taken out of
context.

> What I can't see yet is how I'm going to query this table to find all
> properties that are available for a given period.

> What I want to be able to say is show me all homes that are available
> between the 12th of april and the 27th of April.
-- untested, due to lack of DDL, sample data, desired results;
DECLARE @.startDate SMALLDATETIME, @.endDate SMALLDATETIME;
SELECT @.startDate = '20060412', @.endDate = '20060427';
SELECT HomeID, Description
FROM dbo.Homes h
WHERE NOT EXISTS
(
SELECT 1
FROM dbo.Bookings b
WHERE b.HomeID = h.HomeID
AND
(
b.StartDate BETWEEN @.startDate AND @.endDate
OR
b.EndDate BETWEEN @.startDate AND @.endDate
)
);|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Don't you need a few more things in Bookings?
CREATE TABLE Bookings
(customer_id INTEGER NOT NULL
REFERENCES Customers(customer_id),
property_id INTEGER NOT NULL
REFERENCES Properties (property_id),
start_date DATETIME NOT NULL,
end_date DATETIME,
CHECK (start_date < end_date),
rental_rate DECIMAL (8,2) NOT NULL
CHECK (rental_rate > 0.00),
PRIMARY KEY (customer_id, property_id, start_date));
[vbcol=seagreen]
the 12th of April and the 27th of April. <<
I would build a Calendar table which is discussed in a lot of old
postings.
SELECT @.my_arrival, @.my_depart, P.property_id
FROM Properties AS P
WHERE NOT EXISTS
(SELECT *
FROM Bookings AS B,
Calendar AS C
WHERE C.cal_date
BETWEEN B.start_date AND B.end_date
AND B.property_id = P.property_id
AND cal_date BETWEEN @.my_arrival AND @.my_depart);
Untested.
(SELECT cal_date
FROM Calendar
WHERE cal_date BETWEEN @.my_arrival AND @.my_depart) AS X(cal_date)|||You should consider recording dates for which a given unit is actually
available. One way is to create "fake" bookings for non-availability
dates - but that can get ugly.|||On Tue, 17 Jan 2006 07:28:49 -0800, Simon Harvey wrote:
(snip)
>Can anyone suggest how that query would work? Or is there perhaps a better
>way to store this booking information?
Hi Simon,
Depending on your indexes, the query below might run faster than the
query Aaron suggests:
DECLARE @.startDate SMALLDATETIME, @.endDate SMALLDATETIME;
SELECT @.startDate = '20060412', @.endDate = '20060427';
SELECT HomeID, Description
FROM dbo.Homes h
WHERE NOT EXISTS
(
SELECT 1
FROM dbo.Bookings b
WHERE b.HomeID = h.HomeID
AND b.StartDate <= @.endDate
AND b.EndDate >= @.startDate
);
NOTE: I've set the follow-up to just the SQL Server groups. I can't help
with Access syntax anyway.
Hugo Kornelis, SQL Server MVP|||Hi everyone
[vbcol=seagreen]
context.
Actually, the primary key in my tables is always ID, that way you never have
to think about it. I used to do it the other way, but then I got to thinking
,
why call it BookingID? It's obviously a BookingID by virtue of the fact that
its the ID of the Booking table. I always fully qualify column names anyway
so I use Bookings.ID. I've heard some people say that doing this makes thing
s
faster but I don't really care. I just do it for clarity and to avoid confli
cts.
Of course if I was dealing with a foregin key in a table then I'd call it
BookingID because that would be implicitly helpful and quite necessary.
[vbcol=seagreen]
Declarative Referential Integrity, data types, etc. in your schema are. Samp
le
data is also a good idea, along with clear specifications. It is very hard
to debug code when you do not let us see it."
Well that would be difficult considering I don't have any DDL to offer. I
was asking a general queston on how to achieve an end goal. I suggested a
bookings table which was remarkably simple and existed only in my head. I
thought it was simple enough to be clear to those who saw the possible colum
ns.
Thank you to everyone who has given some suggestions. I think I was pretty
much getting to the same conclusion as Aarons suggestion, but thats solidifi
ed
it in my mind.
Thanks again
Simon

A question on database design

Hi everyone,
I'm hoping someone could give me some advice on how to achieve the following.
I'm making a system that stores information on holiday homes. In particular
I need to store information on their bookings. When is a home occupied and
for how long.
I've considered making a Bookings table which would be somthing like:
ID
HomeID
StartDate
EndDate
What I can't see yet is how I'm going to query this table to find all properties
that are available for a given period.
What I want to be able to say is show me all homes that are available between
the 12th of april and the 27th of April.
Can anyone suggest how that query would work? Or is there perhaps a better
way to store this booking information?
Any advice and suggestions would be gratefully received.
Kindest Regards
Simon> I've considered making a Bookings table which would be somthing like:
> ID
How about BookingID? ID is meaningless and can easily be taken out of
context.
> What I can't see yet is how I'm going to query this table to find all
> properties that are available for a given period.
> What I want to be able to say is show me all homes that are available
> between the 12th of april and the 27th of April.
-- untested, due to lack of DDL, sample data, desired results;
DECLARE @.startDate SMALLDATETIME, @.endDate SMALLDATETIME;
SELECT @.startDate = '20060412', @.endDate = '20060427';
SELECT HomeID, Description
FROM dbo.Homes h
WHERE NOT EXISTS
(
SELECT 1
FROM dbo.Bookings b
WHERE b.HomeID = h.HomeID
AND
(
b.StartDate BETWEEN @.startDate AND @.endDate
OR
b.EndDate BETWEEN @.startDate AND @.endDate
)
);|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Don't you need a few more things in Bookings?
CREATE TABLE Bookings
(customer_id INTEGER NOT NULL
REFERENCES Customers(customer_id),
property_id INTEGER NOT NULL
REFERENCES Properties (property_id),
start_date DATETIME NOT NULL,
end_date DATETIME,
CHECK (start_date < end_date),
rental_rate DECIMAL (8,2) NOT NULL
CHECK (rental_rate > 0.00),
PRIMARY KEY (customer_id, property_id, start_date));
>> What I want to be able to say is show me all homes that are available between
the 12th of April and the 27th of April. <<
I would build a Calendar table which is discussed in a lot of old
postings.
SELECT @.my_arrival, @.my_depart, P.property_id
FROM Properties AS P
WHERE NOT EXISTS
(SELECT *
FROM Bookings AS B,
Calendar AS C
WHERE C.cal_date
BETWEEN B.start_date AND B.end_date
AND B.property_id = P.property_id
AND cal_date BETWEEN @.my_arrival AND @.my_depart);
Untested.
(SELECT cal_date
FROM Calendar
WHERE cal_date BETWEEN @.my_arrival AND @.my_depart) AS X(cal_date)|||You should consider recording dates for which a given unit is actually
available. One way is to create "fake" bookings for non-availability
dates - but that can get ugly.|||On Tue, 17 Jan 2006 07:28:49 -0800, Simon Harvey wrote:
(snip)
>Can anyone suggest how that query would work? Or is there perhaps a better
>way to store this booking information?
Hi Simon,
Depending on your indexes, the query below might run faster than the
query Aaron suggests:
DECLARE @.startDate SMALLDATETIME, @.endDate SMALLDATETIME;
SELECT @.startDate = '20060412', @.endDate = '20060427';
SELECT HomeID, Description
FROM dbo.Homes h
WHERE NOT EXISTS
(
SELECT 1
FROM dbo.Bookings b
WHERE b.HomeID = h.HomeID
AND b.StartDate <= @.endDate
AND b.EndDate >= @.startDate
);
NOTE: I've set the follow-up to just the SQL Server groups. I can't help
with Access syntax anyway.
--
Hugo Kornelis, SQL Server MVP