?
Solved

sqlserver 2000,sql query doubt

Posted on 2008-10-30
9
Medium Priority
?
178 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 1500 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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

719 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