Having trouble distilling something down to a single query

Ok, lets say I have 2 tables, A & B.

Lets say table a has the key A.record_id, and the field emp.

Say table b has the key B.record_id, a foreign key B.a_id that links it to table A, and the field B.date. Now, I want to join these tables as so:

SELECT
A."RECORD_ID", A."EMP",
B."RECORD_ID", B."DATE"
FROM
{ oj "DBA"."A" TABLE_A LEFT OUTER JOIN "DBA"."B" TABLE B ON
A."RECORD_ID" = B."A_ID"}

You see, I want a list of all A.record_id, whether or not I get a return from the B table.

The problem arises when I want to limit the dates via B.date. It's clear to me what the problem is here, I just don't know a way around it.

WHERE
(B."DATE" IS NULL OR
(B."DATE" >= {d '2004-01-01'} AND
B."DATE" <= {d '2004-01-31'}))

So basically, now I'm not getting any a.record_id's for a's that are linked to a b that fall outside of that date range.

Summing up I want...

All A + B where there is a B.date in that range
No A+B for results that are not within the entered date range.
All A's, regardless of if there is a linked B.
All A's, even if there are linked B's outside of the date range.
All in 1 statement (due to environment limitations).

Thanks for your help. I'm pretty much self taught here, so I apologize for not having the language knowledge to make this question more concise. Of course if I knew better how to explain what I'm trying to do then I'd probably know how to do it. ;-)

Mock Sample Data

table A
A001 bill
A002 bill
A003 bill
A004 frank
A005 frank
A006 bob

table B
B001 A001 1/1/2004
B002 A001 1/15/2004
B003 A001 4/1/2004
B004 A003 5/1/2004
B005 A004 1/1/2004
B006 A005 3/3/2004

Mock Results

A001 bill B001 1/1/2004
A001 bill B002 1/15/2004
A002 bill NULL NULL
A003 bill NULL NULL
A004 frank B004 1/1/2004
A005 frank NULL NULL
A006 bob NULL NULL


edit: added mock data/resultsI'd use:(B."DATE" IS NULL OR
(B."DATE" >= {d '2004-01-01'} AND
B."DATE" <= {d '2004-01-31'}))-PatP|||Sorry, that's how it's in there now, editing above to reflect.

Not the problem.|||What version of which database engine are you using?

-PatP|||Using Sybase 9.|||I just noticed some apparent inconsistancies in your query. Could you post the entire query as you are submitting it so that I can try it? I'm using version 8, but I'd expect that to be close enough.

-PatP|||SELECT
A."RECORD_ID", A."EMP",
B."RECORD_ID", B."DATE"
FROM
{ oj "DBA"."A" A LEFT OUTER JOIN "DBA"."B" B ON
A."RECORD_ID" = B."A_ID"}
WHERE
(B."DATE" IS NULL OR
(B."DATE" >= {d '2004-01-01'} AND
B."DATE" <= {d '2004-01-31'}))

Mock Current Results From Earlier Mock Data

A001 bill B001 1/1/2004
A001 bill B002 1/15/2004
A002 bill NULL NULL
A004 frank B004 1/1/2004
A005 frank NULL NULL

Thanks for looking at this.|||Give up? ;-)|||Try a LEFT OUTER JOIN to a Subquery that restricts your table B.

SELECT
A."RECORD_ID", A."EMP",
B."RECORD_ID", B."DATE"
FROM
{ oj "DBA"."A" TABLE_A LEFT OUTER JOIN
(Select *
FROM "DBA"."B" TABLE B where ((B.DAte <='1/31/2004' and B.Date >='1/01/2004') OR B.DAte is NULL)) as S1
on A."RECORD_ID" = S1."A_ID"}

I may have a typo with the brackets up there, but something like that should work.

The key is that you are creating a subquery with results restricted to your data range, and then naming that subquery S1. Then the results of S1 are joined to table A.|||1. All A + B where there is a B.date in that range
2. No A+B for results that are not within the entered date range.
3. All A's, regardless of if there is a linked B.
4. All A's, even if there are linked B's outside of the date range.
5. All in 1 statement (due to environment limitations).
Unless I am missing something, - there are contradicting conditions in your requirements:

If #1 is to be met Then #3 & #4 cannot be
If #2 is to be met Then #4 cannot be
If #3 is to be met Then B.date is NULL, thus #1 cannot be
If #4 is to be met...see above

Can you clarify?

Having difficulty setting Back Up to back up file wihout datetime stamp SQL 2K

Hello,

I'm trying to create a simple back up in the SQL Maintenance Plan that will
make a single back up copy of all database every night at 10 pm. I'd like
the previous nights file to be overwritten, so there will be only a single
back up file for each database (tape back up runs every night, so each days
back up will be saved on tape).

Every night the maintenance plan makes a back up of all the databases to a
new file with a datetime stamp, meaning the previous nights file still
exists. Even when I check "Remove files older than 22 hours" the previous
nights file still exists. Is there any way to create a back up file without
the date time stamp so it overwrites the previous nights file?

Thanks!
RickOn 24.04.2007 15:29, Rico wrote:

Quote:

Originally Posted by

I'm trying to create a simple back up in the SQL Maintenance Plan that will
make a single back up copy of all database every night at 10 pm. I'd like
the previous nights file to be overwritten, so there will be only a single
back up file for each database (tape back up runs every night, so each days
back up will be saved on tape).
>
Every night the maintenance plan makes a back up of all the databases to a
new file with a datetime stamp, meaning the previous nights file still
exists. Even when I check "Remove files older than 22 hours" the previous
nights file still exists. Is there any way to create a back up file without
the date time stamp so it overwrites the previous nights file?

Yes, I believe option is "WITH INIT". Please see BOL for details.

Regards

robert|||Hi Robert,

Thanks, but I should have mentioned that I'm using SQL 2000 and I'm not
using T-SQL, just trying to create the Maintenance plan from the Enterprise
Manager.

Rick

"Robert Klemme" <shortcutter@.googlemail.comwrote in message
news:596ghdF2jtij6U1@.mid.individual.net...

Quote:

Originally Posted by

On 24.04.2007 15:29, Rico wrote:

Quote:

Originally Posted by

>I'm trying to create a simple back up in the SQL Maintenance Plan that
>will
>make a single back up copy of all database every night at 10 pm. I'd
>like
>the previous nights file to be overwritten, so there will be only a
>single
>back up file for each database (tape back up runs every night, so each
>days
>back up will be saved on tape).
>>
>Every night the maintenance plan makes a back up of all the databases to
>a
>new file with a datetime stamp, meaning the previous nights file still
>exists. Even when I check "Remove files older than 22 hours" the
>previous
>nights file still exists. Is there any way to create a back up file
>without
>the date time stamp so it overwrites the previous nights file?

>
Yes, I believe option is "WITH INIT". Please see BOL for details.
>
Regards
>
robert

|||Rico (me@.you.com) writes:

Quote:

Originally Posted by

Thanks, but I should have mentioned that I'm using SQL 2000 and I'm not
using T-SQL, just trying to create the Maintenance plan from the
Enterprise Manager.

From what you described, you should not use a maintenance plan. Just set up
a scheduled job to run the BACKUP statement. You can do this from the
GUI where you backup databases, and select Schedule somewhere on a button.
In the end you get a one-step job that has a BACKUP job.

Then again, if you have any interest in acquiring basic DBA skills, you
should certainly learn to write basic BACKUP commands in T-SQL.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland,

I don't have a problem creating the T-SQL commands, it's using the
Enterprise Manager to do more than create dbs is where I get lost.

I will give that a try (creating a back up job and scheduling)

THanks!
Rick

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns991D5A7E496DYazorman@.127.0.0.1...

Quote:

Originally Posted by

Rico (me@.you.com) writes:

Quote:

Originally Posted by

>Thanks, but I should have mentioned that I'm using SQL 2000 and I'm not
>using T-SQL, just trying to create the Maintenance plan from the
>Enterprise Manager.

>
From what you described, you should not use a maintenance plan. Just set
up
a scheduled job to run the BACKUP statement. You can do this from the
GUI where you backup databases, and select Schedule somewhere on a button.
In the end you get a one-step job that has a BACKUP job.
>
Then again, if you have any interest in acquiring basic DBA skills, you
should certainly learn to write basic BACKUP commands in T-SQL.
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||
"Rico" <me@.you.comwrote in message news:8LvXh.9$_G.8@.edtnps89...

Quote:

Originally Posted by

Thanks Erland,
>
I don't have a problem creating the T-SQL commands, it's using the
Enterprise Manager to do more than create dbs is where I get lost.

That's the problem with using EM.

BTW... the reason you're seeing the behavior you're seeing is that the
maintenance job takes the conservative approach and assumes that the most
recent backup HAS to succeed before it'll delete the older one.

So setting it to 22 hours or anything won't force it to delete the older
file until the new one is successfully created.

Quote:

Originally Posted by

>
I will give that a try (creating a back up job and scheduling)
>
THanks!
Rick
>
>
"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns991D5A7E496DYazorman@.127.0.0.1...

Quote:

Originally Posted by

>Rico (me@.you.com) writes:

Quote:

Originally Posted by

>>Thanks, but I should have mentioned that I'm using SQL 2000 and I'm not
>>using T-SQL, just trying to create the Maintenance plan from the
>>Enterprise Manager.

>>
>From what you described, you should not use a maintenance plan. Just set
>up
>a scheduled job to run the BACKUP statement. You can do this from the
>GUI where you backup databases, and select Schedule somewhere on a
>button.
>In the end you get a one-step job that has a BACKUP job.
>>
>Then again, if you have any interest in acquiring basic DBA skills, you
>should certainly learn to write basic BACKUP commands in T-SQL.
>>
>--
>Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>>
>Books Online for SQL Server 2005 at
>http://www.microsoft.com/technet/pr...oads/books.mspx
>Books Online for SQL Server 2000 at
>http://www.microsoft.com/sql/prodin...ions/books.mspx

>
>

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

hash table (#) order by problem with more records

We have one single hash (#) table, in which we insert data processing
priority wise (after calculating priority).
for. e.g.

Company Product Priority Prod. QtyProd_Plan_Date
C1 P11100
C1 P22 50
C1 P33 30
C2 P11200
C2 P42 40
C2 P53 10

There is a problem when accessing data for usage priority wise.
Problem is as follows:

We want to plan production date as per group (company) sorted order and
priority wise.

==>With less data, it works fine.
==>But when there are more records for e.g. 100000 or more , it changes
the logical order of data

So plan date calculation gets effected.

==Although I have solved this problem with putting identity column and
checking in where condition.

But, I want to know why this problem is coming.

If anybody have come across this similar problem, please let me know
the reason and your solution.

IS IT SQL SERVER PROBLEM?

Thanks & Regards,
T.S.Negi> when there are more records for e.g. 100000 or more , it changes
> the logical order of data

Are you referring to the perceived order in the table? Rows in tables
have NO logical order in a relational database. If you require a
particular order you have to query them using a SELECT statement with
an ORDER BY clause otherwise the ordering is undefined.

If that doesn't answer your question then please describe your problem
with DDL (including keys), sample data INSERT statements and show your
required end result.

--
David Portas
SQL Server MVP
--|||While inserting records in hash table. It is already order by on some
fields.
But when selecting/updating records, I want the same order of records
should be updated/selected.

"Rows in tables have NO logical order in a relational database"
I think, True for hash(#) and permanent table.

T.S.Negi

David Portas wrote:
> > when there are more records for e.g. 100000 or more , it changes
> > the logical order of data
> Are you referring to the perceived order in the table? Rows in tables
> have NO logical order in a relational database. If you require a
> particular order you have to query them using a SELECT statement with
> an ORDER BY clause otherwise the ordering is undefined.
> If that doesn't answer your question then please describe your
problem
> with DDL (including keys), sample data INSERT statements and show
your
> required end result.
> --
> David Portas
> SQL Server MVP
> --|||There is an update condition. Which I want to make sure, performing on
ordered data (order by used at the time of insert).
I want to avoide loop.

Reason: "Rows in tables have NO logical order in a relational database"
!!!!

So Please advice.
Thanks,
T.S.Negi

Sample SQL:
===========

UPDATE #WK_PDR_ProcessingData SET
@.Opn_Stock_Qty= CASE WHEN (
@.Customer_Cd = Customer_Cd
AND @.Product_No = Product_No
AND @.Product_Site_Cd = Product_Site_Cd
AND @.Assy_Company_Cd = Assy_Company_Cd
AND @.Assy_Section_Cd = Assy_Section_Cd
AND @.Line_Cd = Line_Cd
) THEN @.Opn_Stock_Qty + @.Production_Qty - @.Requirement_Qty
ELSE begin_Stock_Qty END,
Calc_Stock_Qty= @.Opn_Stock_Qty + Production_Qty - Requirement_Qty,
@.Customer_Cd = Customer_Cd,
@.Product_No = Product_No,
@.Product_Site_Cd= Product_Site_Cd,
@.Assy_Company_Cd= Assy_Company_Cd,
@.Assy_Section_Cd= Assy_Section_Cd,
@.Line_Cd = Line_Cd,
@.Production_Qty = Production_Qty,
@.Requirement_Qty= Requirement_Qty
FROM #WK_PDR_ProcessingData|||tilak.negi@.mind-infotech.com (tilak.negi@.mind-infotech.com) writes:
> While inserting records in hash table. It is already order by on some
> fields.

And once it is inserted, there is no longer any order.

> But when selecting/updating records, I want the same order of records
> should be updated/selected.
> "Rows in tables have NO logical order in a relational database"
> I think, True for hash(#) and permanent table.

Well, obviously you have some operation that does not give you the
desired result, and you posted an UPDATE statement, which is a little
funny, because all you do is to assign a variable.

I suggest that you follow the standard recommendation and post:

o CREATE TABLE statement for your table(s)
o INSERT statements with sample data.
o The desired result given the sample.
o A short narrative of what ou are trying to achieve.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||UPDATEs are not ordered either. The result of your UPDATE statement is
undefined, unreliable and, in my view, not useful.

Please specify the whole problem rather than post fragments of your
non-working solution. The best way to specify the problem is to post
DDL, sample data and required end results. See:
http://www.aspfaq.com/etiquette.asp?id=5006

--
David Portas
SQL Server MVP
--