July 8, 2009 at 5:44 am
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
July 8, 2009 at 6:12 am
try this:
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
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
July 8, 2009 at 7:16 am
thanks a lot.
u r genius
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply