CODEWITHSUNDEEP
sqlsql-servert-sqljoinsql-server-2012
CM2K
CM2K

Reputation: 883

SQL server - groupping by correctly in a join

I have two tables:

table 1:
rN        rnk           rid      rsn       rsi    tid  rd            r
abc_1m   xy_mfgabc1m_z   1      penta      1      27   2009-01-01    1.2
abc_1m   xy_mfgabc1m_z   1      penta      1      27   2009-01-02    0.3  
abc_3m   xy_mfgabc3m_z   2      penta      1      30   2009-01-01    0.6 
abc_3m   xy_mfgabc3m_z   2      penta      1      30   2009-01-02    0.4

table 1 has over 100.000 rows table 2 is smaller, @ 2000 rows.

table 2:     

     rN        rnk           rid      rsn       rsi    tid  rd            r
    tdf_1y   xy_mfgtdf1y_z   90      penta      1      94   2009-01-01    1.7
    tdf_1y   xy_mfgtdf1y_z   90      penta      1      94   2009-01-02    0.7  
    tdf_1y   xy_mfgtdf1y_z   90      penta      1      94   2009-01-01    0.2 
    tdf_1y   xy_mfgtdf1y_z   90      penta      1      94   2009-01-02    0.9

I want to have everything from table 1 and just append table 2 to the table 1. expected table:

table 3 :
rN        rnk           rid      rsn       rsi    tid  rd            r
abc_1m   xy_mfgabc1m_z   1      penta      1      27   2009-01-01    1.2
abc_1m   xy_mfgabc1m_z   1      penta      1      27   2009-01-02    0.3  
abc_3m   xy_mfgabc3m_z   2      penta      1      30   2009-01-01    0.6 
abc_3m   xy_mfgabc3m_z   2      penta      1      30   2009-01-02    0.4
tdf_1y   xy_mfgtdf1y_z   90     penta      1      94   2009-01-01    1.7
tdf_1y   xy_mfgtdf1y_z   90     penta      1      94   2009-01-02    0.7  
tdf_1y   xy_mfgtdf1y_z   90     penta      1      94   2009-01-01    0.2 
tdf_1y   xy_mfgtdf1y_z   90     penta     1      94   2009-01-02    0.9

If i want everything from the first table and everything from the second table, this should be a full outer join, right?

My problem is that i don't know how to group this by, because if i do something like this, I will have everything from table1 and nothing from table2

select one.* from table1
join table2 two on one.rsn = two.rsn
group by one.rN, one.rnk, one.rid, one.rsn, one.rsi, one.tid, one.rd, one.r
order by rid

and if I won't group them, then I will have millions of records, hence this I must group it somehow. I think I'm not using the groupping by correctly and maybe the columns i'm joining them on.

Any advice would be well received since this seems so simple yet can't see the solution. Thanks

edit:

Thank you all for your answers. You all gave me important information which I forgot about. I totally missed the fact that i could use union. That being said, I chose as an accepted answer the first one which posted (since more or less you all said the same thing - i upvoted all of you since every answer gave a insight of how I should be doing this, thank you again)

Upvotes: 2

Views: 48

Answers (3)

dcieslak
dcieslak

Reputation: 2715

Please use UNION/UNION ALL operator. Because your tables have exactly the same column names and the same order of columns then you can simply do

select * from

for both of them.

If you want to remove duplication and sort result then use UNION. If you don't have duplication (or you don't care) then use UNION ALL as it is faster.

select * from table1 
union all 
select * from table2

Upvotes: 2

Anthony Grist
Anthony Grist

Reputation: 38345

If i want everything from the first table and everything from the second table, this should be a full outer join, right?

Based on your description of the problem, and your example "table 3", no. You don't want a JOIN at all, because there's no relationship between the rows in the two tables. What you want is a UNION, because you want a new result set that's just all the rows from table1 plus all the rows from table2.

So (assuming they have their columns in the same order):

SELECT * FROM table1
UNION
SELECT * FROM table2

Because of the nature of UNION, it will do essentially what your GROUP BY seems to be intended to do: remove any duplicate rows.

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

Just use the UNION operator:

SELECT t1.* FROM table1 t1
UNION ALL
SELECT t2.* FROM table2 t2

A JOIN is not the way to go because that would leave your result set with new columns corresponding to both table1 and table2. As a rule of thumb, when you want to stack 2 tables together you can use UNION and when you want to bring in new columns, or refine existing columns, you can use a JOIN.

Upvotes: 1

Related Questions