Solved

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

Posted on 2004-09-01
10
1,523 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
 
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
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

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

863 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now