Results 1 to 6 of 6

Thread: joins in sql

  1. #1
    katkamravi is offline Member
    Join Date
    Apr 2009
    Posts
    3
    Rep Power
    0

    Default joins in sql

    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
    Last edited by katkamravi; 07-03-2009 at 01:47 PM.

  2. #2
    urskitkit is offline Member
    Join Date
    Jul 2009
    Posts
    1
    Rep Power
    0

    Default

    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:) :) :)

  3. #3
    Eranga's Avatar
    Eranga is offline Moderator
    Join Date
    Jul 2007
    Location
    Colombo, Sri Lanka
    Posts
    11,372
    Blog Entries
    1
    Rep Power
    20

    Default

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

  4. #4
    mtyoung is offline Senior Member
    Join Date
    Dec 2008
    Location
    Hong Kong
    Posts
    473
    Rep Power
    6

  5. #5
    mtyoung is offline Senior Member
    Join Date
    Dec 2008
    Location
    Hong Kong
    Posts
    473
    Rep Power
    6

    Default

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

  6. #6
    Join Date
    Jul 2009
    Posts
    10
    Rep Power
    0

    Default

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •