Subquery with GROUPING as a Table in SQL-Server 2005:

Subquery with GROUPING as a Table in SQL-Server 2005:

Subquery can be used as a derived table in FORM clause of the T-SQL query. Believe me this is really powerful approach and can bring you out of some difficult situation. I have already previously written some articles on Subquery, you can refer those for getting basic idea about subquery, correlated subquery,CTE etc.

Let us create two tables for demonstration and insert some data in it.

–Create first table

CREATE TABLE CricketerDetails

(

ID INT IDENTITY(1,1) CONSTRAINT pk_cricketID PRIMARY KEY NOT NULL,

Name VARCHAR(25) NOT NULL,

Country VARCHAR(10) NOT NULL

)

–Insert data into first table

INSERT INTO CricketerDetails (Name,Country) VALUES(‘Sachin Tendulkar’,‘India’)

INSERT INTO CricketerDetails (Name,Country) VALUES(‘Steve Waugh’,‘Australia’)

INSERT INTO CricketerDetails (Name,Country) VALUES(‘Saurav Ganguly’,‘India’)

INSERT INTO CricketerDetails (Name,Country) VALUES(‘Jaisurya’,‘Sri Lanka’)

–Create second table

CREATE TABLE PersonalScore

(

MatchName VARCHAR(15) NOT NULL,

Run INT NOT NULL,

CricketerID INT NOT NULL CONSTRAINT fk_cricketid REFERENCES CricketerDetails(id)

)

–Insert data into second table

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘LG cup’,100,1)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘LG cup’,10,2)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘LG cup’,17,3)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘LG cup’,0,4)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘WC-06’,10,1)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘WC-06’,99,2)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘WC-06’,137,3)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘WC-06’,10,4)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘WC-1998’,190,1)

After finishing above task, let us see how we can get cricketer name, country and his score with simple JOIN clause.

SELECT C.Name,c.country,MAX(p.Run)

FROM PersonalScore p join CricketerDetails c

ON p.cricketerid=c.id

GROUP BY C.name,c.country

We have achieved whatever we wanted. If you observed we have to put every column in group by clause which is not included in AGGREGATE function. In this case, those are Name and Country. What if you don’t want to put those two columns in group by clause as this is something messy sometime. Let us see how can achieve the same results with the help of derived subquery as table.

SELECT C.Name,c.country,p.Run

FROM CricketerDetails c

JOIN

(

SELECT Cricketerid,MAX(run) as Run FROM PersonalScore GROUP BY CricketerID

) p

ON c.ID=p.CricketerID

See, we have used subqery in JOIN clause rather than any table and achieved the same results. This technique of relational algebra may prove very powerful sometime in difficult situation.

Reference: Ritesh Shah

Complete ref of SQL-Server Join, Inner Join, Left Outer Join, Right Outer Join, Full Outer Join, in SQL-Server 2005:

Complete ref of SQL-Server Join, Inner Join, Left Outer Join, Right Outer Join, Full Outer Join, in SQL-Server 2005:
Merging data is heart of SQL and it shows the depth of relational algebra as well as the power and flexibility of SQL. SQL is excellent at selecting the data and SQL Developer can be master by understanding the relational algebra.
Join seems known technology but it is fun all time. Developer can make very creative query with joins by making data twist and shout. Pulling the data out of join could be the answer of very big code of programming language with iteration. It is much faster than any loop in your code. I promise you will have fun by learning this powerful technique.
SQL-Server 2005 is relational database so you always need to join more than one table in order to get complete information out of data tables. Since, tables are self explanatory itself, order of table doesn’t matter.
Here are the types of available joins in SQL-Server 2005:
Note: AdventureWorks database is going to be used throughout in this chapter.
Inner Join: It’s a very common and frequently needed join. Common intersection will be pulled out in this kind of join. It means, it will compare the result sets of first table with result sets of second table based on common fields or based on primary key and foreign key relationship. Below given is an example of Inner Join.
SELECT Sales.SalesPerson.SalesPersonID,
Sales.SalesPersonQuotaHistory.SalesQuota
FROM Sales.SalesPerson
INNER JOIN
Sales.SalesPersonQuotaHistory
ON
Sales.SalesPerson.SalesPersonID=Sales.SalesPersonQuotaHistory.SalesPersonID
You can get join two table with SalesPersonID in above case as both have common field.
(Part of Outer Join) Left Outer Join: Include all rows from the left side table no matter whether matching rows are exists in right side table or not. It means that Outer join is an extended version of Inner Join as it not only gives your common intersection but it do gives non matching data from left side or right side along with intersection.
SELECT HumanResources.Employee.EmployeeID,HumanResources.Employee.NationalIDNumber,
HumanResources.EmployeeAddress.AddressID
FROM HumanResources.Employee Left Outer Join HumanResources.EmployeeAddress
ON
HumanResources.Employee.EmployeeID=HumanResources.EmployeeAddress.EmployeeID
Employee table is joined with EmployeeAddress table based on EmployeeID. Left out join has been used so it will list all employees, no matter where it meets associated entry in EmployeeAddress.
(Part of Outer Join) Right Outer Join: Include all rows from the right side table no matter whether matching rows are exists in left side table or not. It means that Outer join is an extended version of Inner Join as it not only gives your common intersection but it do gives non matching data from left side or right side along with intersection.
SELECT HumanResources.Employee.EmployeeID,HumanResources.Employee.NationalIDNumber,
HumanResources.EmployeeAddress.AddressID
FROM HumanResources.Employee Right Outer Join HumanResources.EmployeeAddress
ON
HumanResources.Employee.EmployeeID=HumanResources.EmployeeAddress.EmployeeID
Employee table is joined with EmployeeAddress table based on EmployeeID. Right outer join has been used so it will list all employees’ Address, no matter where it meets associated entry in EmployeeAddress.
(Part of Outer Join) Full Outer Join: Includes all rows from both tables regardless of match exists. It means it a union distinct of Left Outer Join and Right Outer Join.
SELECT HumanResources.Employee.EmployeeID,HumanResources.Employee.NationalIDNumber,
HumanResources.EmployeeAddress.AddressID
FROM HumanResources.Employee Full Outer Join HumanResources.EmployeeAddress
ON
HumanResources.Employee.EmployeeID=HumanResources.EmployeeAddress.EmployeeID
Self Join: A self-join refers back to itself. You can use this type of unary relationship for recursive purpose. Like employee table to find boss.
–creating table for self join
create table emp
(
ID int Identity(1,1),
Name Varchar(15),
BossID int
)
GO
–Inserting some records in above table.
Insert Into emp
SELECT ‘DM’,0 UNION ALL
SELECT ‘MA’,1 UNION ALL
SELECT ‘UC’,2 UNION ALL
SELECT ‘LU’,2
GO
–equi join which will find boss name for every employee, DM is a main boss in this case.
SELECT e.ID,e.Name,ep.Name as ‘boss name’
FROM emp e JOIN emp ep
on
e.bossid=ep.id
GO
There are few more types of join like theta join, equi join, natural join etc. which I will explain in coming article.
Reference: Ritesh Shah