0

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!

onedaywhen
  • 55,269
  • 12
  • 100
  • 138
rreeves
  • 2,408
  • 6
  • 39
  • 53
  • 2
    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 Jan 16 '12 at 22:45
  • 1
    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 Jan 16 '12 at 22:46
  • 1
    Yes, but it's a big leap from random data to a business rule. – McKay Jan 16 '12 at 22:50
  • 1
    Also, your usage of the term "relation" is unclear, which definition do you mean? – McKay Jan 16 '12 at 22:51
  • 1
    Do you really have completely separate databases, or are these separate tables in the same database? – Joel C Jan 16 '12 at 22:56
  • 1
    Are you saying one db is sql, the other mysql and you want a join between them? How many records are we talking to query from? – Tony Hopkinson Jan 16 '12 at 22:59
  • yes they are in two separate databases, there is millions of records. I mean relation as... if they were in 2 separate tables and the assetname column was relational i could query across it for the case details count. – rreeves Jan 16 '12 at 23:29

1 Answers1

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]
onedaywhen
  • 55,269
  • 12
  • 100
  • 138
JustBeingHelpful
  • 18,332
  • 38
  • 160
  • 245