Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MS-SQL: Combine table1 and table2 INTO table3 using combined SELECT statements with COUNT

Posted on 2004-09-01
10
Medium Priority
?
1,559 Views
Last Modified: 2007-12-19
I have two data tables that need combined and sync'ed together into another new table (table3). The SELECT statements work well with the UNION statement, however the result is not what I need.

Q. Is there another method to combine SELECT statement other than the UNION???

Heres my code so far:

SELECT DISTINCT table1.col1, table2.col2, COUNT(table1.col1)AS 'count1'
INTO table3
FROM table1, table2
where ltrim(table1.col1) = ltrim(table2.col1)
GROUP BY table1.col1, table2.col2
HAVING COUNT (*) >0 --ORDER BY count1 DESC
UNION
SELECT DISTINCT table1.col2, table2.col2, COUNT(table1.col2)AS 'count1'
INTO table3
FROM table1, table2
where ltrim(table1.col2) = ltrim(table2.col1)
GROUP BY table1.col2, table2.col2
HAVING COUNT (*) >0 ORDER BY count1 DESC

Data result is combined into two columns in table3 like so:

     table1.col1         table2.col2


However, I would like the result to be four columns in table3 like so:

     table1.col1         table2.col2          table1.col2          table2.col2


Both table1.col1 and table1.col2 need to independantly compare data to table2.col1. The desired
result should be as shown above, where the data of table1.col1 beside table2.col2 and table1.col2 beside
table2.col2. The data in both table1 and table2 need to correctly sync with the data in table2. Line by line....

Help...................
0
Comment
Question by:kvnsdr
  • 5
  • 3
  • 2
10 Comments
 
LVL 15

Expert Comment

by:mcmonap
ID: 11953226
Hi kvnsdr,

I think you want something like a cross join between the tables but a little sample data would help ie:

table1
col1    col2
1        3        
2        4

table2
col1    col2
5       7        
6       8

Than doing this:
SELECT *
FROM table1 CROSS JOIN table2

would give you this:
col1      col2      col1      col2
1      2      5      6
3      4      5      6
1      2      7      8
3      4      7      8
0
 
LVL 6

Expert Comment

by:lausz
ID: 11953243
Dis you try this ?

select c1, c2
INTO table3
from
(SELECT table1.col1, table2.col2, COUNT(table1.col1)AS 'count1'  , COUNT(table1.col2)AS 'count1'
FROM table1, table2
where ltrim(table1.col1) = ltrim(table2.col1)
GROUP BY table1.col1, table2.col2
HAVING COUNT (*) >0 ) t (col1, col2, c1,c2)
0
 
LVL 15

Expert Comment

by:mcmonap
ID: 11953296
The result set should have read:

col1      col2      col1      col2
1      3      5      7
2      4      5      7
1      3      6      8
2      4      6      8
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 6

Accepted Solution

by:
lausz earned 750 total points
ID: 11953410
change this...

select col1, col2 ,c1, c2
INTO table3
from
(SELECT table1.col1, table2.col2, COUNT(table1.col1)AS 'count1'  , COUNT(table1.col2)AS 'count1'
FROM table1, table2
where ltrim(table1.col1) = ltrim(table2.col1)
GROUP BY table1.col1, table2.col2
HAVING COUNT (*) >0 ) t (col1, col2, c1,c2)

If you want, post an numeric example to check the query.
0
 
LVL 1

Author Comment

by:kvnsdr
ID: 11955508
Looks like I need a one-way =

Q. How do I state a one-way equal???
0
 
LVL 6

Expert Comment

by:lausz
ID: 11955541
I don't understand your question, post a little example.
Thks
0
 
LVL 1

Author Comment

by:kvnsdr
ID: 11956185
The following script works almost perfectly, except the 'OR' operator causes double rows with incorrect data matches on alternate rows. Now if I remove the OR and use only one WHERE statement at a time, it works fine per WHERE statement. However I need both WHERE statements to make table3 usable.....

Using the 'first' WHERE statement:

     table1.col1         table2.col2          table1.col2          table2.col2        
           A                   right                       B                     wrong    
         
         
Using the 'second' WHERE statement:

     table1.col1         table2.col2          table1.col2          table2.col2
           A                   wrong                     B                     right    


Using the script below, the results are:

     table1.col1         table2.col2          table1.col2          table2.col2
           A                   wrong                     B                     right    
           A                    right                      B                     wrong  


Goal results are:

     table1.col1         table2.col2          table1.col2          table2.col2
           A                    right                     B                       right    
             

SELECT DISTINCT table1.col1, table2.col2, table1.col2, table2.col2, COUNT(table1.col2)AS count1
INTO table3
FROM table1 t1, table2 t2
where ltrim(t1.col1) = ltrim(t2.col1)
OR ltrim(t1.col2) = ltrim(t2.col1)
GROUP BY table1.col1, table2.col2, table1.col2, table2.col2
HAVING COUNT (*) >0
ORDER BY count1 DESC
0
 
LVL 6

Expert Comment

by:lausz
ID: 11956381


wHAT IT MEANS right , is a sum or what ?

if you have ...


table1 values ('A','B')
table2 values ('A','B')

What do you want to obtain ???
0
 
LVL 1

Author Comment

by:kvnsdr
ID: 11956487
I'm just matching data, no sums or calculations......

Both table1.col1 and table1.col2 need to independantly 'compare' data to table2.col1. The desired
result should be as shown above, where the data of table1.col1 is beside table2.col2 and table1.col2 is beside
table2.col2. The data in both table1 and table2 need to correctly sync and SELECT INTO table3, Line by line....


Goal results are:

     table1.col1         table2.col2          table1.col2          table2.col2
           A                    right                     B                       right    
0
 
LVL 6

Expert Comment

by:lausz
ID: 11956746
I think I can help you ..but ........

I want to see the data into the table1 and table2 ..... to understand your goal...

I am trying to reproduce your situation with this ...

create table #table1 (col1 varchar(5), col2 varchar(5))
create table #table2 (col1 varchar(5), col2 varchar(5))

insert into #table1 values ('A','B')
insert into #table2 values ('A','B')
insert into #table1 values ('B','A')
insert into #table2 values ('B','A')


Tell me what is the goal ..
thks

0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question