Business Logic with JOIN – Multiple Join examples in SQL-Server 2005:

Business Logic with JOIN – Multiple Join examples in SQL-Server 2005:


I wrote my previous article about different type of JOIN in SQL-Server 2005. Due to the space constraint in that article, complex example has not been given there, only few simple examples. I felt to explode this topic further by giving solution to some really needed business logic.


If you want to create report for blood testing laboratory, who used to check samples of employee of different companies for different purposes. Following should be the table structure, we will be creating three table. One will store information about Order received from different companies. Another one will be the detail of that order, means which company sent how much samples of how much employee?? And the last one is further more details about how much test should be performs on which sample???


Let us create three tables.


–CREATING FIRST ORDER TABLE


USE [AdventureWorks]


GO


/****** Object: Table [dbo].[Orders] Script Date: 03/08/2009 12:24:39 ******/


SET ANSI_NULLS ON


GO


SET QUOTED_IDENTIFIER ON


GO


SET ANSI_PADDING ON


GO


CREATE TABLE [dbo].[Orders](


[OrderID] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,


[OrderDate] [datetime] NOT NULL,


[CompanyName] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,


PRIMARY KEY CLUSTERED


(


[OrderID] ASC


)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


) ON [PRIMARY]



GO


SET ANSI_PADDING ON


Now, child table of above.



USE [AdventureWorks]


GO


/****** Object: Table [dbo].[OrderDetails] Script Date: 03/08/2009 12:37:27 ******/


SET ANSI_NULLS ON


GO


SET QUOTED_IDENTIFIER ON


GO


SET ANSI_PADDING ON


GO


CREATE TABLE [dbo].[OrderDetails](


[OrderID] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,


[SampleNumber] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,


[SampleOfEmployee] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,


[SampleReceivedDate] [datetime] NULL,


PRIMARY KEY CLUSTERED


(


[SampleNumber] ASC


)


) ON [PRIMARY]



GO


SET ANSI_PADDING ON


GO


ALTER TABLE [dbo].[OrderDetails] WITH CHECK ADD CONSTRAINT [fk_orderid] FOREIGN KEY([OrderID])


REFERENCES [dbo].[Orders] ([OrderID])


GO


ALTER TABLE [dbo].[OrderDetails] CHECK CONSTRAINT [fk_orderid]


One more child table.



USE [AdventureWorks]


GO


/****** Object: Table [dbo].[sampledetails] Script Date: 03/08/2009 12:43:00 ******/


SET ANSI_NULLS ON


GO


SET QUOTED_IDENTIFIER ON


GO


SET ANSI_PADDING ON


GO


CREATE TABLE [dbo].[sampledetails](


[SampleNumber] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,


[TestType] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,



PRIMARY KEY CLUSTERED


(


[SampleNumber] ASC,


[TestType] ASC


)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


) ON [PRIMARY]



GO


SET ANSI_PADDING ON


Now, this is time to insert few records on above tables.


–Received three order from two company


INSERT INTO Orders


SELECT ‘L1000’,‘2009-03-01’,‘Test Inc.’ UNION ALL


SELECT ‘L1001’,‘2009-03-02’,‘Zeel Inc.’ UNION ALL


SELECT ‘L1002’,‘2009-03-04’,‘ABC Inc.’



–received two sample for first order, one sample for second order


–sample for third order yet to receive


INSERT INTO OrderDetails


SELECT ‘L1000’,‘L1000-01’,‘James’,‘2009-03-01’ UNION ALL


SELECT ‘L1000’,‘L1000-02’,‘John’,‘2009-04-01’ UNION ALL


SELECT ‘L1001’,‘L1001-01’,‘Smita’,‘2009-03-05’



–details of sample, which test suppose to be performed


–on which sample


INSERT INTO SampleDetails


SELECT ‘L1000-01’,‘Cancer’ UNION ALL


SELECT ‘L1000-01’,‘AIDS’ UNION ALL


SELECT ‘L1000-02’,‘BP’ UNION ALL


SELECT ‘L1001-01’,‘AIDS’ UNION ALL


SELECT ‘L1001-01’,‘Cancer’


Well, now we are ready to pull data out of three tables with different type of JOIN which we have seen in my previous article.


NOTE: many different logic can be used to perform following tasks, I am giving this just to explain the use of JOIN otherwise there are some more facility which can handle all of the below situation other than join.


If you want to get OrderID, OrderDate, Company (who gave the order??), SampleNumber, EmployeeName (for which employee company has sent the sample??), TestType (which test to be performed in above tables?)


You can use following query which has used one LEFT OUTER JOIN and one INNER JOIN.


SELECT O.OrderID,O.OrderDate,O.CompanyName,


OD.SampleofEmployee,OD.SampleNumber,SD.TestType


FROM


ORDERS AS O


LEFT OUTER JOIN


ORDERDETAILS AS OD


ON


O.OrderID=OD.OrderID


INNER JOIN


SampleDetails AS SD


ON


OD.SampleNumber=SD.SampleNumber



If there is a need to find how many samples came from which company. Use following query with aggregate function COUNT.



SELECT


O.CompanyName,Count(SampleNumber) as ‘TOTAL’


FROM


ORDERS AS O


LEFT OUTER JOIN


ORDERDETAILS AS OD


ON


O.OrderID=OD.OrderID


GROUP BY O.CompanyName


ORDER BY TOTAL DESC


Reference: Ritesh Shah

Advertisements

4 Responses to “Business Logic with JOIN – Multiple Join examples in SQL-Server 2005:”

  1. Swarup Nayak Says:

    very good
    it’s help me to learn the things more.

  2. saranya Says:

    table -1 jobrole table-2 technology

    jobrole_id job_name tech_id tech_name
    1 xxxx 1 html
    2 yyyy 2 java

    table-3 jobpost

    job_id job role_id tech_id
    1 1 2
    2 1 1

    now i want to get job name & tech_name using join
    please reply if anyone know the query

  3. saranya Says:

    table-1 jobrole
    jobrole_id job_name
    1 xxxx
    2 yyyy

    table-2 technology
    tech_id tech_name
    1 html
    2 java
    table-3 jobpost

    job_id job role_id tech_id
    1 1 2
    2 1 1

    now i want to get job name & tech_name using join
    please reply if anyone know the query

    • riteshshah Says:

      select j.job_name, t.tech_name
      from jobpost as jp join jobrole as j
      on jp.jobrole_id=j.jobrole_id
      join technology as t
      on jp.tech_id=t.tech_id


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: