I have a SQL database with the main table called Results. This table stores a record of results of tests that are run nightly.
The Results table has many fields but for arguments say lets just say for now it looks like this:
What I wanted to be able to record was a list of tags used in the tests that were run. The tags may be different for each result and an array of them are stored.
I created a junction table as shown below called Tags:
So what im looking to do is to link these 2 together. I'm not so great with databases ill be honest.
I was thinking that when I save the test results with my normal SQL query immediately after I would loop through each tag and save the tags to this new table but maybe i'm wrong here?
Psuedocode:
//Returned from previous SQL statement that inserted results values into the DB
int ResultID = SQLQueryReturnValue;
Foreach TAG in TAGS
{
string SQLQuery = "INSERT INTO TAGS (ResultID, ScenarioTag, FeatureTag)(@ResultID, @ScenarioTag, @FeatureTag)";
CmdSql.Parameters.AddWithValue("@ResultID", ResultID);
CmdSql.Parameters.AddWithValue("@ScenarioTag", TAG.Scenario);
CmdSql.Parameters.AddWithValue("@FeatureTag", TAG.Feature);
CmdSql.CommandText = SQLQuery;
CmdSql.Execute();
}
Heres an example of what each table might actually look like:
Results Table
|ResultID | Result |
| 10032 | Pass |
| 10031 | Fail |
| 10030 | Fail |
Tags Table
| TagID | ResultID | ScenarioTag | FeatureTag |
| 6 | 10032 | Cheque | Trading |
| 5 | 10032 | GBP | Sales |
| 4 | 10031 | Direct Credit | Trading |
| 3 | 10031 | GBP | Purchase |
| 2 | 10030 | Wire | Dividends |
| 1 | 10030 | USD | Payments |
So finally onto my question...Is there a way that I can physically link this new "Tags" table to my results table. Its informally linked in a way using the ResultID but theres no physical link.
Is it this you're looking for? (Assumption: This query is looking from results. They do not necessarily have to have Tags...)
SELECT *
FROM Results
LEFT JOIN Tags ON Results.ResultID=Tags.ResultID
EDIT: Maybe I did not understand, what you mean by "physically". You could add a foreign key constraint:
ALTER TABLE Tags ADD CONSTRAINT FK_Tags_Results FOREIGN KEY (ResultID) REFERENCES Results(ResultID);
This constraint adds a relation to these tables, making sure, that only values existing in Results are allowed in Tags as "ResultID". On the other hand you cannot delete a Result row with existing children in Tags...
If you do this you could alter the top query to:
SELECT *
FROM Tags
INNER JOIN Results ON Results.ResultID=Tags.ResultID
Now you are looking from Tags (leading table) and you know, that each tag must have a ResultID (INNER JOIN).
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments