Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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,556 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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.

722 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