9

I am trying to inner join these 2 subqueries (I think that's what it's called anyways) together where the branchName of the first query is equal to the branchName of the second query.

However, they don't seem to want to join together, and with my limited knowledge of SQL I can't seem to find a way to fix this. I tried moving the brackets around in all sorts of positions but it didn't like that either.

SELECT * 
FROM
(
SELECT B.branchName, A.type, AVG (T.amount), COUNT(A.accNumber)
FROM Branch B, Account A, Transactions T
WHERE 
    B.branchNumber = A.branchNumber AND
    A.accNumber = T.accNumber
GROUP BY B.branchName, A.type
)

INNER JOIN

(
SELECT B1.branchName, COUNT(A1.accNumber)
FROM Account A1, Branch B1
WHERE 
      A1.branchNumber = B1.branchNumber 
GROUP BY B1.branchName
HAVING COUNT(A1.accNumber) > 5
)
ON
 B.branchName = B1.branchName
HaveNoDisplayName
  • 8,291
  • 106
  • 37
  • 47
user4914034
  • 105
  • 1
  • 1
  • 4

2 Answers2

20

In order to make the query work, you need to name the inner selects (sub-queries):

SELECT * 
FROM
(
SELECT B.branchName, A.type, AVG (T.amount), COUNT(A.accNumber)
FROM Branch B, Account A, Transactions T
WHERE 
    B.branchNumber = A.branchNumber AND
    A.accNumber = T.accNumber
GROUP BY B.branchName, A.type
) q1

INNER JOIN

(
SELECT B1.branchName, COUNT(A1.accNumber)
FROM Account A1, Branch B1
WHERE 
      A1.branchNumber = B1.branchNumber 
GROUP BY B1.branchName
HAVING COUNT(A1.accNumber) > 5
) q2
ON
 q1.branchName = q2.branchName
Amit
  • 45,440
  • 9
  • 78
  • 110
  • Second suggestion would give a different result? The original says "each (branchName) group with more than 5 accNumber", your suggested query says "each (branchname, **type**) group with more than 5 accNumber". – Andomar Nov 09 '15 at 06:49
  • @Andomar - Ha! you're right! I was so confused by the *unreadable* query :-) – Amit Nov 09 '15 at 06:50
  • Haha sorry for making it look confusing xD, just starting out with SQL. But thank you so much!!! I had no idea you had to name the inner selects like that. – user4914034 Nov 09 '15 at 06:54
  • Oh actually I see it now...it's just like how I've been naming my other tables Branch B, Account A, and Transactions T, I gotta name the subqueries like that as well :D – user4914034 Nov 09 '15 at 07:01
1

You should set up name column for each sub-query. Try this:

SELECT * 
FROM
(
   SELECT B.branchName, A.type, AVG (T.amount) AS [AVG], COUNT(A.accNumber) AS [COUNT]
   FROM Branch B, Account A, Transactions T
   WHERE 
       B.branchNumber = A.branchNumber AND
       A.accNumber = T.accNumber
   GROUP BY B.branchName, A.type
) AS T1   
INNER JOIN   
(
   SELECT B1.branchName, COUNT(A1.accNumber) AS [COUNT]
   FROM Account A1, Branch B1
   WHERE 
      A1.branchNumber = B1.branchNumber 
   GROUP BY B1.branchName
   HAVING COUNT(A1.accNumber) > 5
) AS T2
ON
 T1.branchName = T2.branchName
Nguyễn Hải Triều
  • 1,454
  • 1
  • 8
  • 14