How do I join these 2 tables to make a 3rd table?

Question

Post reply

How do I join these 2 tables to make a 3rd table?

William Zabob

SSC Veteran

Points: 272
More actions
December 23, 2021 at 9:08 pm
Go to Answer
#3966837
- This topic was modified 2 years, 11 months ago by William Zabob.

December 24, 2021 at 5:05 pm

This was removed by the editor as SPAM
December 28, 2021 at 10:50 pm

This was removed by the editor as SPAM

Viewing 11 posts - 1 through 10 (of 10 total)

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

ratbak Ten Centuries Points: 1379 More actions · Answer 1

This is an unfortunately redundant design. Table2 appears to inherently contain everything in Table1 except TeamId.

You could insert using union all, one select per position --something like:

SELECT Table1.PlayerId,
       Table1.TeamId,
       'LF',
       SUM(Glf)
FROM Table1
INNER JOIN Table2 ON Table2.PlayerId = Table1.PlayerId
WHERE Table1.Pos = 'OF' 
GROUP BY Table1.PlayerId,
         Table1.TeamId
UNION ALL
SELECT Table1.PlayerId,
       Table1.TeamId,
       'CF',
       SUM(Gcf)
FROM Table1
INNER JOIN Table2 ON Table2.PlayerId = Table1.PlayerId
WHERE Table1.Pos = 'OF' 
GROUP BY Table1.PlayerId,
         Table1.TeamId
UNION ALL
SELECT Table1.PlayerId,
        TeamId,
        'RF',
        SUM(Grf)
FROM Table1
INNER JOIN Table2 ON Table2.PlayerId = Table1.PlayerId
WHERE Table1.Pos = 'OF' 
GROUP BY Table1.PlayerId,
         Table1.TeamId;

The where clause is included based on the assumption that Table1 contains infield positions as well as outfield positions.

Please provide create table scripts & insert scripts for sample data so that those trying to help you can reproduce the problem you want to solve.

How to post code problems

William Zabob SSC Veteran Points: 272 More actions · Answer 2

Thanks, the query works great, except 'yearid' isn't retrieved and I also need that in Table3 to show what year they played LF for 112 games, etc.

This reply was modified 2 years, 11 months ago by William Zabob.

ratbak Ten Centuries Points: 1379 More actions · Answer 3

So add Table1.YearId to each of the 3 select statements and group by statements.

SELECT Table1.PlayerId,
       Table1.TeamId,
       Table1.YearId,
       'LF',
       SUM(Glf)
FROM Table1
INNER JOIN Table2 ON Table2.PlayerId = Table1.PlayerId
WHERE Table1.Pos = 'OF' 
GROUP BY Table1.PlayerId,
         Table1.YearId,
         Table1.TeamId
UNION ALL
SELECT Table1.PlayerId,
       Table1.TeamId,
       Table1.YearId,
       'CF',
       SUM(Gcf)
FROM Table1
INNER JOIN Table2 ON Table2.PlayerId = Table1.PlayerId
WHERE Table1.Pos = 'OF' 
GROUP BY Table1.PlayerId,
         Table1.YearId,
         Table1.TeamId
UNION ALL
SELECT Table1.PlayerId,
        Table1.TeamId,
        Table1.YearId,
        'RF',
        SUM(Grf)
FROM Table1
INNER JOIN Table2 ON Table2.PlayerId = Table1.PlayerId
WHERE Table1.Pos = 'OF' 
GROUP BY Table1.PlayerId,
         Table1.TeamId,
         Table1.YearId;

William Zabob SSC Veteran Points: 272 More actions · Answer 4

I re-posted the picture of the "schema" in my first post. I found and corrected a "MyBad"!

I am getting some blanks for teamid in Table3 after the merge, and when I query the first 2 tables:

select count(*) from table1 where teamid is null;

select count(*) from table2 where teamid is null;

I get 0 (zero) records returned, which tells me that both tables have no missing teamid in them!

Please have a go at this and see if we can get it working properly. THANKS!

ratbak Ten Centuries Points: 1379 More actions · Answer 5

Please have a go at providing scripts instead of pictures, as previously requested.

Please provide create table scripts & insert scripts for sample data so that those trying to help you can reproduce the problem you want to solve.

How to post code problems

I believe the script worked fine for the schema and sample data you showed in a picture (which I laboriously translated into useable scripts to reproduce the problem & provide the query I did... I won't do that again). Sample data should be representative of a variety of scenarios, and not just one best case, to enable detecting & adjusting for such issues early. If you have data that is not working, then you should include that in insert scripts so that you can test.

I recommend using temp tables for testing (prepend table names with #) so that you can experiment w/o risk of polluting the real data. I recommend preceding your create table statements w/ DROP TABLE IF EXISTS statements so that you can repeat tests easily.

Based on your analysis, what do you believe is the problem?

Michael L John One Orange Chip Points: 27056 More actions · Answer 6

William Zabob wrote:

aw... am I not following the rules? WTF man!
Doing it with create scripts and DDL and all the bullshit you want would take way too much time! I'm NOT a SQL Server anything! I'm just trying to get my job done while everybody is gone for the holidays. This thing could be solved just by looking at what I've provided.
If it's such a "redundant design", then why is the solution not working? I'm getting back duplicate records (a.k.a. "redundant records"), and some records w/o teamid in them! Both Table1 AND Table2 have no missing teamid.
If you don't know how to solve this simple JOIN (UNION) the correct way, then just say so!
The "schema" is in my first post. If someone wants to help without bitching about it, then fine!
This is like going to the hospital with COVID-19 and listening to everyone on the staff bitch at you for not getting your vaccine! OR going to the dentist with a toothache and listening to the dentist harping "You don't brush you teeth 3 times a day!" What's done is done! It is what it is. And, in both of these cases, I'M PAYING TO HEAR THIS B.S.... STOP ALREADY!
I suppose you want me to wear a mask too, because you are.
"When coloring, stay in the line, kids... they are our friend, and ONLY do it this way!"
What the hell happened to flexibility and adjusting to what you've been given?

Guess what? You lost.

Someone is asking for more information, and someone is asking YOU to do some work for YOUR problem. We can't work with nothing. You cannot query a picture. This is a free site. With free advice from some very skilled people. You should be saying thank you.

Your tables are garbage. Your data is garbage. If it was so simple, then why can't you figure it out?

And your attitude is worse. Do your own work. We are volunteers who will not put up with your attitude.

This reply was modified 2 years, 11 months ago by Michael L John.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Mike01 SSChampion Points: 11395 More actions · Answer 7

This is the best line, I'll make sure I skip over any of your questions. Glad you got it 'resolved'

Doing it with create scripts and DDL and all the bullshit you want would take way too much time!

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Michael L John One Orange Chip Points: 27056 More actions · Answer 8

Mike01 wrote:

This is the best line, I'll make sure I skip over any of your questions. Glad you got it 'resolved'
Doing it with create scripts and DDL and all the bullshit you want would take way too much time!

Well, considering that his last post 17 years ago was asking questions about a primary key, it appears that he hasn't learned that sometimes you need to do some work yourself.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/