www.webdeveloper.com
Results 1 to 6 of 6

Thread: how to combine data from multiple tables into one master table using sql

  1. #1
    Join Date
    Sep 2013
    Posts
    4

    how to combine data from multiple tables into one master table using sql

    I have 3 tables (JM, JCD, and JEO). JM is the parent with PK JobNo. JCD and JEO are both children to JM with FK's JobNo.

    Below is the desired output table from the 3 tables I am writing the query for.



    Any ideas on what the code would be to achieve this?
    (Note the resultant table does not include any rows where JCD.CostType is equal to 90).

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,174
    Code:
    SELECT
      JCD.JobNo,
      JCD.CostType,
      JCD.Amount,
      JED.Estimate
    FROM JCD
    INNER JOIN JED USING(JobNo, CostType)
    ORDER BY JCD.JobNo, JCD.CostType
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Sep 2013
    Posts
    4
    Thanks for your reply however this did not work. First of all it keeps on giving me a syntax error for the USING command. Not sure if it is supported or not.

    I tried modify your code so I could run the query. Here's what I used:

    Code:
    SELECT
      JCD.JobNo,
      JCD.CostType,
      JCD.Amount,
      JED.Estimate
    FROM JCD
    INNER JOIN JED ON JCD.JobNo = JED.JobNo AND JCD.CostType = JEO.CostType
    ORDER BY JCD.JobNo, JCD.CostType
    It returned something like the following:

    OUTPUT
    JobNo CostType Amount Estimate
    13088 10 100 50
    13088 10 105 50
    13088 23 120 0
    13088 24 130 10
    13088 25 140 0
    13088 26 50 0
    13100 25 5 310


    I think the code should be something like this (although this doesn't work either) ... but could you modify my code to make it work for the output that I need?

    Code:
    SELECT JM.JobNo, JCD.CostType, JCD.Amount, JEO.Estimate
    FROM JM
    FULL OUTER JOIN JCD ON JM.JobNo = JCD.JobNo
    FULL OUTER JOIN JEO ON JM.JobNo = JEO.JobNo
    WHERE JCD.CostType != 90
    GROUP BY JM.JobNo, JCD.CostType, JCD.Amount, JEO.Estimate

  4. #4
    Join Date
    Sep 2013
    Posts
    4
    Thanks for the response however it did not work the way I want. The USING command kept on giving me syntax errors - not sure if its supported.

    I think the code should look something like this (even though this did not work either). Could you try modifying this? I need all entries from JEO and JCD (except when JCD.CostType=90) to be added into the resultant table.

    SELECT JM.JobNo, JCD.CostType, JCD.Amount, JEO.Estimate
    FROM JM
    FULL OUTER JOIN JCD ON JM.JobNo = JCD.JobNo
    FULL OUTER JOIN JEO ON JM.JobNo = JEO.JobNo
    WHERE JCD.CostType != 90
    GROUP BY JM.JobNo, JCD.CostType, JCD.Amount, JEO.Estimate

  5. #5
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,174
    I think I misunderstood what you wanted to do. Upon looking more closely at your desired results, maybe you just want a UNION of the two tables?
    Code:
    (SELECT JobNo, CostType, Amount, 0 AS Estimate FROM JCD WHERE CostType <> 90)
    UNION
    (SELECT JobNo, CostType, 0 AS Amount, Estimate FROM JED WHERE CostType <> 90)
    If getting the exact order you have in the example is important, that may be tricky, however.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  6. #6
    Join Date
    Sep 2013
    Posts
    4
    this worked. thank you.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles