Multiple records comparation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Multiple records comparation

Author

Topic

homelessnick
Starting Member

3 Posts

Posted - 2013-02-21 : 06:21:02

Hi everyone!

I have a food menu table with the following structure:

MenuId Order FoodId
------ ----- ------
320 1 NP0030
320 2 NS0862
320 3 NG4376
611 1 NP0030
611 2 NS0862
611 3 NG4376

Which means that menu number 320 has 3 plates, with its respective order and FoodId. MenuId 611 is exactly the same, only changes the menuID

The problem is that I need to create another table containing unique menus, for example:

MenuId Order FoodId
------ ----- ------
001 1 NP0030
001 2 NS0862
001 3 NG4376

Any advice or help would be appreciated. Thanks in advance!

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-21 : 07:05:01

DECLARE @tab TABLE(MenuId INT, [Order] INT, FoodId VARCHAR(10))
INSERT INTO @tab
SELECT 320, 1, 'NP0030' UNION ALL
SELECT 320, 2, 'NS0862' UNION ALL
SELECT 320, 3, 'NG4376' UNION ALL
SELECT 611, 1, 'NP0030' UNION ALL
SELECT 611, 2, 'NS0862' UNION ALL
SELECT 611, 3, 'NG4376'
SELECT RIGHT( '000'+ CAST(DENSE_RANK() OVER( Order BY (SELECT 1)) AS VARCHAR(5)), 3) MenuId, [Order], FoodId
FROM @tab
GROUP BY [Order], FoodId

--
Chandu

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2013-02-21 : 07:22:47

[code]create table #yak (MenuID varchar(3), [Order] int, FoodID varchar(6))

insert into #yak (MenuID, [Order], FoodID)
values
('320',1,'NP0030')
,('320',2,'NS0862')
,('320',3,'NG4376')
,('611',1,'NP0030')
,('611',2,'NS0862')
,('611',3,'NG4376')
,('225',1,'NM0908')
,('225',2,'NJ0889')

select * from #yak

select
MenuID
,ROW_NUMBER()OVER(ORDER BY MenuID) as [Order]
,FoodID
from
(select distinct '001' as MenuID
,foodid
from #yak) y

MenuID Order FoodID
001 1 NG4376
001 2 NJ0889
001 3 NM0908
001 4 NP0030
001 5 NS0862
[/code]

I added a few extra records to see if this is what the OP meant. My way is MUCH more clumsy

, but i do not get a repeating order number.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

homelessnick
Starting Member

3 Posts

Posted - 2013-02-21 : 09:18:40

Thanks in advance for the quick replies, but I think I have explained myself correctly.

What I need is to know how many unique menus I have in my table, not creating one menu made of all the plates.

For example:

MenuId Order FoodId
------ ----- ------
320 1 NP0030
320 2 NS0862
320 3 NG4376
611 1 NP0030
611 2 NS0862
611 3 NG4376
555 1 DS0974
555 2 DS8374
555 3 DS1232

Would return:
320 1 NP0030
320 2 NS0862
320 3 NG4376
555 1 DS0974
555 2 DS8374
555 3 DS1232

MenuId 611 disapears because its duplicated (MenuId 320 goes first and its the same menu)

Thanks again!

homelessnick
Starting Member

3 Posts

Posted - 2013-02-21 : 09:29:33

At least I need to know if a menu in table 1 exists in table 2. I mean

Table 1
MenuId Order FoodId
------ ----- ------
999 1 NP0030
999 2 NS0862
999 3 NG4376

Table 2
MenuId Order FoodId
------ ----- ------
320 1 NP0030
320 2 NS0862
320 3 NG4376
555 1 DS0974
555 2 DS8374
555 3 DS1234

MenuId 999 exists in table 2, with the MenuId 320

Thanks again!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources