Solved

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

Posted on 2004-09-01
10
1,537 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 6

Accepted Solution

by:
lausz earned 250 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

679 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