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
Solved

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

Posted on 2004-09-01
10
1,533 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

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…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

792 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