joins in sql

[katkamravi]

can we do looping in select query.
suppose i have 3 tables

purchase table, item table, and purchase_item_relation table
in purchase table i just enter purchase date and location
and item table i enter all the items purchased
and in purchase_item_relation table i enter purchaseId, itemId

exaple
if i purchase Laptop,Mouse,Speakers on some date and at some location
so there will be only 1 record in purchase table and 3 records in items table and 3 records purchase_item_relation

purchase table:
Date............Location.........Purchase_id
24/12/2009....Dadar............123

Item table:
Item_name.............Item_id
Laptop...................21
Mouse....................22
Speaker..................23

relatio table:
Purchase_id........Item_id
123....................21
123....................22
123....................23


now i want to display this record like this

purchasedate......location........item
-------------------------------------
24-2-2009..........dadar...........laptop,mouse,spaeker s

if we do join we will get three records but i want only one record like above (all three items in oe colum).
please help i want this from select query

[urskitkit]

CREATE TABLE purchase --table:
(Date DATETIME,
Location VARCHAR(100),
Purchase_id INT)
INSERT INTO purchase
SELECT '2009-12-24', 'Dadar', 123
CREATE TABLE Item --table:
(Item_name VARCHAR(100),
Item_id INT)
INSERT INTO Item
SELECT 'Laptop', 21 UNION ALL
SELECT 'Mouse', 22 UNION ALL
SELECT 'Speaker', 23
CREATE TABLE relatio --table:
(Purchase_id INT,
Item_id INT,)
INSERT INTO relatio
SELECT 123,21 UNION ALL
SELECT 123,22 UNION ALL
SELECT 123,23
-- Starting data
SELECT * FROM purchase
SELECT * FROM Item
SELECT * FROM relatio
SELECT
Date,
Location,
STUFF((SELECT ',' + Item_name
FROM Item i INNER JOIN relatio r ON r.Item_Id = i.Item_Id
WHERE r.Purchase_Id = p.Purchase_id
FOR XML PATH(''))
,1,1,'') as [Item]
FROM purchase p:) :) :)

[Eranga]

Completely related with the Database, not with java. ;) Actually you can have more comments on a database forum.

[mtyoung]

sub query... poor performance...

[mtyoung]

i think you can get that 3 result row and change it in the java code

[mariosimaremare]

you can't do it in DBMS, hard and takes more effort.
just get all records then concat.