Creating and joining multiple queries (more than 2)

Question

Creating and joining multiple queries (more than 2)

Shabbaranks

SSC Eights!

Points: 880
More actions
December 31, 2020 at 1:23 pm
Go to Answer
#3825880
Hi,
I know how to create and join 2 queries but on occasion Ive needed to join more than 2 and Im unsure how to achieve this? The method I use for joining to is as per the example below - can you just extend this as required?
```
Select * from
(
 SELECT Stuff.Example1
Stuff.Example2
Stuff.Example3
Stuff.Example4
 FROM   Table1) As FirstSet
 INNER JOIN
 (
  SELECT This.Example5
 This.Example6
 This.Example7
 This.Example8
 FROM   Table2) As SecondSet
 On FirstSet.Example1 = SecondSet.Example5
```
And also when grouping is there a quick way to group rather than having to write out all the column names which are required when you group?
Thanks
Phil Parkin

SSC Guru

Points: 246960
More actions
December 31, 2020 at 1:30 pm
Answer
#3825883
Just extend what you are already doing
```
Select * from
(
 SELECT Stuff.Example1
Stuff.Example2
Stuff.Example3
Stuff.Example4
 FROM   Table1) As FirstSet
 INNER JOIN
 (
  SELECT This.Example5
 This.Example6
 This.Example7
 This.Example8
 FROM   Table2) As SecondSet
 On FirstSet.Example1 = SecondSet.Example5
INNER JOIN
 (
  SELECT This.Example9
 This.Example10
 This.Example11
 This.Example12
 FROM   Table3) As ThirdSet
 On FirstSet.Example1 = ThirdSet.Example9
--Obviously, care needs to be taken with the joins to avoid unwanted row duplication or elimination.
```
As far as grouping is concerned, you need to write out all the column names explicitly.
Or if you have SQL Prompt, it will automatically detect the non-aggregated columns and fill them in for you.
- This reply was modified 3 years, 10 months ago by Phil Parkin.
- This reply was modified 3 years, 10 months ago by Phil Parkin.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Shabbaranks SSC Eights! Points: 880 More actions · Answer 1

Thanks for the clarity on this - I think when I tried to extend my test query I put the On's one after another rather than at the end of the additional query.