I have these 2 tables
Table 1
----------------------------------
| articulo | Ubicacion |
----------------------------------
| 200116 | cy1112 |
----------------------------------
| 200116 | ee3091 |
----------------------------------
table 2
----------------------------------
| articulo | Ubicacion |
----------------------------------
| 200116 | cy1112 |
----------------------------------
| 200116 | dd3091 |
----------------------------------
I'm trying to do it this way
SI_Ubicacion = COUNT(DISTINCT ISNULL(tabla1.SI_Ubicacion,tabla2.SI_Ubicacion))
is that it count the locations and if they are the same, do not count it twice and the result must be SI_Ubicacion = 3
At this moment it is only telling me the locations of the first table
The tables are related by thearticulo
I hope you can help me, Thank you.
Just do a
UNION
between both tables:Which gives you the following result:
To get the total of records it would be as follows:
Which gives you the following results:
Here you can see thedemostración y sus resultados
It is not possible to understand the question well, but I assume that if there are the tables that you indicate are related by an article, there must be an article table , then you can make a union of table1 and table2 and then that union relate it to your article table to finally do a group by.
Here I leave you an example of what I mention: see example
Note: remember that the UNION of two queries ignores the equal values
If it is not clear, try to improve your question, which is very poorly understood.
As Andrés says, the answer goes through the use of
UNION
to add the two location tables and take advantage of the fact that this statement already removes the duplicate records, but pay attention ,UNION
it takes into account the entire row to determine duplicates, so itSELECT
should recover onlyarticulo
andubicacion
, if we added another field that is not repeated, we would be counting the locations incorrectly.The idea would be the following:
The exit:
An eventual improvement with respect to the previous answers is in the use of
LEFT JOIN
the andISNULL
that allows us to "count" also those articles that do not have locations.