Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2009
    Posts
    2

    Thumbs up Unanswered: How to query column for different values?

    Hi, i have three tables:

    S02:
    --------------------
    S02_ID | S02_NAME
    --------------------
    1502 |AM
    1503 |KD
    1504 |KG
    1467 |KOM
    1468 |KOS
    1469 |L
    1470 |M
    1471 |ML
    1472 |SC
    1473 |ST
    1474 |TB
    1476 |ZAV
    1475 |ZV
    1370 |Warehouse1
    1371 |Warehouse2
    1372 |Warehouse3
    1373 |Warehouse4
    1374 |Warehouse5
    1375 |Warehouse6
    1376 |Warehouse7
    --------------------

    Y36:
    -------------------------------
    Y36_ID | Y36_S02 | Y36_order
    -------------------------------
    45 | 1373 |2009/00001
    46 | 1372 |2009/00002
    -------------------------------

    Y37:
    ------------------------------
    Y37_Y36 |Y37_value |Y37_S02
    ------------------------------
    45 |22.00 | 1504
    46 |25.00 | 1469
    ------------------------------

    I would like to query all those tables in a way, that I would see the result like this:

    ---------------------------------
    Warehouse | Value | measure unit
    ---------------------------------
    Warehouse4 | 22.00 | KG
    Warehouse3 | 25.00 | L
    ---------------------------------

    I have no idea, how to query two times the first column in S02, that I could retrieve warehouse name and measure unit. I really need help on this.
    Thanks in advance.
    Last edited by Spajder; 04-08-09 at 05:46.

  2. #2
    Join Date
    Jan 2009
    Location
    India
    Posts
    2
    Hello,

    Try this...

    CREATE TABLE [dbo].[S02]
    (
    S02_ID INT
    ,S02_Name NVARCHAR(50)
    )

    INSERT INTO [dbo].[S02]
    SELECT 1502,'AM'
    UNION
    SELECT 1504,'KG'
    UNION
    SELECT 1469,'L'
    UNION
    SELECT 1373, 'Warehouse4'
    UNION
    SELECT 1374, 'Warehouse3'

    CREATE TABLE [dbo].[Y36]
    (
    Y36_ID INT
    ,Y36_S02 INT
    ,Y36_Order NVARCHAR(50)
    )

    INSERT INTO [dbo].[Y36]
    SELECT 45,1373,'2009/00001'
    UNION
    SELECT 46,1374,'2009/00002'

    CREATE TABLE [dbo].[Y37]
    (
    Y37_Y36 INT
    ,Y37_Value MONEY
    ,Y37_S02 INT
    )

    INSERT INTO [dbo].[Y37]
    SELECT 45,22.00,1504
    UNION
    SELECT 46,25.00,1469


    --SELECT * FROM S02
    --SELECT * FROM Y36
    --SELECT * FROM Y37

    SELECT S021.S02_Name AS Warehouse
    ,Y37.Y37_Value AS Value
    ,S022.S02_Name AS MeasureUnit
    FROM S02 AS S021
    INNER JOIN Y36 AS Y36
    ON S021.S02_ID = Y36.Y36_S02
    INNER JOIN Y37 AS Y37
    ON Y37.Y37_Y36 = Y36.Y36_ID
    INNER JOIN S02 AS S022
    ON Y37.Y37_S02 = S022.S02_ID
    ORDER BY S021.S02_Name DESC

  3. #3
    Join Date
    Apr 2009
    Posts
    2

    It works!

    Hey thanks, Kokkula, it works!

Posting Permissions

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