Solved

SQL extract without cursor

Posted on 2011-03-19
7
209 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
Comment Utility
>>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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 17

Expert Comment

by:dbaSQL
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
thank you much! exactly what I need!
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Email validation in proper way is  very important validation required in any web pages. This code is self explainable except that Regular Expression which I used for pattern matching. I originally published as a thread on my website : http://www…
There are many situations when we need to display the data in sorted order. For example: Student details by name or by rank or by total marks etc. If you are working on data driven based projects then you will use sorting techniques very frequently.…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

743 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

16 Experts available now in Live!

Get 1:1 Help Now