Distinct query with INNER JOIN in SQL SERVER?

Question

Distinct query with INNER JOIN in SQL SERVER?

Navigation

#1 by (2 votes)
#2 by (1 votes)

1

Good morning: I have two tables in which the one by means of the reference, and it makes the query well, but I want to put the other fields of my table entsal and that are equal to the reference, for example add names , tea de la table entsal.

Example of my query:

select * from (SELECT DISTINCT referencia from salidas) entsal INNER JOIN (SELECT referencia FROM salidas) salidas ON entsal.referencia= salidas.referencia

table entsal fields = nameS (char), reference (char), tea (numeric)

table outputs fields = vin (char), status (char), reference (char)

I want you not to repeat the references and also bring me the corresponding information to my table of entsal with your reference that you have. Can someone help me with my doubt, please.

sql sql-server

asked by AraceLi Torres 25.07.2016 в 15:43

source

2 answers

Problem when saving record in database with PHP7 How can I modify an xml file, delete and write a child, in java?

score 2 · Answer 1

2

You should alias each table and select by prefixing the alias, for example:

Select Distinct A.Campo1,A.Campo2,B.Campo1,B.Campo2 from tabla1 as A 
INNER JOIN Tabla2 as B 
ON A.referencia= B.referencia

answered by 30.08.2016 в 01:34

score 1 · Answer 2

I will start by making the following assumptions:

The sample query is badly formed since in fact it is being named as an entsal and outputs to the same query:

SELECT reference FROM outputs

For a column to be used as a reference in a foreign key, this column must have at least the unique constraint, therefore the use of the distinct is redundant.

The inner join can be done between both tables directly by simply executing:

select * from entsal inner join salidas ON entsal.referencia = salidas.referencia;

If the foreign key is not defined, it must be taken into account that there may be more than one row with the same value for reference in any of the tables, so the join would return more rows of the that can be expected, to exemplify that behavior, you can undo the insert in the example you left.

Example in ideone