0

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)
Camilla
  • 949
  • 2
  • 14
  • 26

2 Answers2

1

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
Luis LL
  • 2,912
  • 2
  • 19
  • 21
  • your query works better than mine, but in case there is a value in Items which does not match with an element in Values it doesn't return me anything. Looking at the example I wrote above, it doesn't return me the field: (1, 03/02/2013 00:00:00, 0, 1000) – Camilla Jul 05 '13 at 09:07
  • there is not such a row in any sample row that you wrote, the closest thing is 2, 03/02/2013 00:00:00, 1000 and it indeed appears. look at the fiddle http://sqlfiddle.com/#!3/97d8d/2/0 – Luis LL Jul 05 '13 at 09:45
  • it works in fiddle, but when I execute the query in my sql it doesn't return me this row (2, 03/02/2013 00:00:00, 0, 1000) – Camilla Jul 05 '13 at 13:25
  • @Camilla: Are you positive you are using *FULL* OUTER JOIN and not *LEFT* OUTER JOIN when running the query in your environment? – Andriy M Jul 06 '13 at 14:40
  • Sure, *LEFT JOIN* would only bring the rows that have no match on the second table, but it would bring no row that has no match in Values. Since you want both sides, you use *FULL JOIN* – Luis LL Jul 06 '13 at 14:45
0

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.

Ashish
  • 72
  • 4