Duplicate values returned with joins

Question

I was wondering if there is a way using TSQL join statement (or any other available option) to only display certain values. I will try and explain exactly what I mean.

My database has tables called Job, consign, dechead, decitem. Job, consign, and dechead will only ever have one line per record but decitem can have multiple records all tied to the dechead with a foreign key. I am writing a query that pulls various values from each table. This is fine with all the tables except decitem. From dechead I need to pull an invoice value and from decitem I need to grab the net wieghts. When the results are returned if dechead has multiple child decitem tables it displays all values from both tables. What I need it to do is only display the dechad values once and then all the decitems values.

e.g.

1 ¦123¦£2000¦15.00¦1

2 ¦--¦------¦20.00¦2

3 ¦--¦------¦25.00¦3

Line 1 displays values from dechead and the first line/Join from decitems. Lines 2 and 3 just display values from decitem. If I then export the query to say excel I do not have duplicate values in the first two fileds of lines 2 and 3

e.g.

1 ¦123¦£2000¦15.00¦1

2 ¦123¦£2000¦20.00¦2

3 ¦123¦£2000¦25.00¦3

Thanks in advance.

Answer 1

Check out 'group by' for your RDBMS http://msdn.microsoft.com/en-US/library/ms177673%28v=SQL.90%29.aspx

Answer 2

this is a task best left for the application, but if you must do it in sql, try this:

SELECT
    CASE
        WHEN RowVal=1 THEN dt.col1
        ELSE NULL
    END as Col1
    ,CASE
        WHEN RowVal=1 THEN dt.col2
        ELSE NULL
    END as Col2
    ,dt.Col3
    ,dt.Col4
    FROM (SELECT
              col1, col2, col3
                  ,ROW_NUMBER OVER(PARTITION BY Col1 ORDER BY Col1,Col4) AS RowVal
              FROM ...rest of your big query here...
         ) dt
    ORDER BY dt.col1,dt.Col4