Solved

sqlserver 2000,sql query doubt

Posted on 2008-10-30
9
163 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

911 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now