SQL Server Nested Queries on multiple tables

Question

I have a database with tables GridData, FormList and FormData.

I need to pull a field called Value from the FormData table, but cannot figure out how to structure the query. I can run it one-by-one and it gives me the result, but need to include it together in a table output.

The query, when I run it step-by-step is:

Select * from GridData where GridName = 'uwg' -- returns:

FormId      GridName        Example Field
1244135     uwg             9
1244135     uwg             10
1244135     uwg             11
1244135     uwg             7
1244135     uwg             66

Using that FormID field, I lookup the FormList table

Select * from FormList where FormID = 1244135 -- returns:

JobId       FormName        FormId
1241899     ExampleForm     1244135

Then querying the SAME FormList table where FormID = JobID from above..

Select * from FormList where FormID = 1241899 -- returns:

JobId       FormName        FormId
1241894     ExampleForm2    1241899

AGAIN.. querying the SAME FormList table where FormID = JobID from above..

Select * from FormList where FormID = 1241894 -- returns:

JobId       FormName        FormId
1241893     ExampleForm3    1241894

The JobID from this result is the ID I need to reference in the FormData table to return the value I need...

Select Value from FormData where FormID = 1241893 and Name = 'ProductName' -- returns:

Value
12345

My goal here is to be able to return all the values from the GridData table, with the corresponding entry in the FormData "Value" field.

I'm really struggling with how to write this query!

I've tried the following, but it returns multiple duplicate entries. The Value field contains multiple data types, and I'm only interested on the value from this table where FormData.Name = 'ProductName'

SELECT	Value,
        RecordId,
	GridData.FormId,
	GridName,
	ExampleField
      
  FROM	GridData

  JOIN	FormData

  ON	GridData.FormId = FormData.FormID
   
  WHERE GridData.FormId IN

  (SELECT FormList.FormId FROM FormList WHERE FormList.FormId IN 
  (SELECT FormList.FormId FROM FormList WHERE FormList.FormId IN 
  (SELECT FormList.FormId FROM FormList WHERE FormList.FormId IN 
  (SELECT FormData.FormID FROM FormData))))

  AND FormData.DataItemName = 'JobProductName'

Based on your description the sub-SELECTs of FormList should select the JobID, not the FormId. Also, put the AND clause in the parentheses with the FormData-SELECT) — TToni
– TToni, Commented May 16, 2017 at 18:37
Sounds like you want to use a recursive query. I also don't understand why you aren't including the FormData.Name = 'ProductName' in your query unless you just aren't syncing your explanation with your description. — Anthony Hancock
– Anthony Hancock, Commented May 16, 2017 at 18:48
@AnthonyHancock I'm trying to filter the return on the Value to where Name is 'JobProductName' - I'm probably not doing it correctly tho. When I ran without it returned multiple (duplicate records). — boseytal
– boseytal, Commented May 16, 2017 at 19:48

Muhammad Awais · Accepted Answer · 2017-05-16 18:51:27Z

1

SELECT  Value, RecordId,GridData.FormId,GridName,ExampleField
FROM GridData
Join FormList  On GridData.FormID = FormList.FormID
Join FormList A On FormList.JobID = A.FormID
JOIN FormData On A.JobID = FormData.FormID
Where FormData.DataItemName = 'JobProductName';

answered May 16, 2017 at 18:51

Muhammad Awais

261 silver badge1 bronze badge

Sign up to request clarification or add additional context in comments.

4 Comments

boseytal Over a year ago

This worked, however I get an error after about 50,000 rows with **"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."" It's worth mentioning I'm using 3 columns as an example, there's approx 80.

boseytal Over a year ago

Also worth noting expected row returns of 2,980,000

Muhammad Awais Over a year ago

Are you using Subqueries in Select or where caluse of the above mentioned query ? When subquery is used in select clause it should return only 1 record. To make sure use Top 1, but this can lease to unesired results. If used in where clause, dont compare with =,< operates that expect single value. Instead use In operator.

boseytal Over a year ago

This helped a lot but was missing a level of depth.

boseytal · Accepted Answer · 2017-05-23 09:33:31Z

1

The query that I managed to extract the data with was:

Select  FD.Value, G.*
FROM    GridData G

JOIN FormList FL1 ON FL1.FormID = G.FormID
JOIN FormList FL2 ON FL1.JobID = FL2.FormID
JOIN FormList FL3 ON FL2.JobId = FL3.FormID
JOIN FormData FD ON FL3.JobId = FD.FormID

WHERE FD.DataItemName = 'ProductNameHere' and G.GridName = 'GridNameHere'

The posts below helped in getting to this conclusion - thank you all.

answered May 23, 2017 at 9:33

boseytal

431 silver badge4 bronze badges

Comments

RegularNormalDayGuy · Accepted Answer · 2017-05-17 12:45:06Z

0

SELECT        GD.*
              ,FD.value
    FROM      GridData GD
LEFT JOIN     FormList FL
           ON GD.FormID = FL.FormID
LEFT JOIN FormList FL1
           ON FL.JobID = FL1.FormID
LEFT JOIN FormData FD
           ON FL1,JobID = FD.FormID
    WHERE GD.GridName = 'uwg'
      AND FormData.DataItemName = 'JobProductName'

edited May 17, 2017 at 12:45

answered May 16, 2017 at 18:56

RegularNormalDayGuy

7351 gold badge9 silver badges29 bronze badges

4 Comments

boseytal Over a year ago

Thanks - but this returns only one field.

RegularNormalDayGuy Over a year ago

Oups, mea culpa. I corrected with the columns you wanted

boseytal Over a year ago

I ran this and it does return the row, it also return duplicates as the filter on FormData.DataItemName = 'JobProductName' wasn't there. As per above, if I run the query with no filters, it only returns 50,000 of expected 2.9m - but with above, I received an error, I didn't here..

RegularNormalDayGuy Over a year ago

I added the filter to my answer. You should try the query on a smaller part of your DB (which you'll know for a fact which rows are to be returned) and check if it works.

KtX2SkD · Accepted Answer · 2017-05-17 13:18:59Z

Could you please try running the query below, level by level?

Level one is GridData alone (lines 14-16), next level is (11-17), and then repeat until the top. This way if things go wrong, you can check when do they do. Either that or you reach the top problem-free :)

Maybe it can be rewritten better, but let us at least get a working version for now.

SELECT [Value] -- 12345
FROM FormData
WHERE [Name] = 'ProductName'
  AND FormID IN (
    SELECT JobId -- 1241893
    FROM FormList
    WHERE FormID IN (
        SELECT JobId -- 1241894
        FROM FormList
        WHERE FormID IN (
            SELECT JobId -- 1241899
            FROM FormList
            WHERE FormID IN (
                SELECT FormId -- 1244135
                FROM GridData
                WHERE GridName = 'uwg'
            )
        )
    )
)

Collectives™ on Stack Overflow

SQL Server Nested Queries on multiple tables

4 Answers 4

4 Comments

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related