Post reply

Right join

  • February 8, 2008 at 12:32 pm

    #103458

    Hi friends, this is my first post in the forum..so Hello.

    I have two tables..One with sold items and the other with the Item Types. I want a list of how many items of each type were sold in a specific date. I have the following query:

    select ItemTypeName, count(*)

    from Sales right join ItemTypes on (ItemTypes.ItemTypeId = Sales.ItemTypeId)

    and datediff(day, SaleDate, getdate()) = 0

    Group By ItemTypeName

    This query returns only result for those items appearing in the Items table, but I would like a complete report. What I want actually is a query returning all the item types and "0" for those who are not in the Sales table(no item of that type were sold in that period of time).

    What am I ding wrong here?

    Thanks

  • February 8, 2008 at 4:44 pm

    #776658

    Here you go......

    DECLARE @ItemTypes TABLE (ItemTypeID int, ItemTypeName varchar(10))

    INSERT INTO @ItemTypes

    SELECT 1, 'One' UNION ALL

    SELECT 2, 'Two' UNION ALL

    SELECT 3, 'Three'

    DECLARE @Sales TABLE (ItemTypeID int, SaleID int IDENTITY(1,1))

    INSERT INTO @Sales (ItemTypeID)

    SELECT 1 UNION ALL

    SELECT 1 UNION ALL

    SELECT 1 UNION ALL

    SELECT 2 UNION ALL

    SELECT 2

    SELECT t1.*, COALESCE(t2.SaleCount, 0) as SaleCount

    FROM @ItemTypes t1

    LEFT JOIN (

    SELECT ItemTypeID, COUNT(*) as SaleCount

    FROM @Sales

    GROUP BY ItemTypeID

    ) t2

    ON t1.ItemTypeID = t2.ItemTypeID

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • February 10, 2008 at 8:40 pm

    #776795

    Thanks a lot...

    I will try it and let u know.

  • February 11, 2008 at 6:37 am

    #776869

    Thanks lot man!!!!

    You got it!!!!

  • February 11, 2008 at 9:06 am

    #776938

    No problem. The key to this one is the derived table. If you're not real familiar with derived tables, I suggest searching SSC and reading up on them. There's also info in BOL. Understanding how and when to use derived tables can greatly simplify your code and they can be a great tool for query tuning for helping with replacing cursors or correlated subqueries.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply