Problems Selecting With A Left Join

[Shadowing]

Hey guys im trying to select a row from table planets where there are no matches between

planet.address and travel.defender_planet

 

I dont think im using inner join correctly at all here

i'm thinking when you want a return result it wont work when there are not two columns matching in two tables

 

 

SELECT planets.address FROM planets LEFT JOIN travel ON planets.address != travel.defender_planet WHERE planets.address != travel.defender_planet LIMIT 1

 

 

My end objective is to let me select a row from planets.address where there are no rows in travel table with that address

[Jessica]

You're not using an inner join, you're using a left join.

Try this.

SELECT planets.address 
FROM planets 
LEFT JOIN travel 
ON planets.address = travel.defender_planet 
WHERE travel.defender_planet IS NULL

[Shadowing]

oh thanks alot Jessica

 

so it selects the row that comes up Null interesting

[Shadowing]

what i really wanted to do was use a left join in a update. so im updating the row in the same query and also comparing to make sure the row im selecting isnt matching in the travel table.

 

something like this but syntax is incorrect

 

UPDATE planets SET planets.id = 4005 LEFT JOIN travel ON planets.address = travel.defender_planet WHERE travel.defender_planet IS NULL

[Jessica]

What is id? Sounds like something you shouldn't be updating...

[Shadowing]

places the players id on the planet to show he owns it

 

This script doesnt get used much maybe i should just do this with two seperate queries

 

Thought it would be kewl if it was combinable.

 

I just started with using joins on my scripts so pretty new to it.

Edited October 1, 2012 by Shadowing

[Christian F.]

Sounds like the field should be named "owner_id" instead of just "id" then, since "id" tends to be the primary key and identifier for each individual record (planets in this case). Which is what Jessica was reacting to.

[Jessica]

If you're getting a syntax error, post it.

 

That query however will assign that id to EVERY matching row. Which I doubt is what you want.

[Barand]

I think yuou had the clauses in the wrong order.

 

Try

 

UPDATE planets
LEFT JOIN travel ON planets.address = travel.defender_planet
SET planets.id = 4005  
WHERE travel.defender_planet IS NULL