Solved

sqlserver 2000,sql query doubt

Posted on 2008-10-30
9
165 Views
Last Modified: 2010-03-19
1.foodtable field                                            
fid int(identity(1,1),primary)
fname varchar                                                        

table 2:Restauranttable          
1.Rid primary key                                                  
2. restaurantname varchar                                                      

table3 : pricetable                                                    
1.pid int(identity(1,1),primary)                                    
2.fid int foriegn key(foodtable)
3..rid  int  foreign key(resttable)
4.price float    



select distinct fid,(Select resname from resttable where rid=maintable.rid) as resname,price,rid  from maintable
 
output is
56	Restaurant A
56	Restaurant B
56	Restaurant C
56	Restaurant D
63	Restaurant A
63	Restaurant B
63	Restaurant C
63	Restaurant D
65	Restaurant B
 
 but i want 
56       Restaurant A,Restaurant B,Restaurant C,Restaurant D
63       Restaurant A,Restaurant B,Restaurant C,Restaurant D

Open in new window

0
Comment
Question by:Rajar Ahmed
  • 5
  • 3
9 Comments
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 500 total points
ID: 22848046
create FUNCTION [dbo].[UDF_GetResNames]
(
       @fid int
      
)
RETURNS nvarchar(4000)
AS
BEGIN      
      -- Declare the return variable here
      DECLARE @str NVARCHAR(4000)
      DECLARE @start NVARCHAR(10)
      DECLARE @end NVARCHAR(10)
      SET            @str = ''
      SET            @start = ' '
      SET            @end = ','

SELECT      @str =      CASE
                                    WHEN @str = '' THEN @start + restaurantname
                                    ELSE @str + @end + @start + restaurantname END

      FROM      resttable,maintable where maintable.fid=@fid

      RETURN @str      

END


select distinct fid,[dbo].[UDF_GetResNames](fid) as resname from maintable
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22851314
Try this:
create function dbo.fn_GetResNames(@fid int)
returns nvarchar(max)
as
begin
declare @Res nvarchar(max)
select @Res = isnull(@Res+',','')+ r.resName from restaurantTable r inner join pricetable  p on r.rid = p.rid where p.fid = @fid
 
return @res
end
 
select fid, dbo.fn_GetResNames(fid) from foodTable

Open in new window

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22852354
Really?  You really wanted a cross join of foods to all restaurants?  I assumed you would only want foods that the restaurant served.  Did you try both solutions?
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 18

Author Comment

by:Rajar Ahmed
ID: 22852614
hi , BrandonGalderis....

s ur r8 , i need  wat ur saying , but the thing is got the answer by using someother query ,

But , that soln gave  idea abt functions to retrive values .  

Regarding ur solutn , am eager to c d result ,

But i found with these errors...

Server: Msg 170, Level 15, State 1, Procedure fn_GetResNames, Line 2
Line 2: Incorrect syntax near 'max'.
Server: Msg 170, Level 15, State 1, Procedure fn_GetResNames, Line 5
Line 5: Incorrect syntax near 'max'.
Server: Msg 137, Level 15, State 1, Procedure fn_GetResNames, Line 6
Must declare the variable '@Res'.
Server: Msg 137, Level 15, State 1, Procedure fn_GetResNames, Line 7
Must declare the variable '@res'.

HOpe this ll b helpful in future..
THankss....
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22852915
Please don't use shorthand when responding to questions.  

The reasons for the error is the datatype.  I saw the zones and assumed you were on 2005 or later.  replacing max with 4000 would have solved it.
0
 
LVL 18

Author Comment

by:Rajar Ahmed
ID: 22855234
okay ,i ll avoid in future .

Ya got it right , thanks for your help .
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22855244
But did you really want the cross join or just a list of restaurants that serve a particular fid?
0
 
LVL 18

Author Comment

by:Rajar Ahmed
ID: 22855314
is  cross join means concatenation , ....???i dint understand wats cross join  means .

i was very much trubled with concatenation b4 , so i used a single query to retrieve .

S, just a list of restaurants that serve a particular fid ,  

 Actually am using  for web application.


This is d query i used it latr. . which serves me to retrieve.

SELECT fid, resname FROM maintable INNER JOIN resttable ON restable.rid = maintable.rid WHERE maintable.fid = " + fid

Thanks . .

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22862337
cross join means every A (fid) matched up with every B (restaurant).  I just don't see how the accepted answer gives you that since this:

FROM      resttable,maintable where maintable.fid=@fid

Is a cross join and it will return very restaurant.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

809 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