Solved

SQL extract without cursor

Posted on 2011-03-19
7
226 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

In the distant past (last year) I hacked together a little toy that would allow a couple of Manager types to query, preview, and extract data from a number of MongoDB instances, to their tool of choice: Excel (http://dilbert.com/strips/comic/2007-08…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Six Sigma Control Plans

705 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