asked on

sqlserver 2000,sql query doubt

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

ASKER CERTIFIED SOLUTION

Pratima

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BrandonGalderisi

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

BrandonGalderisi

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?

Rajar Ahmed

ASKER

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....

BrandonGalderisi

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.

Rajar Ahmed

ASKER

okay ,i ll avoid in future .

Ya got it right , thanks for your help .

BrandonGalderisi

But did you really want the cross join or just a list of restaurants that serve a particular fid?

Rajar Ahmed

ASKER

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 . .

BrandonGalderisi

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.