SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Multiple records comparation
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

homelessnick
Starting Member

3 Posts

Posted - 02/21/2013 :  06:21:02  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 02/21/2013 :  07:05:01  Show Profile  Reply with Quote
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
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2165 Posts

Posted - 02/21/2013 :  07:22:47  Show Profile  Reply with Quote
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


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

Go to Top of Page

homelessnick
Starting Member

3 Posts

Posted - 02/21/2013 :  09:18:40  Show Profile  Reply with Quote
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!
Go to Top of Page

homelessnick
Starting Member

3 Posts

Posted - 02/21/2013 :  09:29:33  Show Profile  Reply with Quote
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!!!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000