How to resolv null problems in relation tables?

Question

I have the following situation using BigQuery Tables.

I have a table with a field containing a string with codes:

Table A
| Model | codevalues | 
| -------- | ------- | 
| 658 | CC(1.3),CMB(GA),ECO(BPL7),L(L1) | 
| 851 | CC(1.3),CMB(DS) | 

I broke this field generating only 1 column for codevalues:

Table B
| Model | codevalues | 
| -------- | ------- | 
| 658 | CC(1.3) | 
| 658 | CMB(GA) | 
| 658 | ECO(BPL7) | 
| 658 | L(L1) | 
| 851 | CC(1.3) | 
| 851 | CMB(DS) | 

I have a second table with a from/to of codes and their respective columns:

Table C
|codevalues | Carburante | LivelloEcologia | CilindrataCc | LivelloAllestimento | FamigliaCambio | Numero Porte | Tipo Trazione | 
| -------- | -------- | -------- | -------- | -------- | -------- | -------- | ------- | 
| CC(1.0) | | | CC(1.0) | | | | |
| CC(1.3) | | | CC(1.3) | | | | |
| CC(1.4) | | | CC(1.4) | | | | |
| CC(1.8) | | | CC(1.8) | | | | |
| CMB(BZ) | CMB(BZ) | | | | | |
| CMB(DS) | CMB(DS) | | | | | |
| CMB(GA) | CMB(GA) | | | | | |
| ECO(BPL6) | | ECO(BPL6) | | | | | 
| ECO(BPL7) | | ECO(BPL7) | | | | | 
| ECO(BPL7P) | | ECO(BPL7P) | | | | | 
| ECO(BPL8) | | ECO(BPL8) | | | | | 
| L(L0) | | | | L(L0) | | | 
| L(L1) | | | | L(L1) | | | 
| L(L11) | | | | L(L11) | | | 

I select and group it to look like this:

Table D

| Modelo | Carburante | LivelloEcologia | CilindrataCc | LivelloAllestimento | FamigliaCambio | Numero Porte | Tipo Trazione | 
| ------ | -------- | -------- | -------- | -------- | -------- | -------- | ------- | 
|658| CMB(GA) | ECO(BPL7) | CC(1.3) | L(L1) | | | | |
|851 | CMB(DS) | | CC(1.3) | | | | | |

Finally I have one last table:

Table E

| Modelo | Volume | Carburante | LivelloEcologia | CilindrataCc | LivelloAllestimento | FamigliaCambio | Numero Porte | Tipo Trazione | 
| ------ | ------ | ------ | ------ | ------ | ------ | ------ | ------ | ------ |
| 658    |  1000  | CMB(DS) | | CC(1.3) | | | | |
| 658    |  5000  | CMB(GA) | ECO(BPL7) | CC(1.3) | L(L1) | | | |
| 658    |  6000  | CMB(GA) | ECO(BPL7P) | CC(1.3) | | | | | | |
| 851    |  2000  | CMB(DS) | | CC(1.3) | | | | | | |
| 851    |  9000  | CMB(GA) | | CC(1.3) | | | | | | |

When I do an inner join between tables D and E:

select e.Modelo, e.Volume 
  from e 
  inner join d on (
  d.Carburante          = e.Carburante         
  and d.LivelloEcologia     = e.LivelloEcologia    
  and d.CilindrataCc        = e.CilindrataCc       
  and d.LivelloAllestimento = e.LivelloAllestimento
  and d.FamigliaCambio      = e.FamigliaCambio     
  and d.Numero Porte        = e.Numero Porte       
  and d.Tipo Trazione       = e.Tipo Trazione      
  ) where d.modelo = 658

This doesn't give me any value. I checked and this is due to fields with null values ​​in D if I use OR instead of AND brings me records that I am not interested in.

I need this to only bring me records that contain this sequence of codes CC(1.3),CMB(GA),ECO(BPL7),L(L1) regardless of the other fields being null.

Answer 1

SELECT e.Modelo, e.Volume
FROM E
INNER JOIN D ON (
    (d.Carburante IS NULL OR d.Carburante = e.Carburante)
    AND (d.LivelloEcologia IS NULL OR d.LivelloEcologia = e.LivelloEcologia)
    AND (d.CilindrataCc IS NULL OR d.CilindrataCc = e.CilindrataCc)
    AND (d.LivelloAllestimento IS NULL OR d.LivelloAllestimento = e.LivelloAllestimento)
    AND (d.FamigliaCambio IS NULL OR d.FamigliaCambio = e.FamigliaCambio)
    AND (d.NumeroPorte IS NULL OR d.NumeroPorte = e.NumeroPorte)
    AND (d.TipoTrazione IS NULL OR d.TipoTrazione = e.TipoTrazione)
)
WHERE d.Modelo = 658;

This query checks for NULL values in the fields of table D and only applies the corresponding conditions when the field in D is not NULL. This way, it allows for matching records even when some fields in D are NULL.