CODEWITHSUNDEEP
sqlouter-join
Camilla
Camilla

Reputation: 969

SQL query: return values even if they don't match

2I have the following two tables with the associated fields:

Values: id, data, quantity
Items: id, data, price

The match depends on the fields: id, data. I'm trying to join the two tables so that if for the very same id and data there is an element in Values, but not in Items, the price is set to 0; while if there is an element in Items but not in Values the field quantity is set to 0.

So far I've written the following code, but it doesn't return me anything!!!

 SELECT     dbo.Values.id, dbo.Values.data, dbo.Values.quantity, dbo.Items.price
 FROM       dbo.Values FULL OUTER JOIN
            dbo.Items ON dbo.Values.id = dbo.Items.id AND dbo.Values.data = dbo.Items.data

Example:

Values                          Items                            Join
(1, 05/07/2013 00:00:00, 2)     (1, 05/07/2013 00:00:00, 20)     (1, 05/07/2013 00:00:00, 2, 20)  
(2, 23/06/2013 00:00:00, 50)    (2, 03/02/2013 00:00:00, 1000)   (2, 23/06/2013 00:00:00, 50, 0)  
                                                                 (2, 03/02/2013 00:00:00, 0, 1000)

Upvotes: 0

Views: 1948

Answers (2)

Luis LL
Luis LL

Reputation: 2993

your join works well , but you are not making use of coalesce or isnull... fidle it.

SELECT     ISNULL(dbo.[Values].id, dbo.Items.id ), ISNULL(dbo.[Values].DATA, dbo.Items.DATA), ISNULL(dbo.[Values].quantity, 0), ISNULL(dbo.Items.price, 0)
FROM       dbo.[Values] 
FULL OUTER JOIN dbo.Items ON dbo.[Values].id = dbo.Items.id AND dbo.[Values].data = dbo.Items.DATA

Upvotes: 1

Ashish
Ashish

Reputation: 72

FULL OUTER JOIN returns all the values of both the tables, even if condition doesn't match. Therefore your query returning all the rows of both tables.

Upvotes: 0

Related Questions