Hide empty tables

Hi,

I've just creates a report with about 20 Tables. To my suprise, it almost runs as intended ;-)
But there is one flaw - Is there any way of hiding an empyt table?
I want to hide all tables that are not filled with data. But wich one is empty differs from the entered parameter.

So long
Sven
(Germany)YOu could set the NoRows property to an empty string, this would hide the tables and the header.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||Well, I think i figured it out myself. But nonetheless. Thanks

##############################

Hey,
thanks for the fast answer.
But I'm quiet a noob regarding ReportingServices. How do I have to use "NoRows" ?
Is that a VB Code and if so, how do i use it ?

Mfg
Sven

Hide aspnet_* objects

Hello, I would like to not have to see the aspnet_ tables and stored procedures that are created when using the Membership, roles, and personalization. Currently I have to suffer seeing the handful of tables and 40+ stored procedures in both the Visual Studio and the SQL Management tool. I have found that on a 2000 SQL Server I can execute a command that forces objects to be created as system objects. If I execute this before creating the objects they become system objects and I don't have to see them any longer.

However, this trick doesnot work in SQL Server 2005. So, I would like to know either 1) is there an easy way to hide these objects or 2) is there a way to change the objects to system objects in SQL Server 2005?

CodeGuy

Right click on the tables folder in Management Studio and choose Filter -> Filter settings.

Now create a filter using name NOT CONTAINING aspnet_

Hope it helps

|||

Klaus, thank you for mentioning that. I actually found reference to that as well. Two downsides to that approach, first you have to set the filter upEVERY TIME, because it's not saved. Second, that only helps me a small amount because it only works in the Management Studio.

CodeGuy

|||

Another option would be to use a separate database for the aspnet_* tables... but that has drawbacks aswell. Hope you find a good solution and keep us posted.

|||

Klaus, thank you for the ideas. You suggestion definitely would work, however, I'd prefer to have these objects in the same database for portability reasons.

Anyone else have any good ideas?

Hep needed!

Hi plz read below is my scenario
I have two diff tables which stores passwords (don't ask why b'coz it was there).Now to make data in both table consistance what I need to do?plz provide optimized solution.
Thanks in advance.
bye bye.
happy coding!!!
I have two diff tables which stores passwords (don't ask why b'coz it was there).Now to make data in both table consistance what I need to do?plz provide optimized solution.
Since there does not appear to be much detail in your statement, I am assuming that you are asking how you can keep passwords in both tables synchronised.
I would recommend that you create one stored procedure to update passwords and revoke explicit update statements to both tables. Then grant update to the stored procedure which will force everyone update to go thru that. Then you can update both tables in that sp.
hth

help--backup tables using BCP

I need to backup data (which satisfies a condition) from tables to flat files.....it worked fine using bcp...now i need to delete the records from tables which have been backed up......is ther any way out?

i wrote that bcp commands in a batch file to automate it.....

Using SSIS, you can write a package to export the table to a text file using a dataflow, and use an Execute SQL task to delete the rows afterward.

If you don't want to use SSIS, you might want to post to the T-SQL or Tools forum.

help--backup tables using BCP

I need to backup data (which satisfies a condition) from tables to flat files.....it worked fine using bcp...now i need to delete the records from tables which have been backed up......is ther any way out?

i wrote that bcp commands in a batch file to automate it.....

Using SSIS, you can write a package to export the table to a text file using a dataflow, and use an Execute SQL task to delete the rows afterward.

If you don't want to use SSIS, you might want to post to the T-SQL or Tools forum.

Help? Fragmentation still high after drop and re-create indexes...

Hello, i am encountering a strange behaviour on SQL 2005 with dropping and re-creating my indexes not affecting my fragmentation.

I have many tables with the same structure, and on each table i have 3 indexes defined, one clustered and 2 other indexes. After some time, they get pretty fragmented. When i drop the indexes, and re-create across all my tables, some of my tables fragmentation % is still very high, over 60%. Yet some other tables report fragmentations of 0 across my 3 indexes.

I use this query to find out fragmentation:

SELECT a.index_id, name, avg_fragmentation_in_percent, index_type_desc
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('NS_Time_Daily'),
NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id

I also checked my physical disk fragmentation and it has no issue, i.e. it does not recommend a defrag (im on win server 2003)

Why would this be happening?

Any thoughts will be greatly appreciated!! Thanks, Jeff

CREATE TABLE [dbo].[NS_Time_Daily]
(
[MemberId] [bigint] NOT NULL,
[lft] [bigint] NULL,
[rgt] [bigint] NULL,
[modelsiteID] [bigint] NOT NULL,
[depth] [bigint] NOT NULL,
[lvl] [bigint] NOT NULL,
[lvlPosition] [bigint] NOT NULL
)

DROP INDEX [NS_Time_Daily$IDX_LP] ON [NS_Time_Daily]
DROP INDEX [NS_Time_Daily$IDX_LR] ON [NS_Time_Daily]
DROP INDEX [NS_Time_Daily$IDX_MM] ON [NS_Time_Daily]

CREATE CLUSTERED INDEX [NS_Time_Daily$IDX_MM] ON [NS_Time_Daily](ModelSiteId, MemberId)
CREATE UNIQUE INDEX [NS_Time_Daily$IDX_LP] ON [NS_Time_Daily](ModelSiteId, Lvl, LvlPosition)
CREATE UNIQUE INDEX [NS_Time_Daily$IDX_LR] ON [NS_Time_Daily](ModelSiteId, Lft, Rgt)

Hi Jeff,

How big is the table mate?

Could you send us the output of the following statement before and after you do the re-index?

selectobject_name(object_id)as TableName, index_id, index_type_desc,

avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages, page_count,

avg_page_space_used_in_percent

from sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('NS_Time_Daily'),

NULL,NULL,NULL)

regards

Jag Sandhu

Help:Select query

I have 3 tables in my database

The first Table is called Customers

The second Table is called Rooms

And the third table is a combination of the primary keys of Customer table and the Rooms Table.

The Room tables consist of 50 rooms and only 30 of these rooms are currently
occupied. I need a query to find the available room numbers that are not
currently in use.

The query below the retrieve the occupied room

SELECT Room.Room, Room.Telefon

FROM ThirdTableINNER JOIN

Room ON ThirdTableINNER .RoomID = Room.RoomID

Thanks in advanceSELECT Room.Room, Room.Telefon

FROM ThirdTable a RIGHT JOIN

Room b ON a .RoomID = b.RoomID

WHERE a.RoomId IS NULL

Are the rooms rented in units of 15 minutes?

:D|||Originally posted by Brett Kaiser
SELECT Room.Room, Room.Telefon

FROM ThirdTable a RIGHT JOIN

Room b ON a .RoomID = b.RoomID

WHERE a.RoomId IS NULL

Are the rooms rented in units of 15 minutes?

:D

Thank u Brett Kaiser for the help. The query was great. Actually all rooms are almost full, but because you did a great job, as the Manager of the Hotel I will offer you 3 nights stay FOC.|||And where might that be?|||Originally posted by Brett Kaiser
And where might that be?

We have branch offices world wide, all you need to do is to specify your location I will send a comfirmation to that branch.

Help: why SQL 2005 is slow?

Hi,
During the weekend, I heard that SQL Server 2000 is very slow with tables co
ntaining rows over millions. So I did some
tests with our new database on SQL Server 2005 Standard Edition. The machine
is Windows 2003 Standard Server box
with 3.5 GB RAM and single 3.0GHZ P4 CPU. There is no RAID configuration. It
has drives: C: with 11GB free space
and D: with 169GB free space. SQL 2005 was installed on D: drive.
The size of the database is 10GB containing 47 tables. The main table, Items
, contains 96 columns and 30 millions rows.
All columns are in varchar data type, and 2 of them are in varchar(2500). Th
ere is no any index setup in the table neither.
The time consumed for some SQL statements with this table are the followings
:
========================================
==============
No. SQL Statement Tim
e
========================================
= =========
1 SELECT * FROM Items WHERE item_num='10029' 16 minutes
----
-- --
2 SELECT COUNT(*) FROM Items 13 minute
s
----
-- --
3 ALTER TABLE Items
ADD rid INT PRIMARY KEY IDENTITY(1,1) 10 hours 50 minute
s
----
-- --
4 SELECT COUNT(*) FROM Items 20 minute
s
----
-- --
5 SELECT * FROM Items WHERE item_num='10029' 18 minutes
========================================
==============
The first 2 statements were run without primary key in the table. The statem
ents #4 and #5 were run after "rid" was added as primary key.
This is the first time I work with a database in such size. But the performa
nce of SQL Server 2005 surprised me.
Would you please tell me:
1. Is such performance normal with such number of rows?
2. Do I have to do something to improve the performance with such simple sta
tement when the number of rows>1 million or >10 millions?
3. Is SQL Server 2005 the right one to handle a database with such big table
, or should I consider DB2 9 or Oracle 10g?
Thank you
HongboHongbo wrote:
> Hi,
> During the weekend, I heard that SQL Server 2000 is very slow with
> tables containing rows over millions. So I did some
> tests with our new database on SQL Server 2005 Standard Edition. The
> machine is Windows 2003 Standard Server box
> with 3.5 GB RAM and single 3.0GHZ P4 CPU. There is no RAID
> configuration. It has drives: C: with 11GB free space
> and D: with 169GB free space. SQL 2005 was installed on D: drive.
> The size of the database is 10GB containing 47 tables. The main table,
> Items, contains 96 columns and 30 millions rows.
> All columns are in varchar data type, and 2 of them are in
> varchar(2500). There is no any index setup in the table neither.
> The time consumed for some SQL statements with this table are the
> followings:
> ========================================
==============
> No. SQL
> Statement Time
> ========================================
= =========
> 1 SELECT * FROM Items WHERE item_num='10029' 16 minutes
> ----
--
> --
> 2 SELECT COUNT(*) FROM Items 13
> minutes
> ----
--
> --
> 3 ALTER TABLE Items
> ADD rid INT PRIMARY KEY IDENTITY(1,1) 10
> hours 50 minutes
> ----
--
> --
> 4 SELECT COUNT(*) FROM Items 20
> minutes
> ----
--
> --
> 5 SELECT * FROM Items WHERE item_num='10029' 18 minutes
> ========================================
==============
> The first 2 statements were run without primary key in the table. The
> statements #4 and #5 were run after "rid" was added as primary key.
> This is the first time I work with a database in such size. But the
> performance of SQL Server 2005 surprised me.
> Would you please tell me:
> 1. Is such performance normal with such number of rows?
> 2. Do I have to do something to improve the performance with such
> simple statement when the number of rows>1 million or >10 millions?
> 3. Is SQL Server 2005 the right one to handle a database with such big
> table, or should I consider DB2 9 or Oracle 10g?
> Thank you
> Hongbo
Do I read this right, you have 30 million rows an no indexes? I would
think about adding a few to help you out. SQL 2005 can handle that with
proper design and hardware.
I believe there is a tremendous amount of paging going on there to
accommodate your full table scans and on a single drive, hence your poor
performance.
Ryan Sanders
http://ryanlsanders.blogspot.com

Help: UNION vs. CONTAINSTABLE

I have n Tables: T_1, T_2, ... T_n that all have the same exact fields/columns.

Ultimately, I want to search through ALL n tables and return a single table of relevant results from all tables, arranged in order by rank.

I'm not sure if this is possible. So far, I have:

1SELECT RANK, field_1, field_2, ..., field_mFROM2(3SELECT RANK, field_1, field_2, ..., field_mFROM T_1,4CONTAINSTABLE(T_1, field_i,@.searchText) searchTable5WHERE KEY = T_1.field_i6UNION7SELECT RANK, field_1, field_2, ..., field_mFROM T_2,8CONTAINSTABLE(T_2, field_i,@.searchText) searchTable9WHERE KEY = T_2.field_i10UNION11.12.13.14UNION15SELECT RANK, field_1, field_2, ... field_mFROM T_n,16CONTAINSTABLE(T_n, field_i,@.searchText) searchTable17WHERE KEY = T_n.field_i18)19ORDER BY RANKDESC

I haven't tried it yet, but it seems wrong. How do we actually do this?

You should first create a CTE (Common Table Expression) or a VIEW:http://www.singingeels.com/Articles/Understanding_SQL_Complex_Queries.aspx

You would do something like this:

WITH MyTables (ColA, ColB etc...) AS ( SELECT * FROM T_1 UNION SELECT * FROM T_2)

now.. you do your query with "MyTables" as the table name...

|||

I'm now getting an error that says:

Cannot use a CONTAINS or FREETEXT predicate on object 'MyTables' because it is not full-text indexed.

That's true, though. The table we created isn't full-text enabled.

----------

My other option is to do a query for each table I want searched, then merge them all into a DataSet, then reorder the DataSet by rank. How tedious, though, especially if I want pagination of large tables.

|||

I read somewhere that SQL Server 2005 does not support free text searching over multiple tables. There's something called SQLONE that actually allows FTS over multiple tables.

Help: Stored Procedure performance issue.

Hi,

I have a large SQL Server 2000 database with 3 core tables.

Table A : 10 million + records
Table B : 2 million + records
Table C : 6 million + records

One of the batch tasks that I have to perform firstly builds a list of
all keys for records from each of the three tables that I need to
process (along with a flag to tell me which table the key is from).
This list is populated into a table variable.

I then loop through the table variable to process all the records with
the particular key value.
The updates are run in order of the tables ... Table A first, B next
and finally C.

The table variable will typically hold 3000 keys.

My problem is this ..... the processing of the key records from Table
A runs well - it takes around 40 minutes which is acceptable for the
level of processing being carried out. Though when I start processing
the transactions for Table B the first couple of statements execute
successfully though then the subsequent statements take a long time (in
some cases hours) to complete. The format of the statements for all
tables is virtually the same and the tables have been indexed
appropriately.

The thing is that if I alter the stored proc to only process records
from Table B or Table C ... the procedure flies through and processes
the records in a flash ... 1-2 minutes.

Can anyone suggest what might be the issue here ?
I have read many posts though can't seem to find the solution.
Should I break up my processing so that it processes each table
individually ?
I've tried running the Profiler though it doesn't provide me with much
in the way of solutions.

Regards,
IanUnless you provide table definitions and the code it is unlikely that
anyone will be able to help very much.

Quote:

Originally Posted by

>I then loop through the table variable to process all the records with
>the particular key value.

It sounds as though this "process" is where things go bad, but all we
know about the process is that it loops. Looping sounds like cursors.
(Cursors are powerful and indispensable tools - but are often
misused.)

There are other possible hints in the description... building a table
of keys instead of (potentially) using a derived table or view, using
a table variable with 3000 values, the fact that the processing of the
different tables is similar but apparently different brings the
cohesion of the procedure into question... but without specifics it
isn't even worth guessing.

Roy Harvey
Beacon Falls, CT|||Hi Roy,

Thanks for your response ..... to supply all the tables and code is
tough due to the volume.

The looping that I'm using is via a table variable

I create the table variable :

declare @.t_ElligiblePromotions table
(ElligiblePromotionRowNum int IDENTITY (1, 1) Primary key NOT NULL ,
[prefix] int NULL,
[body] int NULL,
[entryrectype] varchar(3) NULL,
[DateMostRecentRevsion] smalldatetime NULL)

Insert the key records :

INSERT INTO @.t_ElligiblePromotions
([prefix],
[body],
[entryrectype],
[DateMostRecentRevsion])
select distinct
t_Orders.prefix,
t_Orders.body,
@.RunType_Order,
getdate()
from t_Orders

then loop through the variable

SELECT @.ElligiblePromotions_RowCnt = (select min
(ElligiblePromotionsRowNum) from
@.t_ElligiblePromotions)
SELECT @.MaxElligiblePromotions = (select max
(ElligiblePromotionsRowNum) from
@.t_ElligiblePromotions)

while @.ElligiblePromotions_RowCnt <= @.MaxElligiblePromotions
begin
select @.Prefix = Prefix,
@.Body = Body,
@.entryrectype = entryrectype,
@.DateMostRecentRevsion = DateMostRecentRevsion
from @.t_ElligiblePromotions
where ElligiblePromotionsRowNum = @.ElligiblePromotion_RowCnt

-- Match the record to the control table - then loop through each prize
draw to determine elligibility
--
BEGIN
EXEC @.ErrorNum = proc_QualificationDetail @.ControlID,
@.Prefix, @.Body,
@.EntryRecType, @.DateMostRecentRevsion, @.Identity,
'FM', @.Result
OUTPUT

SET @.ElligiblePromotions_RowCnt =
@.ElligiblePromotions_RowCnt
+ 1
END

I've been reading about parameter sniffing and might try some of the
suggestions that have been put forward for it as it is the statements
in the child procedure proc_QualificationDetail where the issues are
arising with the updates to the 2nd and 3rd table.

Regards,
Ian.

Roy Harvey wrote:

Quote:

Originally Posted by

Unless you provide table definitions and the code it is unlikely that
anyone will be able to help very much.
>

Quote:

Originally Posted by

I then loop through the table variable to process all the records with
the particular key value.

>
It sounds as though this "process" is where things go bad, but all we
know about the process is that it loops. Looping sounds like cursors.
(Cursors are powerful and indispensable tools - but are often
misused.)
>
There are other possible hints in the description... building a table
of keys instead of (potentially) using a derived table or view, using
a table variable with 3000 values, the fact that the processing of the
different tables is similar but apparently different brings the
cohesion of the procedure into question... but without specifics it
isn't even worth guessing.
>
Roy Harvey
Beacon Falls, CT

|||Hopefully you saw my response to your other message.

Roy Harvey
Beacon Falls, CT|||On 29.10.2006 04:45, Catch_22 wrote:

Quote:

Originally Posted by

Hi,
>
I have a large SQL Server 2000 database with 3 core tables.
>
Table A : 10 million + records
Table B : 2 million + records
Table C : 6 million + records
>
One of the batch tasks that I have to perform firstly builds a list of
all keys for records from each of the three tables that I need to
process (along with a flag to tell me which table the key is from).
This list is populated into a table variable.
>
I then loop through the table variable to process all the records with
the particular key value.
The updates are run in order of the tables ... Table A first, B next
and finally C.
>
The table variable will typically hold 3000 keys.
>
My problem is this ..... the processing of the key records from Table
A runs well - it takes around 40 minutes which is acceptable for the
level of processing being carried out. Though when I start processing
the transactions for Table B the first couple of statements execute
successfully though then the subsequent statements take a long time (in
some cases hours) to complete. The format of the statements for all
tables is virtually the same and the tables have been indexed
appropriately.
>
The thing is that if I alter the stored proc to only process records
from Table B or Table C ... the procedure flies through and processes
the records in a flash ... 1-2 minutes.
>
Can anyone suggest what might be the issue here ?
I have read many posts though can't seem to find the solution.
Should I break up my processing so that it processes each table
individually ?
I've tried running the Profiler though it doesn't provide me with much
in the way of solutions.

Maybe the DB engine just runs out of buffer cache. Since table A is the
large one it may be that once your process gets to table B it has too
many dirty buffers and needs to flush them to disk before it can
continue processing. IMHO you should be able to detect that situation
if you look at physical IO (either with profiler or perfmon).

Kind regards

robert|||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.

Next, you have described a sequential magentic file system and an
RDBMS. Rows are nothing like records. We do not use loops in SQL; it
is declarative language, not a procedural one.

Quote:

Originally Posted by

Quote:

Originally Posted by

>One of the batch tasks that I have to perform firstly builds a list of all keys for records [sic] from each of the three tables that I need to process (along with a flag [sic] to tell me which table the key is from). <<

Batch processing! We do not use flags in SQL. Flags are part of
process control, not data. We might have types. codes or other kinds
of attributes that are used declaratively. Next, a key is the key of
one and only one table; it can be referenced in several places. This
sounds like attribute splitting -- another characteristic of sequential
magentic file systems.

Quote:

Originally Posted by

Quote:

Originally Posted by

>This list is populated into a table variable. <<

Using proprietary features to mimic a scratch tape?

Quote:

Originally Posted by

Quote:

Originally Posted by

>I then loop through the table variable to process all the records[sic]with the particular key value. <<

Loops in a declarative language?

Quote:

Originally Posted by

Quote:

Originally Posted by

>The updates are run in order of the tables ... Table A first, B next and finally C.<<

I wish you had given the tables better names. If the tables have no
relationship among themselves, then you would simply use 3 UPDATE
statements. If the tables have no relationship among themselves,
either DRI actions or triggers would propagate related changes among
the tables. That is how a relational solution works.

Quote:

Originally Posted by

Quote:

Originally Posted by

> My problem is this ..... the processing of the key records [sic] from Table A runs well - it takes around 40 minutes which is acceptable for the level of processing being carried out. Though when I start processing the transactions for Table B the first couple of statements execute successfully though then the subsequent statements take a long time (in some cases hours) to complete. <<

Your looping has completely subverted any set-oriented processing which
SQL is designed to do.

Quote:

Originally Posted by

Quote:

Originally Posted by

>The format of the statements for all tables is virtually the same .. <<

This sounds like you have split a single entity across multiple tables.
Tables need to be very different becasue they each model a lpgically
different set of entities. For example, would you split Personnel into
a table of short people and a table of tall people? Of course not!
You would have a height attribute (column) in a single Personnel table.

Quote:

Originally Posted by

Quote:

Originally Posted by

>Can anyone suggest what might be the issue here? <<

Bad DDL, and non-relational programming.

Quote:

Originally Posted by

Quote:

Originally Posted by

>Should I break up my processing so that it processes each table individually ? <<

Ask yourself, if this is one LOGICAL operation. The quick test is can
it be given a name in the form <verb><object -- like
"UpdateInventory" or "InsertNewEmployee". The same rules you learned
about coupling and cohesion in your Software Engineering courses still
apply in SQL.

Please do not say that you cannot rewrite this stuff. Your DB will
only get bigger

I recently did a consulting job at a software company where they had
been trying to merge legs of a journey into longer trip -- say, you
have a journey from A to B, then from B to C, you want to show it as a
journey from A to C if certain temporal (consecutive legs of the trip
are no more than n days apart) and other constraints are met (the cargo
is of the same type for the same shipper, etc.). They were using
cursors and procedural code.

Thinking in sets, they reduced the existing procedure that was creating
a table to a single VIEW. The code was 90% smaller ~10 times faster
and the VIEW is *always* correct while the created table had to be
updated constantly. Years ago, I got a 2300% improvement and removed
~300 lines of code in an update procedure at an educational publisher
in California (I am good, but this was really because the original code
was that bad).

I know from a few decades of experience that it is worth it.|||1) The silly, redundant "" prefixes are in violation of both common
sense and ISO-11179 rules. Hey, we only have one data structure in
SQL!

2) Never put audit data in the table that is being audited. This is
the same principle as not putting the log file on the same drive as the
database. Revision dates never go into a table in the schema, but are
kept externally to it. Under SOX rules in the US, you can go to jail
or be fined.

3) Why did you fill your data dictionary with vague names that beg to
be defined? Prefix of what? Body of what?

4) Eligibility has only one "l", so your names are a bitch to read.
I really aware of that error; I have a tattoo that lets the world know
I am O-positive and have a Penicillin "Alergy" – Really ?!

5) I see you immediately jump to procedure calls with
"proc_qualificationdetail". Again the redundant prefix andpoor
name are in involution of basic software engineering. Tell me what the
procedures does, not what the parameters are. That means a
<verb><objectname, probably something like
"Determine_Eligibility()" instead.

Of course since this is a logical function, you probably should not use
a procedure or function at all. It ought to be in the WHERE clause of
the UPDATE statement.

Quote:

Originally Posted by

Quote:

Originally Posted by

>I create the table variable : <<

Because in the file system you are creating, all data must be
materialized to be persisted. In SQL, we have VIEWs for data that is
computed over and over as the schema changes. Also this is proprietary
and good programmers write to standards, not to dialect.

I gather that this routine is supposed to assign prizes to entries in a
promotion (much easier to understand that than just Tables A, B and C,
isn't it?). Without any more information, all anyone can do is guess
at the DDL, but here is my shot in a skeleton schema

CREATE TABLE Entries
(entry_id INTEGER NOT NULL PRIMARY KEY,
..);

CREATE TABLE Promotions
(promo_id INTEGER NOT NULL,
prize_id INTEGER NOT NULL,
PRIMARY KEY (promo_id, prize_id),
..);

CREATE VIEW Winners (entry_id, promo_id, prize_id)
SELECT entry_id, promo_id, prize_id
FROM Entries AS E, Promotions AS P
WHERE << assignment predicate >>

I am assuming that prizes are a part of promotion, not separate
entities that can exist alone. Having done something like this
before, the << assignment predicate >is a lot of hand-waving if the
rules are complex. But it can be done (cannot have won a prize in the
last 30 days, not an employee of Radio Station WXYZ, etc.) with careful
use of CASE expressions, you can do a decision table (Google "Logic
Gem") and be sure that you have a complete rules set.

Help: Selecting from two tables: odd situation

Hi, I have two tables setup as shown below:

Table name,"config"
+------+------+
| config_name | config_value |
+------+------+
| default_style | 1 |
| *other names | *other values |
+------+------+

Table name,"styles"
+------+------+
| style_id | style_name |
+------+------+
| 1 | Purple Hue |
| *other names | *other values |
+------+------+

Now, for my question:
I need to select everything (*) from the config table
and the 'style_name' from the styles table where style_id of the styles table is equal to the value of default_style of the config table.

I got as far as this query
"SELECT config.*, styles.style_name FROM `config`, `styles` WHERE config.default_style = styles.style_id LIMIT 1"

But obviously that will not work.

I know I can do this with two queries, but I am optimizing and I would like to reduce this down to 1 query, is this even possible to do with one query?

Thanks.left outer join|||Is this the same as a left join? I have heard of left join, but never of a left outer join.

Just an alias or what?|||yeah, it's the same

some databases allow the OUTER keyword to be optional

Help: Query

Hi,
I have 2 tables like below
CREATE TABLE Location
(
LocationID int NOT NULL,
LocationName char (30) NOT NULL UNIQUE,
CONSTRAINT PK_Location PRIMARY KEY (LocationID)
)
CREATE TABLE Parts
(
PartID int NOT NULL,
LocationID int NOT NULL,
PartName char (30) NOT NULL,
CONSTRAINT PK_Parts PRIMARY KEY (PartID),
CONSTRAINT FK_PartsLocation FOREIGN KEY (Location ID)
REFERENCES Location (LocationID)
)
I have created a stored procedure to delete all of partid on a certain
location as shown below.
CREATE PROCEDURE sp_DeleteLocation @.LocName char(30) AS
BEGIN
DECLARE @.PartID int
DECLARE crs_Parts CURSOR FOR
SELECT p.PartID
FROM Parts AS p INNER JOIN Location AS 1
ON p.LocationID = @.LocName
WHERE l.LocationName = @.LocName
OPEN crs_Parts
FETCH NEXT FROM crs_Parts INTO @.PartID
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
DELETE Parts WHERE CURRENT OF crs_Parts
FETCH NEXT FROM crs_Parts INTO @.PartID
END
CLOSE crs_Parts
DEALLOCATE crs_Parts
DELETE Location WHERE LocationName = @.LocName
END
My Question is how to replace the cursor operation in the stored
procedure by using single DELETE statement?
Thanks
Robert LieHi Robert
Jusr the query this way,
DELETE Parts
FROM Parts INNER JOIN Location
ON ON Parts.LocationID = Location.LocationID
WHERE Location.LocationName = @.LocName
hope this is the one that you are looking for:
thanks and regards
Chandra
"Robert Lie" wrote:
> Hi,
> I have 2 tables like below
> CREATE TABLE Location
> (
> LocationID int NOT NULL,
> LocationName char (30) NOT NULL UNIQUE,
> CONSTRAINT PK_Location PRIMARY KEY (LocationID)
> )
>
> CREATE TABLE Parts
> (
> PartID int NOT NULL,
> LocationID int NOT NULL,
> PartName char (30) NOT NULL,
> CONSTRAINT PK_Parts PRIMARY KEY (PartID),
> CONSTRAINT FK_PartsLocation FOREIGN KEY (Location ID)
> REFERENCES Location (LocationID)
> )
>
> I have created a stored procedure to delete all of partid on a certain
> location as shown below.
> CREATE PROCEDURE sp_DeleteLocation @.LocName char(30) AS
> BEGIN
> DECLARE @.PartID int
> DECLARE crs_Parts CURSOR FOR
> SELECT p.PartID
> FROM Parts AS p INNER JOIN Location AS 1
> ON p.LocationID = @.LocName
> WHERE l.LocationName = @.LocName
> OPEN crs_Parts
> FETCH NEXT FROM crs_Parts INTO @.PartID
> WHILE (@.@.FETCH_STATUS <> -1)
> BEGIN
> DELETE Parts WHERE CURRENT OF crs_Parts
> FETCH NEXT FROM crs_Parts INTO @.PartID
> END
> CLOSE crs_Parts
> DEALLOCATE crs_Parts
> DELETE Location WHERE LocationName = @.LocName
> END
> My Question is how to replace the cursor operation in the stored
> procedure by using single DELETE statement?
> Thanks
> Robert Lie
>

Help: Purge Table Stored Procedure

I have to create a stored procedure to purge "x" # of records from a
table. I have two tables (script below):

Schedule
ScheduleHistory

I need to purge records out of ScheduleHistory. The problem is that
the # of records that needs to be "kept" is dynamic, and stored in the
Schedule table. So all records in ScheduleHistory should be purged,
except for the most recent "x" number of records.

For instance, for each Schedule row, you can specify how many records
to "keep" at all times. I want to keep the most recent "x" # of
ScheduleHistory rows for each Schedule.

In the script below, I have two schedules ("test1" and "test2"). One
of them specifies that the most recent "5" records in ScheduleHistory
should be kept, and the other specifies that the most recent "3"
records in ScheduleHistory should be kept.

So, I need a stored procedure that can clean up this table on demand.
I can't figure this one out...it's over my head.

Dan

------
SCRIPT BELOW
------

CREATE TABLE [dbo].[Schedule] (
[ScheduleID] [int] IDENTITY (1, 1) NOT NULL ,
[TypeFullName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[TimeLapse] [int] NOT NULL ,
[TimeLapseMeasurement] [varchar] (2) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RetryTimeLapse] [int] NOT NULL ,
[RetryTimeLapseMeasurement] [varchar] (2) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RetainHistoryNum] [int] NOT NULL ,
[ObjectDependencies] [varchar] (300) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ScheduleHistory] (
[ScheduleHistoryID] [int] IDENTITY (1, 1) NOT NULL ,
[ScheduleID] [int] NOT NULL ,
[StartDate] [datetime] NOT NULL ,
[EndDate] [datetime] NULL ,
[Succeeded] [bit] NULL ,
[LogNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NextStart] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

INSERT INTO Schedule VALUES ('Test1', 1, 'd', 1, 'm', 5, '')
INSERT INTO Schedule VALUES ('Test2', 1, 'd', 1, 'm', 3, '')
INSERT INTO ScheduleHistory VALUES (1,'1/1/2000 1:00 PM', '1/1/2000
1:01 PM', 1, '', '1/2/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/1/2000 1:00 PM', '1/1/2000
1:01 PM', 1, '', '1/2/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/2/2000 1:00 PM', '1/2/2000
1:01 PM', 1, '', '1/3/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/2/2000 1:00 PM', '1/2/2000
1:01 PM', 1, '', '1/3/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/3/2000 1:00 PM', '1/3/2000
1:01 PM', 1, '', '1/4/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/3/2000 1:00 PM', '1/3/2000
1:01 PM', 1, '', '1/4/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/4/2000 1:00 PM', '1/4/2000
1:01 PM', 1, '', '1/5/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/4/2000 1:00 PM', '1/4/2000
1:01 PM', 1, '', '1/5/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/5/2000 1:00 PM', '1/5/2000
1:01 PM', 1, '', '1/6/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/5/2000 1:00 PM', '1/5/2000
1:01 PM', 1, '', '1/6/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/6/2000 1:00 PM', '1/6/2000
1:01 PM', 1, '', '1/7/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/6/2000 1:00 PM', '1/6/2000
1:01 PM', 1, '', '1/7/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/7/2000 1:00 PM', '1/7/2000
1:01 PM', 1, '', '1/8/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/7/2000 1:00 PM', '1/7/2000
1:01 PM', 1, '', '1/8/2000 1:00 PM')Assuming you want the keep the most recent rows based on StartDate, one
method:

DELETE FROM ScheduleHistory
FROM Schedule s
WHERE
(
SELECT COUNT(*)
FROM ScheduleHistory sh
WHERE
sh.ScheduleID = ScheduleHistory.ScheduleID AND
sh.ScheduleID = s.ScheduleID AND
sh.StartDate >= ScheduleHistory.StartDate
) > s.RetainHistoryNum

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Dan Caron" <dancaron-listserv0687@.mailblocks.com> wrote in message
news:4d6e6a6c.0404231047.60cd40d0@.posting.google.c om...
> I have to create a stored procedure to purge "x" # of records from a
> table. I have two tables (script below):
> Schedule
> ScheduleHistory
> I need to purge records out of ScheduleHistory. The problem is that
> the # of records that needs to be "kept" is dynamic, and stored in the
> Schedule table. So all records in ScheduleHistory should be purged,
> except for the most recent "x" number of records.
> For instance, for each Schedule row, you can specify how many records
> to "keep" at all times. I want to keep the most recent "x" # of
> ScheduleHistory rows for each Schedule.
> In the script below, I have two schedules ("test1" and "test2"). One
> of them specifies that the most recent "5" records in ScheduleHistory
> should be kept, and the other specifies that the most recent "3"
> records in ScheduleHistory should be kept.
> So, I need a stored procedure that can clean up this table on demand.
> I can't figure this one out...it's over my head.
> Dan
> ------
> SCRIPT BELOW
> ------
>
> CREATE TABLE [dbo].[Schedule] (
> [ScheduleID] [int] IDENTITY (1, 1) NOT NULL ,
> [TypeFullName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [TimeLapse] [int] NOT NULL ,
> [TimeLapseMeasurement] [varchar] (2) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [RetryTimeLapse] [int] NOT NULL ,
> [RetryTimeLapseMeasurement] [varchar] (2) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [RetainHistoryNum] [int] NOT NULL ,
> [ObjectDependencies] [varchar] (300) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[ScheduleHistory] (
> [ScheduleHistoryID] [int] IDENTITY (1, 1) NOT NULL ,
> [ScheduleID] [int] NOT NULL ,
> [StartDate] [datetime] NOT NULL ,
> [EndDate] [datetime] NULL ,
> [Succeeded] [bit] NULL ,
> [LogNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [NextStart] [datetime] NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> INSERT INTO Schedule VALUES ('Test1', 1, 'd', 1, 'm', 5, '')
> INSERT INTO Schedule VALUES ('Test2', 1, 'd', 1, 'm', 3, '')
> INSERT INTO ScheduleHistory VALUES (1,'1/1/2000 1:00 PM', '1/1/2000
> 1:01 PM', 1, '', '1/2/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/1/2000 1:00 PM', '1/1/2000
> 1:01 PM', 1, '', '1/2/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/2/2000 1:00 PM', '1/2/2000
> 1:01 PM', 1, '', '1/3/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/2/2000 1:00 PM', '1/2/2000
> 1:01 PM', 1, '', '1/3/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/3/2000 1:00 PM', '1/3/2000
> 1:01 PM', 1, '', '1/4/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/3/2000 1:00 PM', '1/3/2000
> 1:01 PM', 1, '', '1/4/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/4/2000 1:00 PM', '1/4/2000
> 1:01 PM', 1, '', '1/5/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/4/2000 1:00 PM', '1/4/2000
> 1:01 PM', 1, '', '1/5/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/5/2000 1:00 PM', '1/5/2000
> 1:01 PM', 1, '', '1/6/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/5/2000 1:00 PM', '1/5/2000
> 1:01 PM', 1, '', '1/6/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/6/2000 1:00 PM', '1/6/2000
> 1:01 PM', 1, '', '1/7/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/6/2000 1:00 PM', '1/6/2000
> 1:01 PM', 1, '', '1/7/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/7/2000 1:00 PM', '1/7/2000
> 1:01 PM', 1, '', '1/8/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/7/2000 1:00 PM', '1/7/2000
> 1:01 PM', 1, '', '1/8/2000 1:00 PM')|||That is exactly what I needed. Thank you very much Dan.

-Dan

"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<MLvic.10091$e4.6777@.newsread2.news.pas.earthlink.n et>...
> Assuming you want the keep the most recent rows based on StartDate, one
> method:
> DELETE FROM ScheduleHistory
> FROM Schedule s
> WHERE
> (
> SELECT COUNT(*)
> FROM ScheduleHistory sh
> WHERE
> sh.ScheduleID = ScheduleHistory.ScheduleID AND
> sh.ScheduleID = s.ScheduleID AND
> sh.StartDate >= ScheduleHistory.StartDate
> ) > s.RetainHistoryNum
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dan Caron" <dancaron-listserv0687@.mailblocks.com> wrote in message
> news:4d6e6a6c.0404231047.60cd40d0@.posting.google.c om...
> > I have to create a stored procedure to purge "x" # of records from a
> > table. I have two tables (script below):
> > Schedule
> > ScheduleHistory
> > I need to purge records out of ScheduleHistory. The problem is that
> > the # of records that needs to be "kept" is dynamic, and stored in the
> > Schedule table. So all records in ScheduleHistory should be purged,
> > except for the most recent "x" number of records.
> > For instance, for each Schedule row, you can specify how many records
> > to "keep" at all times. I want to keep the most recent "x" # of
> > ScheduleHistory rows for each Schedule.
> > In the script below, I have two schedules ("test1" and "test2"). One
> > of them specifies that the most recent "5" records in ScheduleHistory
> > should be kept, and the other specifies that the most recent "3"
> > records in ScheduleHistory should be kept.
> > So, I need a stored procedure that can clean up this table on demand.
> > I can't figure this one out...it's over my head.
> > Dan
> > ------
> > SCRIPT BELOW
> > ------
> > CREATE TABLE [dbo].[Schedule] (
> > [ScheduleID] [int] IDENTITY (1, 1) NOT NULL ,
> > [TypeFullName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
> > NOT NULL ,
> > [TimeLapse] [int] NOT NULL ,
> > [TimeLapseMeasurement] [varchar] (2) COLLATE
> > SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [RetryTimeLapse] [int] NOT NULL ,
> > [RetryTimeLapseMeasurement] [varchar] (2) COLLATE
> > SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [RetainHistoryNum] [int] NOT NULL ,
> > [ObjectDependencies] [varchar] (300) COLLATE
> > SQL_Latin1_General_CP1_CI_AS NOT NULL
> > ) ON [PRIMARY]
> > GO
> > CREATE TABLE [dbo].[ScheduleHistory] (
> > [ScheduleHistoryID] [int] IDENTITY (1, 1) NOT NULL ,
> > [ScheduleID] [int] NOT NULL ,
> > [StartDate] [datetime] NOT NULL ,
> > [EndDate] [datetime] NULL ,
> > [Succeeded] [bit] NULL ,
> > [LogNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [NextStart] [datetime] NULL
> > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> > GO
> > INSERT INTO Schedule VALUES ('Test1', 1, 'd', 1, 'm', 5, '')
> > INSERT INTO Schedule VALUES ('Test2', 1, 'd', 1, 'm', 3, '')
> > INSERT INTO ScheduleHistory VALUES (1,'1/1/2000 1:00 PM', '1/1/2000
> > 1:01 PM', 1, '', '1/2/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/1/2000 1:00 PM', '1/1/2000
> > 1:01 PM', 1, '', '1/2/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/2/2000 1:00 PM', '1/2/2000
> > 1:01 PM', 1, '', '1/3/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/2/2000 1:00 PM', '1/2/2000
> > 1:01 PM', 1, '', '1/3/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/3/2000 1:00 PM', '1/3/2000
> > 1:01 PM', 1, '', '1/4/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/3/2000 1:00 PM', '1/3/2000
> > 1:01 PM', 1, '', '1/4/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/4/2000 1:00 PM', '1/4/2000
> > 1:01 PM', 1, '', '1/5/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/4/2000 1:00 PM', '1/4/2000
> > 1:01 PM', 1, '', '1/5/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/5/2000 1:00 PM', '1/5/2000
> > 1:01 PM', 1, '', '1/6/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/5/2000 1:00 PM', '1/5/2000
> > 1:01 PM', 1, '', '1/6/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/6/2000 1:00 PM', '1/6/2000
> > 1:01 PM', 1, '', '1/7/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/6/2000 1:00 PM', '1/6/2000
> > 1:01 PM', 1, '', '1/7/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/7/2000 1:00 PM', '1/7/2000
> > 1:01 PM', 1, '', '1/8/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/7/2000 1:00 PM', '1/7/2000
> > 1:01 PM', 1, '', '1/8/2000 1:00 PM')|||That is exactly what I needed. Thank you very much Dan.

-Dan

"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<MLvic.10091$e4.6777@.newsread2.news.pas.earthlink.n et>...
> Assuming you want the keep the most recent rows based on StartDate, one
> method:
> DELETE FROM ScheduleHistory
> FROM Schedule s
> WHERE
> (
> SELECT COUNT(*)
> FROM ScheduleHistory sh
> WHERE
> sh.ScheduleID = ScheduleHistory.ScheduleID AND
> sh.ScheduleID = s.ScheduleID AND
> sh.StartDate >= ScheduleHistory.StartDate
> ) > s.RetainHistoryNum
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dan Caron" <dancaron-listserv0687@.mailblocks.com> wrote in message
> news:4d6e6a6c.0404231047.60cd40d0@.posting.google.c om...
> > I have to create a stored procedure to purge "x" # of records from a
> > table. I have two tables (script below):
> > Schedule
> > ScheduleHistory
> > I need to purge records out of ScheduleHistory. The problem is that
> > the # of records that needs to be "kept" is dynamic, and stored in the
> > Schedule table. So all records in ScheduleHistory should be purged,
> > except for the most recent "x" number of records.
> > For instance, for each Schedule row, you can specify how many records
> > to "keep" at all times. I want to keep the most recent "x" # of
> > ScheduleHistory rows for each Schedule.
> > In the script below, I have two schedules ("test1" and "test2"). One
> > of them specifies that the most recent "5" records in ScheduleHistory
> > should be kept, and the other specifies that the most recent "3"
> > records in ScheduleHistory should be kept.
> > So, I need a stored procedure that can clean up this table on demand.
> > I can't figure this one out...it's over my head.
> > Dan
> > ------
> > SCRIPT BELOW
> > ------
> > CREATE TABLE [dbo].[Schedule] (
> > [ScheduleID] [int] IDENTITY (1, 1) NOT NULL ,
> > [TypeFullName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
> > NOT NULL ,
> > [TimeLapse] [int] NOT NULL ,
> > [TimeLapseMeasurement] [varchar] (2) COLLATE
> > SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [RetryTimeLapse] [int] NOT NULL ,
> > [RetryTimeLapseMeasurement] [varchar] (2) COLLATE
> > SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [RetainHistoryNum] [int] NOT NULL ,
> > [ObjectDependencies] [varchar] (300) COLLATE
> > SQL_Latin1_General_CP1_CI_AS NOT NULL
> > ) ON [PRIMARY]
> > GO
> > CREATE TABLE [dbo].[ScheduleHistory] (
> > [ScheduleHistoryID] [int] IDENTITY (1, 1) NOT NULL ,
> > [ScheduleID] [int] NOT NULL ,
> > [StartDate] [datetime] NOT NULL ,
> > [EndDate] [datetime] NULL ,
> > [Succeeded] [bit] NULL ,
> > [LogNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [NextStart] [datetime] NULL
> > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> > GO
> > INSERT INTO Schedule VALUES ('Test1', 1, 'd', 1, 'm', 5, '')
> > INSERT INTO Schedule VALUES ('Test2', 1, 'd', 1, 'm', 3, '')
> > INSERT INTO ScheduleHistory VALUES (1,'1/1/2000 1:00 PM', '1/1/2000
> > 1:01 PM', 1, '', '1/2/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/1/2000 1:00 PM', '1/1/2000
> > 1:01 PM', 1, '', '1/2/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/2/2000 1:00 PM', '1/2/2000
> > 1:01 PM', 1, '', '1/3/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/2/2000 1:00 PM', '1/2/2000
> > 1:01 PM', 1, '', '1/3/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/3/2000 1:00 PM', '1/3/2000
> > 1:01 PM', 1, '', '1/4/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/3/2000 1:00 PM', '1/3/2000
> > 1:01 PM', 1, '', '1/4/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/4/2000 1:00 PM', '1/4/2000
> > 1:01 PM', 1, '', '1/5/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/4/2000 1:00 PM', '1/4/2000
> > 1:01 PM', 1, '', '1/5/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/5/2000 1:00 PM', '1/5/2000
> > 1:01 PM', 1, '', '1/6/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/5/2000 1:00 PM', '1/5/2000
> > 1:01 PM', 1, '', '1/6/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/6/2000 1:00 PM', '1/6/2000
> > 1:01 PM', 1, '', '1/7/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/6/2000 1:00 PM', '1/6/2000
> > 1:01 PM', 1, '', '1/7/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/7/2000 1:00 PM', '1/7/2000
> > 1:01 PM', 1, '', '1/8/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/7/2000 1:00 PM', '1/7/2000
> > 1:01 PM', 1, '', '1/8/2000 1:00 PM')

Help: Purge Table Stored Procedure

I have to create a stored procedure to purge "x" # of records from a
table. I have two tables (script below):

Schedule
ScheduleHistory

I need to purge records out of ScheduleHistory. The problem is that
the # of records that needs to be "kept" is dynamic, and stored in the
Schedule table. So all records in ScheduleHistory should be purged,
except for the most recent "x" number of records.

For instance, for each Schedule row, you can specify how many records
to "keep" at all times. I want to keep the most recent "x" # of
ScheduleHistory rows for each Schedule.

In the script below, I have two schedules ("test1" and "test2"). One
of them specifies that the most recent "5" records in ScheduleHistory
should be kept, and the other specifies that the most recent "3"
records in ScheduleHistory should be kept.

So, I need a stored procedure that can clean up this table on demand.
I can't figure this one out...it's over my head.

Dan

------
SCRIPT BELOW
------

CREATE TABLE [dbo].[Schedule] (
[ScheduleID] [int] IDENTITY (1, 1) NOT NULL ,
[TypeFullName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[TimeLapse] [int] NOT NULL ,
[TimeLapseMeasurement] [varchar] (2) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RetryTimeLapse] [int] NOT NULL ,
[RetryTimeLapseMeasurement] [varchar] (2) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RetainHistoryNum] [int] NOT NULL ,
[ObjectDependencies] [varchar] (300) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ScheduleHistory] (
[ScheduleHistoryID] [int] IDENTITY (1, 1) NOT NULL ,
[ScheduleID] [int] NOT NULL ,
[StartDate] [datetime] NOT NULL ,
[EndDate] [datetime] NULL ,
[Succeeded] [bit] NULL ,
[LogNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NextStart] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

INSERT INTO Schedule VALUES ('Test1', 1, 'd', 1, 'm', 5, '')
INSERT INTO Schedule VALUES ('Test2', 1, 'd', 1, 'm', 3, '')
INSERT INTO ScheduleHistory VALUES (1,'1/1/2000 1:00 PM', '1/1/2000
1:01 PM', 1, '', '1/2/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/1/2000 1:00 PM', '1/1/2000
1:01 PM', 1, '', '1/2/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/2/2000 1:00 PM', '1/2/2000
1:01 PM', 1, '', '1/3/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/2/2000 1:00 PM', '1/2/2000
1:01 PM', 1, '', '1/3/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/3/2000 1:00 PM', '1/3/2000
1:01 PM', 1, '', '1/4/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/3/2000 1:00 PM', '1/3/2000
1:01 PM', 1, '', '1/4/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/4/2000 1:00 PM', '1/4/2000
1:01 PM', 1, '', '1/5/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/4/2000 1:00 PM', '1/4/2000
1:01 PM', 1, '', '1/5/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/5/2000 1:00 PM', '1/5/2000
1:01 PM', 1, '', '1/6/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/5/2000 1:00 PM', '1/5/2000
1:01 PM', 1, '', '1/6/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/6/2000 1:00 PM', '1/6/2000
1:01 PM', 1, '', '1/7/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/6/2000 1:00 PM', '1/6/2000
1:01 PM', 1, '', '1/7/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/7/2000 1:00 PM', '1/7/2000
1:01 PM', 1, '', '1/8/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/7/2000 1:00 PM', '1/7/2000
1:01 PM', 1, '', '1/8/2000 1:00 PM')Assuming you want the keep the most recent rows based on StartDate, one
method:

DELETE FROM ScheduleHistory
FROM Schedule s
WHERE
(
SELECT COUNT(*)
FROM ScheduleHistory sh
WHERE
sh.ScheduleID = ScheduleHistory.ScheduleID AND
sh.ScheduleID = s.ScheduleID AND
sh.StartDate >= ScheduleHistory.StartDate
) > s.RetainHistoryNum

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Dan Caron" <dancaron-listserv0687@.mailblocks.com> wrote in message
news:4d6e6a6c.0404231047.60cd40d0@.posting.google.c om...
> I have to create a stored procedure to purge "x" # of records from a
> table. I have two tables (script below):
> Schedule
> ScheduleHistory
> I need to purge records out of ScheduleHistory. The problem is that
> the # of records that needs to be "kept" is dynamic, and stored in the
> Schedule table. So all records in ScheduleHistory should be purged,
> except for the most recent "x" number of records.
> For instance, for each Schedule row, you can specify how many records
> to "keep" at all times. I want to keep the most recent "x" # of
> ScheduleHistory rows for each Schedule.
> In the script below, I have two schedules ("test1" and "test2"). One
> of them specifies that the most recent "5" records in ScheduleHistory
> should be kept, and the other specifies that the most recent "3"
> records in ScheduleHistory should be kept.
> So, I need a stored procedure that can clean up this table on demand.
> I can't figure this one out...it's over my head.
> Dan
> ------
> SCRIPT BELOW
> ------
>
> CREATE TABLE [dbo].[Schedule] (
> [ScheduleID] [int] IDENTITY (1, 1) NOT NULL ,
> [TypeFullName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [TimeLapse] [int] NOT NULL ,
> [TimeLapseMeasurement] [varchar] (2) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [RetryTimeLapse] [int] NOT NULL ,
> [RetryTimeLapseMeasurement] [varchar] (2) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [RetainHistoryNum] [int] NOT NULL ,
> [ObjectDependencies] [varchar] (300) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[ScheduleHistory] (
> [ScheduleHistoryID] [int] IDENTITY (1, 1) NOT NULL ,
> [ScheduleID] [int] NOT NULL ,
> [StartDate] [datetime] NOT NULL ,
> [EndDate] [datetime] NULL ,
> [Succeeded] [bit] NULL ,
> [LogNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [NextStart] [datetime] NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> INSERT INTO Schedule VALUES ('Test1', 1, 'd', 1, 'm', 5, '')
> INSERT INTO Schedule VALUES ('Test2', 1, 'd', 1, 'm', 3, '')
> INSERT INTO ScheduleHistory VALUES (1,'1/1/2000 1:00 PM', '1/1/2000
> 1:01 PM', 1, '', '1/2/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/1/2000 1:00 PM', '1/1/2000
> 1:01 PM', 1, '', '1/2/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/2/2000 1:00 PM', '1/2/2000
> 1:01 PM', 1, '', '1/3/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/2/2000 1:00 PM', '1/2/2000
> 1:01 PM', 1, '', '1/3/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/3/2000 1:00 PM', '1/3/2000
> 1:01 PM', 1, '', '1/4/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/3/2000 1:00 PM', '1/3/2000
> 1:01 PM', 1, '', '1/4/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/4/2000 1:00 PM', '1/4/2000
> 1:01 PM', 1, '', '1/5/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/4/2000 1:00 PM', '1/4/2000
> 1:01 PM', 1, '', '1/5/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/5/2000 1:00 PM', '1/5/2000
> 1:01 PM', 1, '', '1/6/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/5/2000 1:00 PM', '1/5/2000
> 1:01 PM', 1, '', '1/6/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/6/2000 1:00 PM', '1/6/2000
> 1:01 PM', 1, '', '1/7/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/6/2000 1:00 PM', '1/6/2000
> 1:01 PM', 1, '', '1/7/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/7/2000 1:00 PM', '1/7/2000
> 1:01 PM', 1, '', '1/8/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/7/2000 1:00 PM', '1/7/2000
> 1:01 PM', 1, '', '1/8/2000 1:00 PM')

HELP: Physical Database Design

Hello to all.

I'm having trouble translating this requirement into physical DB tables:

- Information about users in general are:

- Username and Password

- Users belong to a group which have different roles

- (e.g. Customers, Administrators)

- Users can only belong to one group

- Information about groups are:

- GroupID, GroupName, Roles

- Customers and Administrators have different fields

- Customers : (CustomerID, CompanyName, BillingAddress, ShippingAddress, etc.)

- Administrators: (AdministratorID, AdministratorName)

- The system must be able to do the following:

- Ban a group

- Ban a specific user

- Information about a user must be based on the group that he/she is in. (e.g. Customer info must be queried from the Customers table) with the username as the only provided information initially.

- Add a new user type in the future

Any comments and suggestions will do. Thanks in advance.

- Raymond

Here is a good resource for classwork design problems such as this.

If you review some of the designs listed at this site, you will have a better idea of how to accomplish your assignment. (Note: the models and designs at this site are only a suggestion and should be used with caution -but they are better than nothing...)

http://www.databaseanswers.org/data_models/index.htm

HELP: Installing SQl server databases

Dear all,
One of our application consist of configuring an SQL server database with
user rights to tables and preparing corresponding store procedure.
usually when we do this, we prepare the server in our test environment and
then ship the whole hardware pre configure to customers.
in some cases we do not have the customer server, so we need to do the whole
configuration on site and in cas of errors its hard to find.
is there an easy way to prepare the SQL confisuration database, store
procedure, table rights on a test PC, then do a kind of straight forward
deployement on any server running SQL ?
thanks for your help
regards
SergeHi,
You can create the customer database in your local sql server. Do all the
setups in that new database. backup the database or detach the database.
Copy the backup or MDF and LDF file and take it in a tape. Restore the files
from tape to Customer side server and
do a Database restore or Attach the MDF and LDF.
Incase if you have login an user suid mismacth use the system stored
procedure sp_change_users_login to fix.
This will ensure that all the configurations are avaiable.
Thanks
Hari
SQL Server MVP
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:EDEAF3C8-903B-49D6-8A48-9FD1B71E379F@.microsoft.com...
> Dear all,
> One of our application consist of configuring an SQL server database with
> user rights to tables and preparing corresponding store procedure.
> usually when we do this, we prepare the server in our test environment and
> then ship the whole hardware pre configure to customers.
> in some cases we do not have the customer server, so we need to do the
> whole
> configuration on site and in cas of errors its hard to find.
> is there an easy way to prepare the SQL confisuration database, store
> procedure, table rights on a test PC, then do a kind of straight forward
> deployement on any server running SQL ?
> thanks for your help
> regards
> Serge|||I have seen the method suggested earlier about taking the mdf and ldf and
using sp_attach_Db and in most cases you should be OK ( except for
permissions.) However the risk you are taking is with the file version...
The physical files will only have a limited life span, as MS changes things
usually attach works for one release backwards...
Another way is to generate scripts... These take longer to run, but are
usually safer because basic syntax does not change that often...One thing
you might do is combine the two... In your release, generate the scripts.
Then when you are about to go and setup a db for a user, THEN run the
scripts, and take the resulting mdf and ldf...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:EDEAF3C8-903B-49D6-8A48-9FD1B71E379F@.microsoft.com...
> Dear all,
> One of our application consist of configuring an SQL server database with
> user rights to tables and preparing corresponding store procedure.
> usually when we do this, we prepare the server in our test environment and
> then ship the whole hardware pre configure to customers.
> in some cases we do not have the customer server, so we need to do the
> whole
> configuration on site and in cas of errors its hard to find.
> is there an easy way to prepare the SQL confisuration database, store
> procedure, table rights on a test PC, then do a kind of straight forward
> deployement on any server running SQL ?
> thanks for your help
> regards
> Serge|||I am a bit confuse as I am new in that part.
let say that my ServerA is my test server in my office on which I prepare
every thing
lets call ServerB the target server on which th final DB will run
Could you please details me which operation to do on which server?
What is the content of those MDF and LDF files ?
How to deploy rights or group from server A to ServerB at the same time
thanks and sorry to disturb
experince will come with the time :-)
serge
"Wayne Snyder" wrote:
> I have seen the method suggested earlier about taking the mdf and ldf and
> using sp_attach_Db and in most cases you should be OK ( except for
> permissions.) However the risk you are taking is with the file version...
> The physical files will only have a limited life span, as MS changes things
> usually attach works for one release backwards...
> Another way is to generate scripts... These take longer to run, but are
> usually safer because basic syntax does not change that often...One thing
> you might do is combine the two... In your release, generate the scripts.
> Then when you are about to go and setup a db for a user, THEN run the
> scripts, and take the resulting mdf and ldf...
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
> message news:EDEAF3C8-903B-49D6-8A48-9FD1B71E379F@.microsoft.com...
> > Dear all,
> >
> > One of our application consist of configuring an SQL server database with
> > user rights to tables and preparing corresponding store procedure.
> >
> > usually when we do this, we prepare the server in our test environment and
> > then ship the whole hardware pre configure to customers.
> >
> > in some cases we do not have the customer server, so we need to do the
> > whole
> > configuration on site and in cas of errors its hard to find.
> >
> > is there an easy way to prepare the SQL confisuration database, store
> > procedure, table rights on a test PC, then do a kind of straight forward
> > deployement on any server running SQL ?
> >
> > thanks for your help
> > regards
> > Serge
>
>|||On ServerA you will prepare the database to be installed, call it DBa. Once
this is complete you have 2 choices. 1) You can dump DBa and use the
resulting dump file, or 2) you can detach DBa and use the MDF and LDF files.
Copy either the dump file or MDF and LDF files to ServerB.
If you selected the database dump option, restore DBa. See Books Online for
the syntax.
If you selected the detach option, attach DBa. Again see Books Online for
the syntax.
As mentioned in one of the earlier posts if you created any data server
logins and database users you will need to fix those up via the
sp_change_users_login stored proc. See Books Online for documentation.
"serge calderara" wrote:
> I am a bit confuse as I am new in that part.
> let say that my ServerA is my test server in my office on which I prepare
> every thing
> lets call ServerB the target server on which th final DB will run
> Could you please details me which operation to do on which server?
> What is the content of those MDF and LDF files ?
> How to deploy rights or group from server A to ServerB at the same time
> thanks and sorry to disturb
> experince will come with the time :-)
> serge
> "Wayne Snyder" wrote:
> > I have seen the method suggested earlier about taking the mdf and ldf and
> > using sp_attach_Db and in most cases you should be OK ( except for
> > permissions.) However the risk you are taking is with the file version...
> > The physical files will only have a limited life span, as MS changes things
> > usually attach works for one release backwards...
> >
> > Another way is to generate scripts... These take longer to run, but are
> > usually safer because basic syntax does not change that often...One thing
> > you might do is combine the two... In your release, generate the scripts.
> > Then when you are about to go and setup a db for a user, THEN run the
> > scripts, and take the resulting mdf and ldf...
> >
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
> > message news:EDEAF3C8-903B-49D6-8A48-9FD1B71E379F@.microsoft.com...
> > > Dear all,
> > >
> > > One of our application consist of configuring an SQL server database with
> > > user rights to tables and preparing corresponding store procedure.
> > >
> > > usually when we do this, we prepare the server in our test environment and
> > > then ship the whole hardware pre configure to customers.
> > >
> > > in some cases we do not have the customer server, so we need to do the
> > > whole
> > > configuration on site and in cas of errors its hard to find.
> > >
> > > is there an easy way to prepare the SQL confisuration database, store
> > > procedure, table rights on a test PC, then do a kind of straight forward
> > > deployement on any server running SQL ?
> > >
> > > thanks for your help
> > > regards
> > > Serge
> >
> >
> >

Help: I need a MS SQL query that will find relationships of a table

Hello,
If anyone can help me I will be forever grateful :-)
In MSSQL, I have table 'x'...
I need a query that will show all of the tables and primary keys that
table 'x' has relationships with.
so..say table 'x' has the following columns
x
--
id (PK)
table1FK (FK to 'table1')
table2FK (FK to 'table2')
dummy
and I have...
table1 table2
-- --
id (PK) id (PK)
dummy dummy
The pseudo query is...
SELECT table_name, primary_key
FROM system_tables
WHERE original_table = 'x';
please please help!
thanks so much..
-JayHi Jason,
Check sp_foreignkeys in Books Online.
--
Jacco Schalkwijk
SQL Server MVP
"Jason Baldwin" <jaybald@.yahoo.com> wrote in message
news:5a4fce6c.0310080733.4811a11d@.posting.google.com...
> Hello,
> If anyone can help me I will be forever grateful :-)
> In MSSQL, I have table 'x'...
> I need a query that will show all of the tables and primary keys that
> table 'x' has relationships with.
> so..say table 'x' has the following columns
> x
> --
> id (PK)
> table1FK (FK to 'table1')
> table2FK (FK to 'table2')
> dummy
> and I have...
> table1 table2
> -- --
> id (PK) id (PK)
> dummy dummy
>
> The pseudo query is...
> SELECT table_name, primary_key
> FROM system_tables
> WHERE original_table = 'x';
>
> please please help!
> thanks so much..
> -Jay

Help: Find the different records from 3 tables

I have 3 tables a , ab and b. table ab is the maping table between a
and b. table a contains multiple atid for aid. Same as table b. Now I
want find those aid and bid that contains different atid or btid.
please see the following scripts:
create table a
(aid int, atid int)
create table b
(bid int, btid int)
create table ab
(aid int, bid int)
insert into ab
values(1,1)
insert into ab
values(2,2)
insert into ab
values(3,3)
insert into ab
values(4,4)
insert into a
values(1,1)
insert into a
values(1,2)
insert into a
values(2,5)
insert into a
values(3,3)
insert into a
values(3,4)
insert into a
values(4,7)
insert into b
values(1,1)
insert into b
values(1,2)
insert into b
values(2,5)
insert into b
values(3,3)
insert into b
values(3,7)
insert into b
values(4,6)
I want get 3 and 4 .
because aid 1 contains (1,2) which is same as bid 1
aid 2 contain (5) which is same as bid 2
aid 3 contains (3,4) which is DIFF with bid 3 which contains(3,7)
aid 4 contains (7) which is DIFF with bid 4 which contains(6)
but if I execute the following I get 4 only, because one record (3, 3)
is satify the where condition.
select * from
ab
where ab.aid not in(
select a.aid from
a inner join ab on a.aid = ab.aid
inner join b on ab.bid = b.bid
where a.atid = b.btid
)
help pleaseselect distinct aid from(
select aid, count(*) cnt from(
select aid, atid from a
union all
select aid, btid atid from ab join b on ab.bid=b.bid
) t
group by aid, atid
having count(*)=1
) t|||select distinct ab.aid
from ab join a on ab.aid=a.aid
full outer join b on ab.bid=b.bid and a.atid=b.btid
where (a.aid is null or b.bid is null)
and ab.aid is not null|||Excellent, Thank you!!

HELP: Exporting for use in MySQL

Ok, anyone know anything about MySQL? The only way I see to use data within
tables for use in MySQL is to export as text file but I dont see a way to
import the data from the MySQL Console Manager.
Then my next consideration would be a query to create the tables and insert
it into te MySQL database.
Thoughts anyone?
"Matrix"
Vice President
Permian Basin Chapter
Bikers Against Child AbuseLots on MySQL here:
http://www.mysql.com/documentation/index.html
--
David Portas
--
Please reply only to the newsgroup
--
"Matrix" <matrix@.!nospam.visualsun.ws> wrote in message
news:vnjs143fb0q9a1@.corp.supernews.com...
> Ok, anyone know anything about MySQL? The only way I see to use data
within
> tables for use in MySQL is to export as text file but I dont see a way to
> import the data from the MySQL Console Manager.
> Then my next consideration would be a query to create the tables and
insert
> it into te MySQL database.
> Thoughts anyone?
> "Matrix"
> Vice President
> Permian Basin Chapter
> Bikers Against Child Abuse
>