Creating Database Relationship programmatically

Question

I have two separate databases that have data that coincides with another. However they are not relational.

I'm trying pro-grammatically create a relationship to build some statistics in C#. I'm looking for the number of actions in a case and its assets associated.

From one database I can see what asset belongs to what case:

| 7AU45448 | cases/unchanged/
| 7AI61361 | cases/unchanged/
| 8C52A5A1 | cases/unchanged/
| 8643Y053 | cases/unchanged/
| 8643Y052 | cases/unchanged/
| 8643Y051 | cases/unchanged/
| 8643Y050 | cases/unchanged/
| B4F043RB | cases/ups01/
| B4F043R7 | cases/ups01/
| B4F043R5 | cases/ups01/
| B4F043QZ | cases/ups01/
| B4F043QY | cases/ups01/
| B4F043RA | cases/ups01/
| B4F043R1 | cases/ups01/
| B4F043R8 | cases/ups01/
| B4F043R9 | cases/ups01/
| B4F043QX | cases/ups01/
| B4F043R3 | cases/ups01/
| B4F043QW | cases/ups01/
| B4F043R4 | cases/ups01/
| B4F043RC | cases/ups01/
| B4F043R2 | cases/ups01/
| B4F043R0 | cases/ups01/
| B4F043RD | cases/ups01/
| B4F043R6 | cases/ups01/

The other database is for logs, and holds no information on the case itself. Only the asset and detail are inside.

The information in this database is like:

7AU45448 | Processed file
7AU45448 | Download file
7AU45448 | View file

I can easily do a action count per asset on the database but not on the case. This is why I need the relationship.

If anyone has and Ideas or suggestions please let me know!

Thanks in Advance!

Sorry, could you clarify what is being asked. I can't quite gather what it is you want, and why MySQL, SQL, WPF, and MVVM are applicable. — McKay
– McKay, Commented Jan 16, 2012 at 22:45
its just the stuff that I'm using, I want to figure out the count of actions per case. I tried to put in bold what i need — rreeves
– rreeves, Commented Jan 16, 2012 at 22:46
Yes, but it's a big leap from random data to a business rule. — McKay
– McKay, Commented Jan 16, 2012 at 22:50
Also, your usage of the term "relation" is unclear, which definition do you mean? — McKay
– McKay, Commented Jan 16, 2012 at 22:51
Do you really have completely separate databases, or are these separate tables in the same database? — Joel C
– Joel C, Commented Jan 16, 2012 at 22:56

onedaywhen · Accepted Answer · 2012-01-17 09:37:45Z

2

Since your definition of "not relational" was merely meant to be "without constraints", you should be able to compare data in two different databases as long as the field you're joining on is the same data type. Just make sure your left table is the table with the values you care about if you use a LEFT OUTER JOIN. In this case, [db1].[dbo].[table1] is the left table.

Example:

SELECT [db1].[dbo].[table1].*, [db2].[dbo].[table2].*
FROM [db1].[dbo].[table1]
LEFT OUTER JOIN [db2].[dbo].[table2] ON [db1].[dbo].[table1].[field_in_db1_table1] = [db2].[dbo].[table2].[field_in_db2_table2]

edited Jan 17, 2012 at 9:37

onedaywhen

57.4k12 gold badges103 silver badges142 bronze badges

answered Jan 16, 2012 at 23:36

JustBeingHelpful

19.1k39 gold badges169 silver badges254 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

rreeves Over a year ago

cool thanks, that sounds about right. I guess I misunderstood relations. Is this the MySQL syntax need ?

JustBeingHelpful Over a year ago

No, this is T-SQL for SQL Server. I don't know MySQL very well, but let me Google it. Should be similar.

JustBeingHelpful Over a year ago

seems to be the same: stackoverflow.com/questions/4897954/…

Collectives™ on Stack Overflow

Creating Database Relationship programmatically

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related