SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


combining two tables.


combining two tables.

Author
Message
soldout6000
soldout6000
SSChasing Mays
SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)

Group: General Forum Members
Points: 645 Visits: 146
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
J Livingston SQL
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45318 Visits: 41777
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
Gordon Barclay
SSChasing Mays
SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)

Group: General Forum Members
Points: 618 Visits: 275
That's really going to help isn't it!

Gordon Barclay
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (391K reputation)SSC Guru (391K reputation)SSC Guru (391K reputation)SSC Guru (391K reputation)SSC Guru (391K reputation)SSC Guru (391K reputation)SSC Guru (391K reputation)SSC Guru (391K reputation)

Group: General Forum Members
Points: 391461 Visits: 42832

Gordon Barclay - Friday, July 20, 2018 7:52 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.



Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
J Livingston SQL
J Livingston SQL
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45318 Visits: 41777
Gordon Barclay - Friday, July 20, 2018 7:52 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
Lynn Pettis
SSC Guru
SSC Guru (391K reputation)SSC Guru (391K reputation)SSC Guru (391K reputation)SSC Guru (391K reputation)SSC Guru (391K reputation)SSC Guru (391K reputation)SSC Guru (391K reputation)SSC Guru (391K reputation)

Group: General Forum Members
Points: 391461 Visits: 42832
J Livingston SQL - Friday, July 20, 2018 8:05 AM
Gordon Barclay - Friday, July 20, 2018 7:52 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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Gordon Barclay
Gordon Barclay
SSChasing Mays
SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)

Group: General Forum Members
Points: 618 Visits: 275
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
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
J Livingston SQL
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45318 Visits: 41777
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
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search