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?

Handling merge replication foreign key conflicts

I have SQL CE clients replicating against a SQL Server 2005 db using merge replication. The DB has a table A and a table B, which has a foreign key to table A. It is common in my application for records in table A to be deleted on the server. I'm running into issues when a table A record has been deleted, but table B records were created on the clients which point to that record. When I sync I get a conflict because the table B records cannot be applied at the server, and the table A delete cannot be applied at the client.

What I would like to happen is to have the table B records on the client be deleted by the merge process, and to create a log of the event. I've looked into creating a business logic handler to do this, but I'm not sure what type of conflict this is (UpdateDeleteConflict or otherwise), and I'm not sure that deleting the table B records is something I can do in the business logic handler.

This seems like it would be a common problem in merge replication. I'm not locked into using a custom business logic handler at all. Any suggestions are welcome.

Thanks.
Any ideas on this?|||

presumably you have a CASCADE DELETE to trash the child ("table B") whenever you delete the parent ("tableA") rows

- whether DRI or by trigger

if you were using transactional replication the delete child-then-parent would be part of same atomic transaction

- thus maybe you would get both or neither.

possibly using transactional repl with immediate updating would avoid updates to such ghosts.

however using merge repl [yes I recognise its benefits!] will inevitably have to cope with unconstrained race conditions across the fabric. therefore you would have to cope with this situation

seems that you could

1. write custom resolvers, or

2. delve into the guts of the sprocs that actually execute the I/U/D statements [or use insteadof triggers]

3. simply add extra IsDeleted flag on each tableA,tableB table for soft-delete [actually do hard-delete at quiet time]

HTH

Dick

Handling merge replication foreign key conflicts

I have SQL CE clients replicating against a SQL Server 2005 db using merge replication. The DB has a table A and a table B, which has a foreign key to table A. It is common in my application for records in table A to be deleted on the server. I'm running into issues when a table A record has been deleted, but table B records were created on the clients which point to that record. When I sync I get a conflict because the table B records cannot be applied at the server, and the table A delete cannot be applied at the client.

What I would like to happen is to have the table B records on the client be deleted by the merge process, and to create a log of the event. I've looked into creating a business logic handler to do this, but I'm not sure what type of conflict this is (UpdateDeleteConflict or otherwise), and I'm not sure that deleting the table B records is something I can do in the business logic handler.

This seems like it would be a common problem in merge replication. I'm not locked into using a custom business logic handler at all. Any suggestions are welcome.

Thanks.
Any ideas on this?|||

presumably you have a CASCADE DELETE to trash the child ("table B") whenever you delete the parent ("tableA") rows

- whether DRI or by trigger

if you were using transactional replication the delete child-then-parent would be part of same atomic transaction

- thus maybe you would get both or neither.

possibly using transactional repl with immediate updating would avoid updates to such ghosts.

however using merge repl [yes I recognise its benefits!] will inevitably have to cope with unconstrained race conditions across the fabric. therefore you would have to cope with this situation

seems that you could

1. write custom resolvers, or

2. delve into the guts of the sprocs that actually execute the I/U/D statements [or use insteadof triggers]

3. simply add extra IsDeleted flag on each tableA,tableB table for soft-delete [actually do hard-delete at quiet time]

HTH

Dick

Handling merge replication foreign key conflicts

I have SQL CE clients replicating against a SQL Server 2005 db using merge replication. The DB has a table A and a table B, which has a foreign key to table A. It is common in my application for records in table A to be deleted on the server. I'm running into issues when a table A record has been deleted, but table B records were created on the clients which point to that record. When I sync I get a conflict because the table B records cannot be applied at the server, and the table A delete cannot be applied at the client.

What I would like to happen is to have the table B records on the client be deleted by the merge process, and to create a log of the event. I've looked into creating a business logic handler to do this, but I'm not sure what type of conflict this is (UpdateDeleteConflict or otherwise), and I'm not sure that deleting the table B records is something I can do in the business logic handler.

This seems like it would be a common problem in merge replication. I'm not locked into using a custom business logic handler at all. Any suggestions are welcome.

Thanks.
Any ideas on this?|||

presumably you have a CASCADE DELETE to trash the child ("table B") whenever you delete the parent ("tableA") rows

- whether DRI or by trigger

if you were using transactional replication the delete child-then-parent would be part of same atomic transaction

- thus maybe you would get both or neither.

possibly using transactional repl with immediate updating would avoid updates to such ghosts.

however using merge repl [yes I recognise its benefits!] will inevitably have to cope with unconstrained race conditions across the fabric. therefore you would have to cope with this situation

seems that you could

1. write custom resolvers, or

2. delve into the guts of the sprocs that actually execute the I/U/D statements [or use insteadof triggers]

3. simply add extra IsDeleted flag on each tableA,tableB table for soft-delete [actually do hard-delete at quiet time]

HTH

Dick

handling foreign characters

I have a database that will be accepting foreign characters from different
languages, as in Germany, Russia, Japanese, Chinese and so on. I was
wondering if there was anything different you need to set up on the database
for this to work properly, as I am currently working in a database and any
characters from Japanese and Chinese show up as ? marks. Any help would be
appreciated, thanks.
J. DThis might actually be a genuine case to use the unicode datatype. You do us
e
it, don't you?
Where do those special characters "show up as ? marks"? If they are
displayed correctly in the appropriate client application, then I guess
you're done. :)
ML|||JD wrote:
> I have a database that will be accepting foreign characters from
> different languages, as in Germany, Russia, Japanese, Chinese and so
> on. I was wondering if there was anything different you need to set
> up on the database for this to work properly, as I am currently
> working in a database and any characters from Japanese and Chinese
> show up as ? marks. Any help would be appreciated, thanks.
As ML said, you need to use unicode data types (nchar, nvarchar, and
ntext) in order to store 2-byte characters requires by some languages.
In addition, the client application that retrieves the data must use
unicode variables (this should be automatic in most programming
languages). Then you need to make sure the objects used in the
application to display the unicode data support unicode and the font
that is used is unicode supported. Fonts like Tahoma are OpenType fonts,
meaning they have Font Fallback which links to fonts that support East
Asian script characters if run on those versions of Windows. You can
always use the Arial Unicode font, which is a 24MB font that
encapsulates most all languages for testing.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Handling autogenerated PK/FK conflicts in merge replication

We use autogenerated primary keys in most of our tables. Some of these keys are also foreign keys in other tables. Right now there is only 1 database sever at a central location. But now there is a need to have multiple database servers at different locations. Data from these remote sites needs to be replicated to the central server. Some data would also distribute from central server to selected remote sites.

If I could resdesign, I would have choosen something like GUIDs for the primary keys or combination of something like ServerName and AutoGenerated number as a combined key. But that's not possible right now. How do I handle merge replication conflicts in this case?

I am looking for some pointers as to how to handle this case. If it were just simple table with 1 primary key, that would be easy as I can throw the primary key on remote server and let the central server create a new key when data is inserted. But in my case, a single table can be related to 5 or more other tables through these autogenerated keys. Any help is much appreciated.

GUID would be easiest however you can also make a composite key with ID and hostname. That way the key will always be unique.

Would not recommend conflict resolver for the primary key, you will end up with a mess eventually i think.

Martin

|||

you can also use identity ranges for subscribers so they don't conflict when they upload back to the publisher server.

Handling autogenerated PK/FK conflicts in merge replication

We use autogenerated primary keys in most of our tables. Some of these keys are also foreign keys in other tables. Right now there is only 1 database sever at a central location. But now there is a need to have multiple database servers at different locations. Data from these remote sites needs to be replicated to the central server. Some data would also distribute from central server to selected remote sites.

If I could resdesign, I would have choosen something like GUIDs for the primary keys or combination of something like ServerName and AutoGenerated number as a combined key. But that's not possible right now. How do I handle merge replication conflicts in this case?

I am looking for some pointers as to how to handle this case. If it were just simple table with 1 primary key, that would be easy as I can throw the primary key on remote server and let the central server create a new key when data is inserted. But in my case, a single table can be related to 5 or more other tables through these autogenerated keys. Any help is much appreciated.

GUID would be easiest however you can also make a composite key with ID and hostname. That way the key will always be unique.

Would not recommend conflict resolver for the primary key, you will end up with a mess eventually i think.

Martin

|||

you can also use identity ranges for subscribers so they don't conflict when they upload back to the publisher server.