Hi,
I have two tables:
foto and relationship_foto_location
They share the same idfoto
Now I want to extract records from the first one (foto) where idfoto is not in the second table (relationship_foto_location)
This is my query:
It doesn’t work at all.
Result is so strange.
https://www.campaniaturismo.org/admin/admin-foto-list.php#admintemplate (second tab - no luoghi)
Can anybody help me?
Thank you
You should try a left (or right as appropriate) to join all elements in the primary table to the secondary table. This will set the fields in join for the the secondary table which don’t match to null. Set a filter to where table.fielldname is Null, the query will return all records which have a join as null i.e. no match
Hi, and thank you for your attention.
Anyway I don’t understand.
I’ve tried left and right join but I don’t have the required result (I want records from the first table that are not in the second one).
I don’t understand what you say about null value.
you want something like:
SELECT * FROM tbl_foto RIGHT JOIN tbl_relazioni_foto_loughi on tbl_foto.idfoto = tbl_relazioni_foto_loughi.idfoto WHERE idrelazione_foto_loughi is Null
Sorry, that should be a left join i think
Uhmm, idrelazione_foto_loughi can’t be null. It’s the primary key in tbl_relazioni_foto_luoghi
Teodor. when you have a moment, can you give a look to this post?
Thank you
as it is a left join you will get all the records from table 1 and the linked records from table 2. where there is no match in table 2 then all the corresponding table 2 fields will be null.we identify them by testing for null. Thats how the query finds NOT IN
This infographic may help you understand SQL joins.
1 Like
Something like this?
Unfortunately it doesn’t run.
I see (example on the left top)
a key = b key.
In my qyery it is the opposit.
I don’t know if it’s important
but it’s no possible to something different
Pardon, I was wrong.
The right example is the second one, left side (A-B)
I don’t understand the clause WHERE
because the B key can never be NULL.
This is the deal.
I have two tables.
The first one, tbl_foto, contains information about fotos (id, url, heading, etc.)
The second table, tbl_relazioni_foto_luoghi, contains relations between fotos and cities, monuments, etc.
So each record in this table must have a relationID (autoincrement), a fotoID and a placeID.
Places are stored in another table so tbl_relazioni_foto_luoghi links together two other tables:
tbl_foto and tbl_places.
This way no IDcolumn can be NULL because the relation must have a fotoID and a PlaceID.
Anyway when I store a foto in tbl_foto, It’s no compulsory to add a relation too.
That’s way I want to know which fotos have a relation with a place and which don’t.
WRONG, IT CAN Null IN THE QUERY! That is the whole point of the LEFT join. you get all the records from the left table linked with EITHER a matching field in the other table or where there is no match the fields WILL BE NULL.
The same works in reverse, with a right join you get all records from the right table and either matching fields from the left or Null.
HOWEVER be aware if you select all fields (select *) from both tables and both tables have an identically named field, one of them must be given an alias to stop name conflicts
1 Like
It is exactly as Hyperbytes says, you can do it using the LEFT JOIN.
SELECT tbl_foto.*
FROM tbl_foto
LEFT JOIN tbl_relazioni_foto_loughi
ON tbl_foto.idfoto = tbl_relazioni_foto_loughi.idfoto
WHERE idrelazione_foto_loughi.idfoto IS NULL
The LEFT JOIN will return all records from the lefttable and JOIN that with the right table, when there is no match in the right table it will still have a result for the left part and the fields for the right table will be NULL. That is why the WHERE is there, it filters the results to show only the records that are not in the right table.
1 Like
Hi guys, thank you very much indeed. It runs well now.
Morover you both explain me a very important issue about join.
Really really thank you
1 Like
PS. @icalesca - you can paste images straight here in the forum editor. No need to link to those external screenshot links …
Thank you. Last info.
How can I save, or something like it, this conversation?
Click the 3 dots beside the reply button and select bookmark