CODEWITHSUNDEEP
c#mysqlsql
rreeves
rreeves

Reputation: 2458

Creating Database Relationship programmatically

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!

Upvotes: 0

Views: 494

Answers (1)

JustBeingHelpful
JustBeingHelpful

Reputation: 18980

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]

Upvotes: 2

Related Questions