Solved

SQL extract without cursor

Posted on 2011-03-19
7
218 Views
Last Modified: 2012-05-11
I need to compare two tables as follows:

Table1:
ID1 menu1
ID1 menu2
ID1 menu3

ID2 Menu2
ID2 Menu10

ID3 Menu7
ID3 Menu5
ID3 Menu1

Table2:
Key1  Item1 Menu1
Key1  item1 Menu5

Key2 item1 menu1
Key2 item1 menu2
Key2 item1 menu3

Key3 item2 menu1
Key3 item2 menu2
Key3 item2 menu3

Key4  Item1 Menu1
Key4  item1 Menu5

Key5 item2 menu1
Key5 item2 menu2
 
Key6 item10 menu1
Key6 item10 menu2
Key6 item10 menu3
Key6 item10 menu7


I need to compare only one field 'MENU' no matter which keys they have, but if combination of menus in Table 2 is the same as in table 1 then some action s/b taken other wise ignore. In this sample only K3 in table one will trigger action.

I don't want to use cursor.  Thank you in advance for any suggestion.  
0
Comment
Question by:DDB
  • 3
  • 2
  • 2
7 Comments
 
LVL 17

Expert Comment

by:dbaSQL
ID: 35173007
>>In this sample only K3 in table one will trigger action.

There is no 'K3' in your Table1, only ID# and menu#.   Please clarify.
0
 
LVL 3

Author Comment

by:DDB
ID: 35173559
table2.K3 key records has the same values that table1.ID1. I think what I need is summary on varchar and group by in both tables.

thank you!
0
 
LVL 3

Author Comment

by:DDB
ID: 35173594
I think that code below should work for me with some minor changes, I will check it on Monday.
;with xxx As(
Select Id, Menu
from tbl1
group by key)

Select id, Column = stuff((Select ',' + cast(Menu as varchar(max)) from xxx t2 where t2.Id = t1.id
   for xml path(''),1,1,'')
from xxx t1
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 17

Expert Comment

by:dbaSQL
ID: 35173694
Sorry, I'm still not quite certain what you are trying to do.   I've created your tables, and matched the menu items across tables, regardless of the other key values.  But, it is unclear what you are trying to do from here.  Maybe on monday, let me know if there's anything i can do to help further


CREATE TABLE Table1 (idField char(5),menufield char(10))
INSERT Table1 (idfield,menufield)
SELECT 'ID1','menu1'
UNION
SELECT 'ID1','Menu2'
UNION
SELECT 'ID1','menu3'
UNION
SELECT 'ID2','Menu2'
UNION
SELECT 'ID2','Menu10'
UNION
SELECT 'ID3','Menu7'
UNION
SELECT 'ID3','Menu5'
UNION
SELECT 'ID3','Menu1'

CREATE TABLE Table2 (keyField char(5),itemField char(6),menuField char(10))
INSERT Table2 (keyField,itemField,menuField)
SELECT 'Key1','Item1','Menu1'
UNION
SELECT 'Key1','item1','Menu5'
UNION
SELECT 'Key2','item1','menu1'
UNION
SELECT 'Key2','item1','menu2'
UNION
SELECT 'Key2','item1','menu3'
UNION
SELECT 'Key3','item2','menu1'
UNION
SELECT 'Key3','item2','menu2'
UNION
SELECT 'Key3','item2','menu3'
UNION
SELECT 'Key4','Item1','Menu1'
UNION
SELECT 'Key4','item1','Menu5'
UNION
SELECT 'Key5','item2','menu1'
UNION
SELECT 'Key5','item2','menu2'
UNION
SELECT 'Key6','item10','menu1'
UNION
SELECT 'Key6','item10','menu2'
UNION
SELECT 'Key6','item10','menu3'
UNION
SELECT 'Key6','item10','menu7'


SELECT a.menufield [Menu],ISNULL(acount,0) [Table1],ISNULL(bcount,0) [Table2]
FROM (
SELECT menufield,COUNT(*) acount
FROM dbo.Table1
GROUP BY menufield) a
LEFT JOIN
(SELECT menufield,COUNT(*) bcount
FROM dbo.Table2 
GROUP BY menuField) b
 ON a.menufield = b.menufield
ORDER BY a.menufield

Open in new window

0
 
LVL 40

Expert Comment

by:Sharath
ID: 35174023
In your sample, table2.K2 key records also has the same values that table1.ID1. Don't you want K2 in your expected result along with K3?
0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 35174036
you can try this sql.
;WITH cte1 
     AS (SELECT DISTINCT idfield, 
                         RTRIM(SUBSTRING(ISNULL((SELECT   ',' + menufield 
                                                 FROM     Table1 t1 
                                                 WHERE    t1.idfield = t2.idfield 
                                                 ORDER BY t1.menufield 
                                                 for xml path('')),''),2,2000)) Grp 
         FROM   Table1 t2), 
     cte2 
     AS (SELECT DISTINCT KeyField, 
                         RTRIM(SUBSTRING(ISNULL((SELECT   ',' + menufield 
                                                 FROM     Table2 t1 
                                                 WHERE    t1.KeyField = t2.KeyField 
                                                 ORDER BY t1.menufield 
                                                 for xml path('')),''),2,2000)) Grp 
         FROM   Table2 t2) 
SELECT c2.KeyField 
FROM   cte1 c1 
       JOIN cte2 c2 
         ON c1.Grp = c2.Grp

Open in new window

Tested with your sample data and I got two keys, Key2 and Key3 as both have menu1,2,3 matching with ID1.
/* Thanks dbaSQL for sample data
CREATE TABLE Table1 ( 
  idField VARCHAR(5),menufield VARCHAR(10)) 

INSERT Table1 
      (idfield,menufield) 
SELECT 'ID1','menu1' 
UNION 
SELECT 'ID1','Menu2' 
UNION 
SELECT 'ID1','menu3' 
UNION 
SELECT 'ID2','Menu2' 
UNION 
SELECT 'ID2','Menu10' 
UNION 
SELECT 'ID3','Menu7' 
UNION 
SELECT 'ID3','Menu5' 
UNION 
SELECT 'ID3','Menu1' 

CREATE TABLE Table2 ( 
  keyField CHAR(5),itemField VARCHAR(6),menuField VARCHAR(10)) 

INSERT Table2 
      (keyField,itemField,menuField) 
SELECT 'Key1','Item1','Menu1' 
UNION 
SELECT 'Key1','item1','Menu5' 
UNION 
SELECT 'Key2','item1','menu1' 
UNION 
SELECT 'Key2','item1','menu2' 
UNION 
SELECT 'Key2','item1','menu3' 
UNION 
SELECT 'Key3','item2','menu1' 
UNION 
SELECT 'Key3','item2','menu2' 
UNION 
SELECT 'Key3','item2','menu3' 
UNION 
SELECT 'Key4','Item1','Menu1' 
UNION 
SELECT 'Key4','item1','Menu5' 
UNION 
SELECT 'Key5','item2','menu1' 
UNION 
SELECT 'Key5','item2','menu2' 
UNION 
SELECT 'Key6','item10','menu1' 
UNION 
SELECT 'Key6','item10','menu2' 
UNION 
SELECT 'Key6','item10','menu3' 
UNION 
SELECT 'Key6','item10','menu7'
*/
;WITH cte1 
     AS (SELECT DISTINCT idfield, 
                         RTRIM(SUBSTRING(ISNULL((SELECT   ',' + menufield 
                                                 FROM     Table1 t1 
                                                 WHERE    t1.idfield = t2.idfield 
                                                 ORDER BY t1.menufield 
                                                 for xml path('')),''),2,2000)) Grp 
         FROM   Table1 t2), 
     cte2 
     AS (SELECT DISTINCT KeyField, 
                         RTRIM(SUBSTRING(ISNULL((SELECT   ',' + menufield 
                                                 FROM     Table2 t1 
                                                 WHERE    t1.KeyField = t2.KeyField 
                                                 ORDER BY t1.menufield 
                                                 for xml path('')),''),2,2000)) Grp 
         FROM   Table2 t2) 
SELECT c2.KeyField 
FROM   cte1 c1 
       JOIN cte2 c2 
         ON c1.Grp = c2.Grp
/*
KeyField
Key2 
Key3 
*/

Open in new window

0
 
LVL 3

Author Closing Comment

by:DDB
ID: 35181241
thank you much! exactly what I need!
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Perl Versus AWK? 7 52
SQL R 21 27
changing page verifacation 1 28
MS SQL and inserting dates into tables using stored procedures 6 17
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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 INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

837 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