CODEWITHSUNDEEP
sqlsql-server
Sebastián Olguín
Sebastián Olguín

Reputation: 3

SQL query with WHERE condition that allows empty fields

I'm sorry if the title isn't very clear, frankly I don't really know how to explain it in simple words. I have the following problem.

I have this query: 

SELECT EQUIPO.EQUI_SERIE
FROM EQUIPO
     LEFT JOIN ASIGNACION ON EQUIPO.EQUI_SERIE = ASIGNACION.EQUI_SERIE
WHERE ASIG_ACTIVA = 'No'
ORDER BY EQUI_SERIE ASC;

I would like it to display all the elements from the EQUIPO table that are or are not referenced in the ASIGNACION table, and if they are referenced, I would like it to only display those which have the 'No' value on the ASIG_ACTIVA column. I've tried different things, but I can't quite find what I need from the internet. Any help would be much appreciated.

Upvotes: 0

Views: 95

Answers (4)

GuidoG
GuidoG

Reputation: 12014

You can do this by restricting your joined table.

SELECT EQUIPO.EQUI_SERIE
FROM EQUIPO
  LEFT JOIN ASIGNACION ON EQUIPO.EQUI_SERIE = ASIGNACION.EQUI_SERIE
                      AND ASIGNACION.ASIG_ACTIVA = 'No'
ORDER BY EQUI_SERIE ASC;

By adding the AND to your join, you restrict the joined table ASIGNACION to only rows that have no in column ASIGN_ACTIVE
That will get you all rows in ASIGNACION that are related and have noas value.
Since its a left join, you also get NULL for every row that is either not related, or has yesas value.

Upvotes: 1

katbarahona
katbarahona

Reputation: 53

just as an addition to the other answers, I'll explain you why your query isn't working as you want to, you got the following:

SELECT EQUIPO.EQUI_SERIE
FROM EQUIPO
     LEFT JOIN ASIGNACION ON EQUIPO.EQUI_SERIE = ASIGNACION.EQUI_SERIE
WHERE ASIGNACION.ASIG_ACTIVA = 'No'
ORDER BY EQUIPO.EQUI_SERIE ASC;

The problem over here is that the where clause is filtering those values that have ASIGNACION.ASIG_ACTIVA as NULL (based on the LEFT JOIN), and as I understood you need the records with NULL or "No" value. So you can use the solutions given above, or just add to the where clause: "OR ASIGNACION.ASIG_ACTIVA IS NULL", getting the following:

SELECT EQUIPO.EQUI_SERIE
FROM EQUIPO
     LEFT JOIN ASIGNACION ON EQUIPO.EQUI_SERIE = ASIGNACION.EQUI_SERIE
WHERE ASIGNACION.ASIG_ACTIVA = 'No' OR ASIGNACION.ASIG_ACTIVA IS NULL
ORDER BY EQUIPO.EQUI_SERIE ASC;

Upvotes: 0

Thom A
Thom A

Reputation: 95589

Guessing that ASIG_ACTIVA is a column in the table ASIGNACION not EQUIPO (this is why aliases are important). if so, move the WHERE to the ON

SELECT E.EQUI_SERIE
FROM EQUIPO E
     LEFT JOIN ASIGNACION A ON E.EQUI_SERIE = A.EQUI_SERIE
                           AND A.ASIG_ACTIVA = 'No'
ORDER BY E.EQUI_SERIE ASC;

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

change join condition add ASIG_ACTIVA = 'No' on join

SELECT EQUIPO.EQUI_SERIE
FROM EQUIPO
     LEFT JOIN ASIGNACION ON EQUIPO.EQUI_SERIE = ASIGNACION.EQUI_SERIE
 and ASIG_ACTIVA = 'No'

ORDER BY EQUI_SERIE ASC;

Upvotes: 2

Related Questions