Converting a Subquery into a Join

[f9073341]

Hi guys,

The below SQL statement returns a requirement name and the SUM of the number of number of tests run in each test related to that requirement for Release 10. To filter on a particular release, I need to convert this statement into an equivalent INNER JOIN because 'Tests_run' isn't the only attribute for a test I need to return.

Please help! Here's the current SQL subquery statement.

SELECT Requirement.Name
(SELECT SUM(Test_Status.Run)
FROM Test_Status, Test_Lookup, Test_Script
WHERE Test_Lookup.Test=Test_Scripts.ID
AND Test_Status.Test=Test_Script.Test_Script_Test
AND Test_Lookup.Requirement=Requirement.ID
AND Test_Status.Release=10) AS Tests_Run,
FROM Requirement


Thanks very much.

 

[vbaInet]

Create a query to return the other values you would want to return and include it in this query joining via the ID.

 

[f9073341]

OK, I've managed to do the join by the following code. However... now the problem I'm getting is that because it is a join over 3 tables... if I add any more joins, it multiples the result by 3!!!

Please help!! Here's the SQL:

SELECT Requirement.Name,
SUM(Script_Status.Run) AS Test_Variations
FROM ((((Requirement
INNER JOIN Test_Lookup ON Test_Lookup.Requirement=Requirement.ID)
INNER JOIN Test_Script ON Test_Script.ID=Test_Lookup.Test)
INNER JOIN Script_Status ON Script_Status.Test=Test_Script.Test_Script_Test)

Many Thanks

 

[vbaInet]

Use the Distinct predicate to get rid of duplicates:

http://www.techonthenet.com/sql/distinct.php

 

[f9073341]

Thanks again for your reply. I've tried that and it still gives me multiplied sum results. Could it be something to do with incorrect grouping? Or maybe logical grouping?

This is where I start to get multiplied results:

SELECT Requirement.Name,
SUM(Script_Status.Run) AS Test_Variations
FROM ((((Requirement
INNER JOIN Test_Lookup ON Test_Lookup.Requirement=Requirement.ID)
INNER JOIN Test_Script ON Test_Script.ID=Test_Lookup.Test)
INNER JOIN Script_Status ON Script_Status.Test=Test_Script.Test_Script_Test)
LEFT JOIN Area ON Requirement.ID=Area.Name)
LEFT JOIN Component ON Requirement.ID=Component.Name

 

[vbaInet]

Well, in the first place what you did wasn't what I explained in my first post. Explain what you mean by, "because 'Tests_run' isn't the only attribute for a test I need to return".

 

[f9073341]

Oh... I think I get you now. You mean create another query and use the results of it?

By that, I meant columns from the same Script_Status table such as 'Passed', 'Failed' etc. But I figure, I can get that column successfully, then the others are just included in the SELECT statement.

 

[vbaInet]

Yes that's what I mean. Then Join via the ID so it will only pull related records but still show the count field and your other fields.

 

[f9073341]

OK, how would that work when trying to filter results programmatically in VBA? E.g, I need this feature because filtering on release is currently determined from the user interface as the user selects it.

Thanks for your time.

 

[vbaInet]

How would it work in what way? Explain what you mean.

You copy the sql from what you've just created. Set criteria on the relevant fields.

 

[f9073341]

Well, currently, once I've created an SQL statement that I'm happy with, I copy and paste the expression into the VBA editor and ensure the syntax is compilable. When a user makes a filter selection and hits "go", I take the variables from the selection choices in the interface and use the variables in my VBA syntax to capture those choices, then the SQL is executed and the results displayed to the user.

What I'm saying is that if I have two separate queries to get a result (rather than a single query), how do I change the values of the first query to capture in my overall query in VBA code?

Apologies for my ignorance, I just haven't done it this way before!

 

[vbaInet]

Just as you would do with any inner join,

WHERE QueryName.FieldName = "whatever"  OR TableName.FieldName = "something else"

It will filter accordingly.

 

[f9073341]

Is this what you mean? I have two queries below... one which returns the test information and one which incorporates that test information with other information (without multiplying any aggregate SUM results). Would this work?


SELECT Requirement.Name, SUM(Script_Status.Run) AS Test_Variations
FROM ((((Requirement
INNER JOIN Test_Lookup ON Test_Lookup.Requirement=Requirement.ID)
INNER JOIN Test_Script ON Test_Script.ID=Test_Lookup.Test)
INNER JOIN Script_Status ON Script_Status.Test=Test_Script.Test_Script_Test)


SELECT Requirement.Name, qry1.Test_Variations,
Area.Name, Component.Name
FROM (Requirement
LEFT JOIN Area ON Requirement.ID=Area.Name)
LEFT JOIN Component ON Requirement.ID=Component.Name

Thanks

 

[vbaInet]

I thought you said you had other fields from the Script_Status table you would like to include which was the main aim of your exercise? I don't see those fields.

It looks like you've renamed Test_Script to Script_Status??? Could you explain what's going on here.

 

[f9073341]

Yes, that's right. I haven't included them in this post because there are alot of them! But other fields include 'pass', 'fail' etc. But they are all within the same table. I haven't renamed the names of the tables... Test_Script holds the script name, and Test_Status holds whether that particular test passed or not.

How would I use the results of these two separate queries?

Thanks

 

[vbaInet]

Just to clarify, in your post #7 you said the Script_Status table has the passed, failed fields. Was that just a typo? But now you make no mention of it here.

 

[f9073341]

Sorry yes... I'm getting kinda flustered with this problem - Script_Status is the name.

 

[f9073341]

Nevermind... I solved it anyway.

By using a subquery in my JOIN criteria, along with a GROUP BY, I was able to perform the query I wanted whilst keeping the SUM features logically separated - resulting in no unwanted multiplication.

E.g.

Select MainQuery.Field_A
FROM MainQuery
INNER JOIN(SELECT Table_A.Field_A, SUM(Table_D.Field_D) AS Field_D
WHERE etc...
GROUP BY Table_A.Field_A) AS Tests ON MainQuery.Field_A = Tests.Field_A

Add as many joins as you like

 

[vbaInet]

Glad you got it figured out. That was what I was getting at in my first post.

 

[f9073341]

Yes, thanks for your help. With the other fields and joins added, the query is HUUUUUUGE. Kinda satisfying