Join Query with three tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Join Query with three tables

Author

Topic

afvcr
Starting Member

3 Posts

Posted - 2013-08-30 : 09:47:49

I hope this is the right fórum to post. I need to make a query to extract data from three tables.

The tables are:

Customer

CREATE TABLE [Customer] (
[CustomerCode] VARCHAR(20)  PRIMARY KEY NULL,
[CustomerName] VARCHAR(60)  NULL,
[CustomerNif] VARCHAR(10)  NULL,
[CustomerAddr] VARCHAR(120)  NULL,
[CustomerZipCode] VARCHAR(40)  NULL,
[CustomerCity] VARCHAR(20)  NULL,
[CustomerState] VARCHAR(20)  NULL,
[CustomerCountry] VARCHAR(4)  NULL,
[CustomerPhone1] VARCHAR(20)  NULL,
[CustomerPhone2] VARCHAR(20)  NULL,
[CustomerPhone3] VARCHAR(20)  NULL,
[CustomerFax] VARCHAR(20)  NULL,
[CustomerEmail] VARCHAR(40)  NULL,
[CustomerWebSite] VARCHAR(40)  NULL,
[CustomerContact1] VARCHAR(40)  NULL,
[CustomerContact1Position] VARCHAR(40)  NULL,
[CustomerContact1Phone] VARCHAR(20)  NULL,
[CustomerContact1BusinessEmail] VARCHAR(60)  NULL,
[CustomerContact1PrivateEmail] VARCHAR(60)  NULL,
[CustomerContact2] VARCHAR(40)  NULL,
[CustomerContact2Position] VARCHAR(40)  NULL,
[CustomerContact2Phone] VARCHAR(20)  NULL,
[CustomerContact2BusinessEmail] VARCHAR(60)  NULL,
[CustomerContact2PrivateEmail] VARCHAR(60)  NULL
)

SupportHistory

CREATE TABLE [SupportHistory] (
[IncidentID] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
[IncidentCustomerCode] NUMERIC  NULL,
[IncidentCustomerContact] VARCHAR(20)  NULL,
[IncidentDesc] VARCHAR(1024)  NULL,
[IncidentOpenedBy] INTEGER  NULL,
[IncidentTechInCharge] INTEGER  NULL
)

and SupportIncidentHistory

CREATE TABLE [SupportIncidentHistory] (
[IncidentID] INTEGER  PRIMARY KEY NOT NULL,
[IncidentChangeDate] VARCHAR(10)  NULL,
[IncidentChangeTo] VARCHAR(1)  NULL,
[IncidentReasonToChange] VARCHAR(40)  NULL,
[IncidentComents] VARCHAR(256)  NULL
)

The Fields I'm Interested on are:

From Customer Table:
CustomerName

From SupportHistory Table:
IncidentDesc
IncidentTechInCharge

From SupportIncidentHistory Table:
IncidentChangeTo
IncidentChangeDate

I wrote this SQL Query based in several tutorial on the net:

"SELECT c.CustomerName, sh.IncidentDesc, sh.IncidentTechInCharge, sih.IncidentChangeTo, sih.IncidentChangeDate FROM Customer c INNER JOIN SupportHistory sh ON c.CustomerCode = 'sh.IncidentCustomerCode' INNER JOIN SupportIncidentHistory sih ON sh.IncidentID = sih.IncidentID"

but it does not return any results. Can someone help me with the query?

Thank You

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2013-08-30 : 10:44:36

SELECT c.CustomerName, sh.IncidentDesc, sh.IncidentTechInCharge, sih.IncidentChangeTo, sih.IncidentChangeDate
FROM Customer c
INNER JOIN SupportHistory sh
ON c.CustomerCode = sh.IncidentCustomerCode
INNER JOIN SupportIncidentHistory sih
ON sh.IncidentID = sih.IncidentID

To create a auto increment column the synntax is
[IncidentID] INTEGER PRIMARY KEY NOT NULL Identity(1,1),

mohammad.javeed.ahmed@gmail.com

afvcr
Starting Member

3 Posts

Posted - 2013-08-30 : 11:25:59

Thanks. It works. And because of that I found an error. I need to add another table:

SupportTechs

CREATE TABLE [SupportTechs] (
[SupportTechID] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
[SupportTechName] VARCHAR(40)  NULL,
[SupportTechNickName] VARCHAR(20)  NULL,
[SupportTechPhone1] VARCHAR(15)  NULL,
[SupportTechPhone2] VARCHAR(15)  NULL,
[SupportTechAddr] VARCHAR(60)  NULL,
[SupportTechCity] VARCHAR(20)  NULL,
[SupportTechState] VARCHAR(20)  NULL
)

and in place of IncidentTechInCharge, which is just the ID of the tech, I need to get SupportTechNickName from the SupportTechs table. I was trying to make the change by myself with no success. Can You please help me with the change? Many, many Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-31 : 04:48:17

it would again be a simple join.
see


SELECT c.CustomerName, sh.IncidentDesc, st.SupportTechName AS TechInCharge, sih.IncidentChangeTo, sih.IncidentChangeDate 
FROM Customer c 
INNER JOIN SupportHistory sh 
ON c.CustomerCode = sh.IncidentCustomerCode
INNER JOIN SupportIncidentHistory sih 
ON sh.IncidentID = sih.IncidentID
INNER JOIN SupportTechs st
ON st.SupportTechID = sh.IncidentTechInCharge

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

afvcr
Starting Member

3 Posts

Posted - 2013-08-31 : 06:23:10

Thank You. While I was waiting I could change your Query, which works for me:

    
          SELECT sh.IncidentID, 
          c.CustomerName, 
          sh.IncidentDesc, 
          st.SupportTechNickName, 
          sih.IncidentChangeTo, 
          sih.IncidentChangeDate 
          FROM Customer c 
          INNER JOIN SupportHistory sh
          ON c.CustomerCode = sh.IncidentCustomerCode
          INNER JOIN SupportIncidentHistory sih
          ON sh.IncidentID = sih.IncidentID
          INNER JOIN SupportTechs st
          ON st.SupportTechID = sh.IncidentTechInCharge

ThankYou Very much. I think I understood it now.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-02 : 09:35:50

Cool..Glad that you sorted it out

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources