Solved

sqlserver 2000,sql query doubt

Posted on 2008-10-30
9
160 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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
separate column 24 20
SQL Server 2012 Row Selection 2 29
t-sql splitting string column 5 26
Azure SQL DB? 3 16
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

706 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

18 Experts available now in Live!

Get 1:1 Help Now