Select data where ID not exist in table 2 (Beginner)

[webbhelp]

Hi!

 

I have 2 tables (See attached image).

 

I want to select all(') from the table variables, but I only want to get data where variables.ID doesn't exist in table translations.ID.

okey! So if the variables-ID does not exist in the table-translation:Variable, den I want to get it.

Also, there must have to be a where statement so I can choose too fetch variables with language (id) 3, or another integer.

 

I want this because I want to select all variables which hasn't been used in the translation table.

 

I have tried with innerjoin... and actually I have never used joins before, never had too. But now I think I have to, but my innerjoin didn't worked.

And because of my understanding in joins is small, I also tried left and right join but I din't get it too work.

 

It would by my knowledge be something like this.

 

SELECT variables.* FROM variables INNER JOIN translations ON variables.id != translations.id WHERE language = 3

 

But no.. I only get all variables even if the variable ID is in the translation.variable table.

 

Thanks in advance! I guess this is simple for you guys.

[Psycho]

SELECT *
FROM Variables
WHERE id NOT IN (SELECT variable FROM Translations)

You can also do this by JOINing the tables as you did above - you just took the wrong approach. You have to do a LEFT JOIN - else the records without a match to JOIN on will not be included. Then filter the records where there is a NULL value

SELECT Variables.*
FROM Variables
LEFT JOIN Translations ON Variables.id = Translations.variable
WHERE Translations.variable IS NULL

But, I believe the first example is a better solution.

 

 

 

I have tried with innerjoin... and actually I have never used joins before, never had too.

 

If you've never had to use JOINs then you're probably using your tables completely wrong.

Edited November 11, 2013 by Psycho

[webbhelp]

PERFECT! 

 

I used the first one, without joining, and it worked perfect.

Aha so INNER JOIN, did right the opposite of what I was trying to do, filter the none existing out?

 

Yeah... I agree with that statement, my databases haven't been so complexed, but I have learned a lot since then, and now I start to understand, and you help me a lot, thanks =)

[Psycho]

Aha so INNER JOIN, did right the opposite of what I was trying to do, filter the none existing out?

 

A normal, or INNER, JOIN will only return results where the records from the two tables being JOINed actually have a reference. So, the records from the Variables table that do not have any records in the Translations table will not be returned because there is nothing for them to JOIN on. A LEFT or RIGHT JOIN will return results even when there are no matches for the JOIN.

 

The best example I can come up with would be an "authors" table and a "books" table. Assume that there are some author records for which there are no records in the books table and that there are some book records with a NULL references to authors.

 

Normal/Inner JOIN

 

SELECT *
FROM authors
JOIN books ON authors.id = books.authorID

This will return the results of every author and their associated books. It will not return any authors that do not have books or any books that have no associated author

 

LEFT JOIN

 

SELECT *
FROM authors
LEFT JOIN books ON authors.id = books.authorID

This will return the results of every author and their associated books. Because it is a LEFT JOIN it will include ALL records from the LEFT table (i.e. authors) even if there is no matching record in the books table. That record will have NULL for all the values from that second table. It will not include any records from the books table that do not have a reference to an author

 

 

RIGHT JOIN

 

SELECT *
FROM authors
RIGHTJOIN books ON authors.id = books.authorID

This will return the results of every author and their associated books. Because it is a RIGHT JOIN it will include ALL records from the RIGHT table (i.e. books) even if there is no reference to an author id. That record will have NULL for all the values from the first/left table. It will not include records from the author table that do not have any associated books.

[webbhelp]

ohh I see! very good explanation!

This made me understand a lot more. Now I can see why I should use them.

I really appreciate your help and the time you took to write this =)

 

Thank you very much!