AndreaNobili AndreaNobili - 2 years ago 60
MySQL Question

What is the best solution to select from 3 tables bound together?

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:



SourceId BugTraqID





VulnerabilitySolution COLUMNS:



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:



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.


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

Answer Source

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

    @c c
    inner join @a a on c.ida =
    inner join @b b on c.ida =
    where c.ida = 1  -- here you just comment and check the logic
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download