Post reply

left outer join with a where clause should display all the values in the left table

  • July 28, 2009 at 4:36 am

    #136613

    Hi,

    I am new to SQL queries..

    I have four tables..

    Master.ProductDatabase which contain the ProductName, MaterialID etc..

    Sales.DispatchDatabase which contain the DispatchID, DispatchDate, CustomerName etc..

    Sales.DispatchedProducts which contain DispatchID, MaterialID, Quantity..

    Stock.StockDatabase which contain MaterialID, Stock(current)

    I need a join query that connects the above three tables and give me the dispatch details based on the dispatch date.. so that I can add it to current stock and calculate the stock as on that particular date..

    The query that I have written is as follows:

    SELECT Master.ProductDatabase.ProductName, SUM(Sales.DispatchedProducts.Quantity) AS 'Dispatched'

    FROM Sales.DispatchDatabase INNER JOIN

    Sales.DispatchedProducts ON Sales.DispatchDatabase.DispatchID = Sales.DispatchedProducts.DispatchID RIGHT OUTER JOIN

    Master.ProductDatabase ON Sales.DispatchedProducts.MaterialID = Master.ProductDatabase.MaterialID

    WHERE (Sales.DispatchDatabase.InvoiceDate > '2009-07-04') OR

    (Sales.DispatchDatabase.InvoiceDate IS NULL)

    GROUP BY Master.ProductDatabase.ProductName

    Here all those products that are not dispatched after 4th July are not displayed in the query..

    while the query that i run for current stock gives all the products.. so i cannot compare them easily..

    Can I have a query that will display all the products and give a value = 0 if it is not dispatched after that particular date..

    I hope I have explained my doubt sufficiently.. if there is any additional information required please let me know..

    thanks alot!!

    Regards,

    Vaibhav

  • July 30, 2009 at 4:02 am

    #1032252

    no response..

  • July 30, 2009 at 4:19 am

    #1032259

    Hi,

    You would have a much greater chance of getting a response if you post ddls and sample data in a readily consummable format, along with the query you have already posted, and how the output differs from what you want, i.e provide a sample of what you expect based upon the sample data you provide. The following link gives you the information you would need to post to maximise your chance of getting a useful response: http://www.sqlservercentral.com/articles/Best+Practices/61537/

    Hope this helps!

  • July 30, 2009 at 4:23 am

    #1032260

    okay..

    well following is the example.. I have made my requirement more specific..

    this must help..

    I have two tables with unique product name column and different quantity..

    For eg:

    1st table has following values

    Product1 - "25"

    Product2 - "30"

    Product3 - "50"

    and my second table has following values

    Product2 - "20"

    Product3 - "23"

    Now I want a resulting select query that will give me the following result

    Product1 (=25 + 0) - "25"

    Product2 (=30 + 20) - "50"

    Product3 (=50 + 23) - "73"

    Please help..

    Regards,

    Vaibhav

  • July 30, 2009 at 4:24 am

    #1032261

    extra information..

    the two tables that I am talking about are not physically present but they are created using a select statement..

    Thanks..

    Vaibhav

  • July 30, 2009 at 4:27 am

    #1032264

    Wow, that was a fast response! Did you have a chance to read the article I referenced above?

    Any chance of getting sample data and ddl (create table and insert statements) for each of the tables which make up the queries you are having problems with?

    Thanks

    Allister

  • July 30, 2009 at 4:28 am

    #1032266

    c.vaibhav (7/30/2009)

    extra information..

    the two tables that I am talking about are not physically present but they are created using a select statement..

    Thanks..

    Vaibhav

    Yeah, I guessed that, I think you have only provided code for one of the select statements?

  • July 30, 2009 at 5:18 am

    #1032291

    ohh.. thanks.. I got it done.. The query is:

    select t1.price + isnull(t2.price,0) as totalprice from table1 t1

    left outer join table2 t2 on t1.product=t2.product

    regards,

    Vaibhav

Viewing 8 posts - 1 through 7 (of 7 total)

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