Menu
Ok, I have a problem with joining 2 tables (with INNER JOIN). First table containts categories list (ecc. for photo albums, or blog posts) while the second table contains "the data"
I tried like this:
SELECT galeries_id, galeries_title,
photos.photos_id, photos.photos_gal_id, photos.photos_link
FROM galeries
INNER JOIN photos
ON galeries.galeries_id=photos.photos_gal_id
GROUP BY photos_gal_id
This gives me fair result, joining my tables nicely, just as I want them to with one crucial exeption.
If table "photos" doesn't have a row which contains "photos_gal_id" (for example "2"), than it will NOT return any category (galeries_id, galeries_title) for that category even if it does egzist in galeries table.
It is logical because of:
ON galeries.galeries_id=photos.photos_gal_id
Now I need to adopt this part to show me even thoes which doesn't have a related row in the second table
The result I'm trying to get is:
galeries_id galeries_title photos_id photos_link
1 blabla 3 test.jpg
2 bla bla2
3 etata 5 test.jpg
I hope I explained it good enough :) Thank you..
928
When using left join, right join, full (outer) join, it is possible to return NULL value, while (inner) join, cross join will not return NUll value.
We can use 'cross join' without on condition. Cross join gives the result in cartesian product form. For instance, if in one table there are 3 records and another table has 2 records, then the first record will match with all the second table records. Then, the same process will be repeated for second record and so on.
Columns containing NULL do not match any values when you are creating an inner join and are therefore excluded from the result set.
If you use INNER JOIN without the ON clause (or if you use comma without a WHERE clause), the result is the same as using CROSS JOIN : a Cartesian product (every row of o1 paired with every row of o2 ).
To retain the rows from galeries with no matching ID in photos, you will need to join photos on galeries with LEFT JOIN instead of INNER JOIN:
SELECT galeries_id, galeries_title,
photos.photos_id, photos.photos_gal_id, photos.photos_link
FROM galeries
LEFT JOIN photos
ON galeries.galeries_id=photos.photos_gal_id
GROUP BY photos_gal_id
This will give you:
galeries_id galeries_title photos_id photos_link
1 blabla 3 test.jpg
2 bla bla2 NULL NULL
3 etata 5 test.jpg
And if you wish to replace NULL with an empty string, you can use:
SELECT
IFNULL(photos.photos_id, ''),
IFNULL(photos.photos_link, '')
Wouldn't it just be a
LEFT JOIN photos
Because you want the records reguardless of wether or not they are filled?
30
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
