WHERE IN query?

Question

With the next database:

I need to select all soldiers who have been ONLY in battles in their own planet and I don't have any idea of how can I make it.

This is what I've tried.

SELECT id_soldier, name 
FROM soldier, battle_log, battle 
WHERE IN(...soldier.planet_id = battle.id_planet_battle...)

score 2 · Answer 1 · answered Oct 01 '15 at 08:58

2

Since I don't have access to the db I can't test it but can you give this a try?

SELECT id_soldier, name 
FROM soldier s 
LEFT JOIN battle_log l ON s.id_soldier = l.soldier_id 
LEFT JOIN battle b ON l.battle_id = b.id_battle 
WHERE s.planet_id = b.id_planet_battle

If it returns an error just let me know and we'll try to fix it.

answered Oct 01 '15 at 08:58

nowhere

1,558
1
12
31

@javipedrera Glad to help – nowhere Oct 01 '15 at 09:30

score 2 · Answer 2 · answered Oct 01 '15 at 09:00

2

You can use the not exists operator to exclude soldiers who participated in battles on other planets:

SELECT *
FROM   soldier s
WHERE  NOT EXISTS (SELECT *
                   FROM   battle b
                   JOIN   battle_log bl ON b.id_battle = bl.battle_id
                   WHERE  bl.soldier_id = s.id_soldier AND
                          b.id_planet_battle != s.planet_id)

answered Oct 01 '15 at 09:00

Mureinik

297,002
52
306
350

Thanks, this works. You helped me a lot :D – therealbigpepe Oct 01 '15 at 09:29

score 1 · Answer 3 · answered Oct 01 '15 at 09:04

1

I think you can use simple query and join this step.

If I understand correctly soldier join planet (soldier have own planet) Then join Battle by planet (because planet that soldier have it's have battle)

answered Oct 01 '15 at 09:04

Chanom First

1,136
1
11
25

You have a shorter path if you join soldier with battle_log and finally battle ;) – nowhere Oct 01 '15 at 09:31
Ah... In fact my flow have only 3 main entity (soldier,planet and battle) equal your flow :) – Chanom First Oct 01 '15 at 09:35
But if you don't add battle_log how do you know if the soldier fought a battle on a certain planet? – nowhere Oct 01 '15 at 09:39

WHERE IN query?

3 Answers3