Solved

sqlserver 2000,sql query doubt

Posted on 2008-10-30
9
174 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
[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
  • 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

635 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