SQL - Master in same table with default value - Return the different values

Question

SQL - Master in same table with default value - Return the different values

Navigation

#1 by (1 votes)

0

I would like to know how (and if it can be done) in the same query, as long as the following SQL server query is efficient:

I have a single table with the following fields: Id, CorporationId, Name, Value.

In this table there are some "masters" in such a way that the Corporation is always null, and the Name and its value are kept unaltered.

Now, sometimes rows will be inserted with corporationId being able to customize the value for a given name.

For example: - 1, null, Floor, 10 - 2, null, TErraza, 10 - 3, 150, Floor, 15

What I want is that if I do a search by corporation, being a corporation in this case 150, I will return rows 2 and 3. In case of not passing any corporation I will return the default values, that is, rows 1 and 2.

Can you do this in a single consultation?

Thanks

EDIT: I tried this:

(SELECT DISTINCT * FROM t1 WHERE [CorporationId] is null

UNION

SELECT DISTINCT * FROM 
t1 WHERE [CorporationId] = 150)

And my idea now was to remove the rows that have coporation id to null, if there is a row with corporation with value and duplicate name

thanks

sql sql-server

asked by daicon 19.02.2018 в 23:02

source

1 answer

Subquery in count SQL Float with one unrounded decimal C ++ [closed]

score 1 · Accepted Answer

You can list all the default values, join them with a LEFT JOIN with the values of the corporation and, using the function ISNULL , select the value of the corporation if it exists and, if not, the value by default.

The query could stay:

SELECT ISNULL(C.Id, D.Id) Id, C.CorporationID, D.Nombre, ISNULL(C.Valor, D.Valor) Valor
FROM t1 D
LEFT JOIN t1 C ON C.Nombre = D.Nombre AND C.CorporationId=150
WHERE D.CorporationId IS NULL