I am pretty new in DB and I have the following situation: I have 2 main tables named VulnerabilityAlertDocument and VulnerabilitySolution.
The columns of these 2 tables are something like this:
VulnerabilityAlertDocument COLUMNS:
Id
VulnerabilityAlertId
SourceId BugTraqID
Title
..........
..........
..........
VulnerabilitySolution COLUMNS:
Id
Description
These 2 tables are related togheter by a many to many relation implemented by a third table named VulnerabilityAlertDocument_VulnerabilitySolution in which every row bound togheter a row of VulnerabilityAlertDocument with a row of VulnerabilitySolution (using the ids of these tables)
VulnerabilityAlertDocument_VulnerabilitySolution COLUMNS:
VulnerabilityAlertDocumentId
VulnerabilitySolutionId
Now my problem is: given the id of a VulnerabilityAlertDocument row I have to obtain all the related VulnerabilitySolution rows
So I have found two possible solution:
1) I use a JOIN between VulnerabilityAlertDocument_VulnerabilitySolution and VulnerabilitySolution table and I select using the VulnerabilityAlertDocument row id.
or
2) First I perform a query like this:
select * VulnerabilityAlertDocument_VulnerabilitySolution where VulnerabilityAlertDocumentID = 3
Thene I take the related value of VulnerabilitySolutionId of every obtained rows and I perform query on these
What is better? I think the solution that use the JOIN operator but I am not so sure
Choose 1, Go with inner join. Check this example. Inner join easily find your data.
declare @a table (id int, name varchar(50))
declare @b table (id int, name varchar(50))
declare @c table (ida int, idb int)
insert into @a values(1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e')
insert into @b values(1,'x'),(2,'y'),(3,'z')
insert into @c values(1,1),(1,2),(2,1),(2,3),(3,1),(3,2),(3,3)
declare @value int = 1
select
a.name, b.name
from
@c c
inner join @a a on c.ida = a.id
inner join @b b on c.ida = b.id
where c.ida = 1 -- here you just comment and check the logic