First of all, outer joins are for instances where info is being looked up on another table and you want rows returned even though nothing was matched for a particular id, e.g.:
create table food (
id int unsigned primary key auto_increment,
food char(16),
yummy char(1),
animal char(16)
);
create table animals (
name char(16) primary key,
weight int unsigned
);
insert into food values
(NULL, 'apple', 'Y', 'human'),
(NULL, 'oranges', 'Y', 'human'),
(NULL, 'apple', 'N', 'dog'),
(NULL, 'oranges', 'N', 'dog'),
(NULL, 'apple', 'N', 'cat'),
(NULL, 'oranges', 'N', 'cat');
insert into animals values
('cat', 12),
('dog', 25);
table 'food'
id food yummy animal
--- ---- ----- ------
1 apple Y human
2 oranges Y human
3 apple N dog
4 oranges N dog
5 apple N cat
6 oranges N cat
table 'animals'
name weight
---- ------
Cat 12
Dog 25
Let's say you want a list of all the foods and the weight of the animals.
If you do:
select f.*, a.weight
from food f, animals a
where f.animal = a.name
In this case the result would be:
+----+---------+-------+--------+--------+
| id | food | yummy | animal | weight |
+----+---------+-------+--------+--------+
| 5 | apple | N | cat | 12 |
| 6 | oranges | N | cat | 12 |
| 3 | apple | N | dog | 25 |
| 4 | oranges | N | dog | 25 |
+----+---------+-------+--------+--------+
Notice that humans is missing, but if you want the humans to show up even
though there's no human entry in the 'animals' table you have to do an
outer join (aka LEFT [OUTER] JOIN in Mysql):
select f.*, a.weight
from food f
LEFT JOIN animals a on (f.animal=a.name)
In this case the result would be:
+----+---------+-------+--------+--------+
| id | food | yummy | animal | weight |
+----+---------+-------+--------+--------+
| 1 | apple | Y | human | NULL |
| 2 | oranges | Y | human | NULL |
| 3 | apple | N | dog | 25 |
| 4 | oranges | N | dog | 25 |
| 5 | apple | N | cat | 12 |
| 6 | oranges | N | cat | 12 |
+----+---------+-------+--------+--------+
Here are some more examples:
SELECT T.name, V.value, T.unit, T.id, A.Product_id
FROM attribute_type AS T
LEFT JOIN attribute AS A ON ( T.id = A.type_id AND A.Product_id = 21 )
LEFT JOIN attribute_value AS V ON ( A.value_id = V.id );
select j.id, j.job_id, d.NAMES, e.DETAILS1, j.file_name, j.job_owner, j.import_date
from db.summary_jobs j
left join db.EST e on (j.job_id=e.JOB_NUMBER)
left join db.DEB d on (e.DEBTOR=d.AC_NO)
order by j.job_id desc limit
5 comments:
How about this scenario
I have 3 tables namely Product, SalesDetails and PurchaseDetails
SalesDetails contains the QuantitySold for each product (it has multiple instances, say Code 'P1' was sold 4 times, so it has 4 instances in the Salesdetails table)
Same is true with PurchaseDetails
I want to create a summary of Products showing the Total Quantity Sold and Total Quantity Purchased.
What is the SQL statement do do it?
Additional info on my first query...
It is possible that a certain product was not sold nor bought.
If this is the scenario, the product must still be a part of the summary.
Any help for this?
Thankful for valuable information
Hi can u help in following :
i have two tables "users" and users_role". both have a field "activity_status__code".
If a user is assigned a "role" an entry is made in "user_role " table.I want to retrieve all records of userrs table even if there are no records in "users_role " for some users.one more thing is that if there is record in "user_role " for some user then it should b retrieved only if user_role.activity_status__code='30'
i really have a problem with mysql and the join function.
i have 3 tables, lets say bad_jokes, good_jokes and regular_jokes. but all of them have the same fields. i mean the fields or columns in each of them are: title (of the joke), body and date_entered. i actually have more fields, but it doesnt matter. what it matters is that all the 3 tables have the same field names. how can i make a query to select the last 3 jokes in each table, for example? or the jokes added in the last 3 days ( in all tables, of course ). it seems a little difficult to realise.
Post a Comment