Doubt with inner join

Question

Doubt with inner join

Navigation

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

1

I have a database with two tables. One is the coche table and the ruta table:

Table coche :

pk_coche    modelo      
-------------------
1           Modelo 1
2           Modelo 2 
3           Modelo 3

Table ruta :

pk_ruta     fk_coche    kms_inicio     kms_final
-------------------------------------------------
1           1              200.000        210.000
2           3              300            0

The column of kms_inicio refers to the kilometers with which a car starts the route, for example with 200.000 kms . As long as the column of kms_final is in 0 , it means that that car is en route.

Well, my problem comes when I create a sql statement so that I can return the cars that are not en route, so that there are not two cars in the same route. As you can see the car Modelo 2 , it is not en route so with my sentence if it appears, the problem is that Modelo 1 should appear since that car has finished its route. But with my sentence I only see Modelo 2 , which has not started any route. My sentence is this:

SELECT pk_coche, modelo FROM coche v 
left JOIN ruta r ON v.pk_coche=r.fk_coche 
WHERE r.pk_ruta IS NULL

Any help?

sql join

asked by Xerox 16.04.2018 в 12:02

source

3 answers

1

To get back the cars that are not en route, there must be records in the route table with kms_start to 0 or to null .

Check it out.

If not, you should change the meaning of the Query, from INNER JOIN, to LEFT JOIN. The LEFT JOIN would take out the cars even if they are not in the table ruta .

answered by 16.04.2018 в 12:43

1

If I understand correctly, you want the cars that are NOT en route. That is, they are not in the routes table.

select coche.pk_coche, ruta.fk_coche
from coche 
left join ruta on coche.pk_coche = ruta.fk_coche
where ruta.fk_coche is null

this link will be very helpful link

edit: As the partner says, you have to put IS NULL.

edit: After the clarifications:

/*Coches no en ruta*/
SELECT * FROM coche c 
LEFT JOIN ruta r ON c.pk_coche=r.fk_coche 
WHERE r.kms_final = 0 or  r.pk_ruta IS NULL;

answered by 16.04.2018 в 12:44

what is happening? Convert String to matrix

score 2 · Accepted Answer

Xerox, instead of = null you should use IS NULL ... otherwise it will not work.

In the test I've done, there are three cars, the one whose pk_coche equals 2 is not en route, while cars 1 and 3 are en route.

If you want to show the cars that are not en route, if the data is well inserted, this would suffice:

/*Coches no en ruta*/
SELECT * FROM coche c 
LEFT JOIN ruta r ON c.pk_coche=r.fk_coche 
WHERE r.pk_ruta IS NULL;

The result would be:

pk_coche    modelo      pk_ruta     fk_coche    kms_inicio       kms_final
2           Modelo 2    NULL        NULL        NULL             NULL

And to show the cars that are en route:

/*Coches en ruta*/
SELECT * FROM coche c 
LEFT JOIN ruta r ON c.pk_coche=r.fk_coche 
WHERE r.pk_ruta IS NOT NULL;

The result would be:

pk_coche    modelo      pk_ruta     fk_coche    kms_inicio     kms_final
1           Modelo 1    1           1           100            200
3           Modelo 3    2           3           300            800

You can see the full demo here: SEE DEMO IN REXTESTER