Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL extract without cursor

Posted on 2011-03-19
7
Medium Priority
?
230 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

636 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