combining two tables.

  • soldout6000

    Ten Centuries

    Points: 1051

    I obtaining information from one main table to retrieve employees and spouses, and display the fields in my report. I'm counting employees and spouses based on different scenarios or grouping. I'm having trouble coming tb_emp and tb_spouse. I'm getting cartesian product with the following join.

    Please advise me as to how can I combine two mini tables. Thanks in advance!

    With tb_main as
    (
    SELECT CLIENT_NO, AMOUNT, ACTIVITY_TYPE, PERSON_ID, EMP_SPOUSE_NUMBER
    FROM table_main_db
    WHERE EMPLOYER_NUMBER = 1234
    ),

    tb_emp as
    (SELECT e.CLIENT_NO, e.AMOUNT, e.EMP_SPOUSE_NUMBER,
    COUNT(e.CLIENT_NO) OVER (PARTITION BY e.CLIENT_NO) emp_group,
    SUM(e.AMOUNT) OVER (PARTITION BY e.CLIENT_NO) emp_amt_group,
    COUNT(DISTINCT e.ACTIVITY_TYPE) OVER (PARTITION BY e.CLIENT_NUMBER) emp_act_group
    FROM tb_main e
    WHERE PERSON_ID = 11 -- id for employees
    ),

    tb_spouse as
    (SELECT s.CLIENT_NO, s.AMOUNT, s.EMP_SPOUSE_NUMBER,
    COUNT(s.CLIENT_NO) OVER (PARTITION BY s.CLIENT_NO) spouse_group,
    SUM(s.AMOUNT) OVER (PARTITION BY s.CLIENT_NO) spouse_amt_group,
    COUNT(DISTINCT s.ACTIVITY_TYPE) OVER (PARTITION BY s.CLIENT_NO) spouse_act_group
    FROM tb_main s
    WHERE PERSON_ID = 99 -- id for spouse
    ),

    SELECT e.CLIENT_NO, e.AMOUNT, e.emp_group, e.emp_amt_group,
    s.CLIENT_NO, s.AMOUNT, s.spouse_group, s.spouse_amt_group
    FROM tb_emp e
    LEFT OUTER JOIN tb_spouse s ON (e.EMP_SPOUSE_NUMBER = s.EMP_SPOUSE_NUMBER)

  • J Livingston SQL

    SSC Guru

    Points: 51272

    https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Gordon Barclay

    SSC Eights!

    Points: 922

    J Livingston SQL - Saturday, December 16, 2017 10:43 AM

    That's really going to help isn't it!

    Gordon Barclay

  • Lynn Pettis

    SSC Guru

    Points: 442160

    Gordon Barclay - Friday, July 20, 2018 7:52 AM

    J Livingston SQL - Saturday, December 16, 2017 10:43 AM

    That's really going to help isn't it!

    Well, he should have also said that we need DDL (CREATE TABLE statement) for the table(s) involved, sample data (as INSERT INTO statements) for the table(s) involved that is representative of the problem domain (in other words, not production data), and finally the expected results based on the provided sample data.  All of the SQL code provided should also be tested in an empty sandbox database to ensure all works prior to posting.

  • J Livingston SQL

    SSC Guru

    Points: 51272

    Gordon Barclay - Friday, July 20, 2018 7:52 AM

    J Livingston SQL - Saturday, December 16, 2017 10:43 AM

    That's really going to help isn't it!

    maybe you can explain why the link does not help ?
    it provides all the necessary prerequisites to post a question that is most likely to get accurate answers quickly.
    I posted the link because I thought it helpful........

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Lynn Pettis

    SSC Guru

    Points: 442160

    J Livingston SQL - Friday, July 20, 2018 8:05 AM

    Gordon Barclay - Friday, July 20, 2018 7:52 AM

    J Livingston SQL - Saturday, December 16, 2017 10:43 AM

    That's really going to help isn't it!

    maybe you can explain why the link does not help ?
    it provides all the necessary prerequisites to post a question that is most likely to get accurate answers quickly.
    I posted the link because I thought it helpful........

    I think it would have been more helpful with an explanation of why to read it and follow the directions.  Others coming on to this thread my wonder the meaning as well thinking it would answer the question.

  • Gordon Barclay

    SSC Eights!

    Points: 922

    Lynn Pettis - Friday, July 20, 2018 8:09 AM

    J Livingston SQL - Friday, July 20, 2018 8:05 AM

    Gordon Barclay - Friday, July 20, 2018 7:52 AM

    J Livingston SQL - Saturday, December 16, 2017 10:43 AM

    That's really going to help isn't it!

    maybe you can explain why the link does not help ?
    it provides all the necessary prerequisites to post a question that is most likely to get accurate answers quickly.
    I posted the link because I thought it helpful........

    I think it would have been more helpful with an explanation of why to read it and follow the directions.  Others coming on to this thread my wonder the meaning as well thinking it would answer the question.

    Not sure why you're asking when you know full well what I mean?

    Gordon Barclay

  • J Livingston SQL

    SSC Guru

    Points: 51272

    Gordon Barclay - Friday, July 20, 2018 8:35 AM

    Lynn Pettis - Friday, July 20, 2018 8:09 AM

    J Livingston SQL - Friday, July 20, 2018 8:05 AM

    Gordon Barclay - Friday, July 20, 2018 7:52 AM

    J Livingston SQL - Saturday, December 16, 2017 10:43 AM

    That's really going to help isn't it!

    maybe you can explain why the link does not help ?
    it provides all the necessary prerequisites to post a question that is most likely to get accurate answers quickly.
    I posted the link because I thought it helpful........

    I think it would have been more helpful with an explanation of why to read it and follow the directions.  Others coming on to this thread my wonder the meaning as well thinking it would answer the question.

    Not sure why you're asking when you know full well what I mean?

    Not sure why you posted as you did...the thread is 7 months old ?  
    Are you looking for a solution that is similar to the OP's ?

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply