#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    5
    Rep Power
    0

    Join Query with three tables


    I hope this is the right forum. I need to make a query to extract data from three tables.

    The tables are:

    Customer

    Code:
    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

    Code:
    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

    Code:
    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 DataBase is a SQLite and 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:

    Code:
    "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 work. Can someone help me with the query?

    Thank You
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    here's your query reformatted for human beings --
    Code:
    SELECT c.CustomerName
         , sh.IncidentDesc
         , sh.IncidentTechInCharge
         , sih.IncidentChangeTo
         , sih.IncidentChangeDate 
      FROM Customer c 
    INNER 
      JOIN SupportHistoy sh 
        ON c.CustomerCode = 'sh.IncidentCustomerCode' 
    INNER 
      JOIN SupportIncidentHistory sih
        ON sh.IncidentID = sih.IncidentID
    i'm gonna guess that the "doesn't work" error message that you're getting is because in the first join, you're joining on a string

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    5
    Rep Power
    0
    Still "Doesn't work" = doesn't return any results...
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by afvcr
    Still "Doesn't work" = doesn't return any results...
    fix the join
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    5
    Rep Power
    0

    Angry


    If I knew how to do it, wouldn't be asking for help here
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by afvcr
    If I knew how to do it, wouldn't be asking for help here
    i told you

    usually, you join two tables based on matching values in a column from each table

    your first join attempts to match the values of a column in one table with a string (the characters inside the single quotes)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    5
    Rep Power
    0
    Why don't You show me? Or you just know the Theory, as I've read in innumerous posts in the net...
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by afvcr
    Why don't You show me?
    here it is right here --
    Code:
    ON c.CustomerCode = 'sh.IncidentCustomerCode'
    the thing on the left is a column name

    the thing on the right is a string

    i've now explained it three times, so if you still don't get what a string is, i suggest you remove the quotes and see what happens
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    5
    Rep Power
    0
    Has it so hard to say which was the problem in a direct language?
  18. #10
  19. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by afvcr
    Has it so hard to say which was the problem in a direct language?
    you're being awfully snooty for someone on the receiving end of free support
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo