?
Solved

SQL extract without cursor

Posted on 2011-03-19
7
Medium Priority
?
227 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
[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
  • 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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 41

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 41

Accepted Solution

by:
Sharath earned 2000 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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…
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.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
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.

765 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