Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Feb 2011
    Posts
    30

    Unanswered: To do i display a value of Null if an item does not exist

    My sql query that generates the results is :

    USE RSSQLDB
    SELECT
    AccountID,
    Itemcode,
    UserField1,
    b.ITEMID,
    STUFF((SELECT',' + a.ItemDesc AS [text()] from RSITEMDESCRIPTION a where a.ITEMID = b.ITEMID Order by a.ItemDesc for xml PATH('')), 1, 1, '') AS Item_Description,
    STUFF((SELECT',' + c.ItemContent AS [text()] from RSITEMCONTENT c where c.ITEMID = b.ITEMID Order by c.ItemContent for xml PATH('')), 1, 1, '') AS Item_Content
    FROM
    RSITEMDESCRIPTION b
    INNER JOIN RSITEMCONTENT c ON (b.ItemID = c.ItemID)
    Left JOIN RSITEM ON (b.ItemID = RSITEM.ItemID)
    WHERE
    AccountID=63
    GROUP BY
    b.ITEMID,accountid, Itemcode,UserField1
    ORDER BY
    ITEMID


    but in my RSITem table i have ItemIDs that are not in the ItemContent nor the ItemDescription tables.. i would like these items to show up in my results as 'Null' in Item_Description and Item_Content.

    I was reading up the "Case" function.. but i am a bit confused on how i can inject it into this query to give me teh desired results.

    PLEASE help..

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    instead of left join rsitem, try right join rsitem
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2011
    Posts
    13
    This might work

    USE RSSQLDB
    SELECT
    AccountID,
    Itemcode,
    UserField1,
    b.ITEMID,
    Item_Description = Case when b.ITEMID is null then NULL ELSE STUFF((SELECT',' + a.ItemDesc AS [text()] from RSITEMDESCRIPTION a where a.ITEMID = b.ITEMID Order by a.ItemDesc for xml PATH('')), 1, 1, '') END,
    Item_Content = Case when b.ITEMID is null then NULL ELSE STUFF((SELECT',' + c.ItemContent AS [text()] from RSITEMCONTENT c where c.ITEMID = b.ITEMID Order by c.ItemContent for xml PATH('')), 1, 1, '') END
    FROM
    RSITEMDESCRIPTION b
    INNER JOIN RSITEMCONTENT c ON (b.ItemID = c.ItemID)
    Left JOIN RSITEM ON (b.ItemID = RSITEM.ItemID)
    WHERE
    AccountID=63
    GROUP BY
    b.ITEMID,accountid, Itemcode,UserField1
    ORDER BY
    ITEMID

  4. #4
    Join Date
    Aug 2011
    Posts
    13
    Sorry I just realised, B was the first table, not the one with nulls in

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    did you try the right join?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2011
    Posts
    30
    havent had time to try it... tomorrow i will..

    if it doesnt work.. what else can i try ?

  7. #7
    Join Date
    Aug 2011
    Posts
    13
    I don't think the right join will work will it? If the information is not in the other tables then there will be no join to the other tables? Correct me if I am wrong, I think the joins go the right way

    This should work though

    USE RSSQLDB
    SELECT
    AccountID,
    Itemcode,
    UserField1,
    b.ITEMID,
    Item_Description = Case when RSITEM.ITEMID is null then NULL ELSE STUFF((SELECT',' + a.ItemDesc AS [text()] from RSITEMDESCRIPTION a where a.ITEMID = b.ITEMID Order by a.ItemDesc for xml PATH('')), 1, 1, '') END,
    Item_Content = Case when RSITEM.ITEMID is null then NULL ELSE STUFF((SELECT',' + c.ItemContent AS [text()] from RSITEMCONTENT c where c.ITEMID = b.ITEMID Order by c.ItemContent for xml PATH('')), 1, 1, '') END
    FROM
    RSITEMDESCRIPTION b
    INNER JOIN RSITEMCONTENT c ON (b.ItemID = c.ItemID)
    Left JOIN RSITEM ON (b.ItemID = RSITEM.ItemID)
    WHERE
    AccountID=63
    GROUP BY
    b.ITEMID,accountid, Itemcode,UserField1
    ORDER BY
    ITEMID

  8. #8
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Retracted due to misreading previous post.

  9. #9
    Join Date
    Aug 2011
    Posts
    13
    Haha, you must have retracted that quickly then. I was nearly in keyboard warrior mode!

  10. #10
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    ChrisLomax. When I first read your post, I thought you were implying that an Outer join wouldn't work because rows from the other table wouldn't match (which is the whole point of an Outer Join). A closer reading, showed you were referring to the ORDER of the tables in the Outer join. Sorry about that. (I blame it on my caffeine deprived brain this morning...).

  11. #11
    Join Date
    Aug 2011
    Posts
    13
    I would have blamed by keyboard warrior'ness on too much caffeine this morning

    In any case, I think the sample I have provided should sort the issue out

    It's good to get back to these boards, I was a member years ago when I was just starting out in my DBA life. Now I know a thing or 2, be good to give something back

  12. #12
    Join Date
    Feb 2011
    Posts
    30
    it works with the right join and takes only a few secs to run.

    but problem.. If an ItemId has a ItemContent but no ItemDescription and vice se versa... it brings up all the columns need with 'Null' with only the ItemId..
    Last edited by drdre; 08-08-11 at 11:16.

  13. #13
    Join Date
    Feb 2011
    Posts
    30
    ChrisLomax your modifitications do not work. I am getting "
    Msg 8120, Level 16, State 1, Line 2
    Column 'RSITEM.ItemID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
    "

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i can't help you debug your query because i don't know which tables those columns are in

    it would help a great deal if you prefixed every column in the query with its table name

    also, i have no idea what those "for xml" subqueries are for, so perhaps you couyld explain what they're doing and why
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Feb 2011
    Posts
    30
    USE RSSQLDB
    SELECT
    RSITEM.AccountID,
    RSITEM.Itemcode,
    RSITEM.AlternateCode,
    RSITEM.ItemDesc as ShortDescription,
    RSITEM.UserField1,
    RSITEM.USerField2,
    RSITEM.USerField3,
    RSITEM.Userfield4,
    b.ITEMID,
    STUFF((SELECT',' + a.ItemDesc AS [text()] from RSITEMDESCRIPTION a where a.ITEMID = b.ITEMID Order by a.ItemDesc for xml PATH('')), 1, 1, '') AS Item_Description,
    STUFF((SELECT',' + c.ItemContent AS [text()] from RSITEMCONTENT c where c.ITEMID = b.ITEMID Order by c.ItemContent for xml PATH('')), 1, 1, '') AS Item_Content
    FROM
    RSITEMDESCRIPTION b
    INNER JOIN RSITEMCONTENT c ON (b.ItemID = c.ItemID)
    Right JOIN RSITEM ON (b.ItemID = RSITEM.ItemID)
    WHERE
    AccountID=63
    GROUP BY
    b.ITEMID,accountid,AlternateCode,RSITEM.ItemDesc, Itemcode,UserField1,UserField2,UserField3,UserFiel d4
    ORDER BY
    ITEMID


    The 3 tables are RSITEM, RSITEMDESCRIPTION and RSITEMCONTENT, where ItemID is key in all 3 tables.

    The XML path concatenates the RSITEMDESCRIPTION.ItemDesc and RSITEMCONTENT.ItemContent since they are spread access multiple lines per ItemID.

    There are some items that would have both RSITEMDESCRIPTION.ItemDesc and RSITEMCONTENT.ItemContent, some with either of them and some with not of them.

    I hope this explains it.

Posting Permissions

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