Hi, i have a problem, could anybody help me?
I had 3 tables for example, the table would be...
1. Apple with the records and fields
----------------------------
| string | Year |
================
| a | 2002 |
| b | 2002 |
| c | 2002 |
| a | 2003 |
| b | 2003 |
| c | 2003 |
| d | 2003 |
| e | 2003 |
| f | 2003 |
===============
2. Grape
===============
| string | codeyear |
================
| a | 02 |
| b | 02 |
| c | 02 |
| a | 03 |
| b | 03 |
===============
3. Codes
=================
| codeyear | Year |
=================
| 01 | 2001 |
| 02 | 2002 |
| 03 | 2003 |
=================
The Apple table has a relation with Grape in "string", and Grape has a relation with Codes in "codeyear".
If I want to display the data from Grape and Codes with "inner join" relation ship and condition year=2003, I might use these commands:
"select grape.*,codes.year from grape
inner join codes on grape.codeyear = codes.codeyear
and codes.year = 2003"
and the result would be :
=========================
| string | codeyear | year |
=========================
| a | 03 | 2003 |
| b | 03 | 2003 |
=========================
Now what do I have to do if I want to display the data from Apple with left join relation ship to the above result and with conditions Apple.year=2003 and the string from the result is null,
so the data will give the result like this :
===================
| string | year |
===================
| c | 2003 |
| d | 2003 |
| e | 2003 |
| f | 2003 |
===================
I really appreciate to everyone who'd like to help and solve my problem.
Could mysql select 3 tables with nested "select" c
Moderator: General Moderators
Thank you very much for your help. But it looks like I failed to understand your answer.
What I mean is, I want the data from Apple which has year=2003 and not yet include in table Grape.
You see that the data in table Apple that have exact inner join with Grape and have year=2003 are
a and b
So we could notice that the data from Apple that are not in Grape and have the year=2003 are
c, d, e and f
Now, what can I do to get the above results?
The latest command that I used is:
"select apple.string, grape.string , codes.codeyear from codes inner join
grape on grape.codeyear = codes.codeyear
and codes.year = 2003 right join apple on apple.string = grape.string
and grape.string is null where apple.year = 2003"
But it seems that the results are very unexpected.
What I mean is, I want the data from Apple which has year=2003 and not yet include in table Grape.
You see that the data in table Apple that have exact inner join with Grape and have year=2003 are
a and b
So we could notice that the data from Apple that are not in Grape and have the year=2003 are
c, d, e and f
Now, what can I do to get the above results?
The latest command that I used is:
"select apple.string, grape.string , codes.codeyear from codes inner join
grape on grape.codeyear = codes.codeyear
and codes.year = 2003 right join apple on apple.string = grape.string
and grape.string is null where apple.year = 2003"
But it seems that the results are very unexpected.
Hi. Try this query ...
SELECT apple.*, grape.* FROM apple LEFT JOIN grape
ON apple.string=grape.string AND grape.codeyear='03'
WHERE apple.year='2003' AND grape.string IS null;
There is still one problem. You have to set the "grape.codeyear"
parameter (which is '03' in the query above) manually.
Or, use mysql user variable and do the query in 2 steps, example:
Step #1: save the '03' codeyear into @mycodeyear variable
Step #2: use the same query as above, but replace the '03' parameter with '@mycodeyear'
Code:
SELECT (@mycodeyear:=codeyear) FROM codes WHERE (year='2003');
SELECT apple.*, grape.* FROM apple LEFT JOIN grape
ON apple.string=grape.string AND grape.codeyear=@mycodeyear
WHERE apple.year='2003' AND grape.string IS null;
When you use the queries above, remember to set
the year parameter ('2003') on both queries.
MySQL v4.1 supports nested select query
but it's still alpha version.

SELECT apple.*, grape.* FROM apple LEFT JOIN grape
ON apple.string=grape.string AND grape.codeyear='03'
WHERE apple.year='2003' AND grape.string IS null;
There is still one problem. You have to set the "grape.codeyear"
parameter (which is '03' in the query above) manually.
Or, use mysql user variable and do the query in 2 steps, example:
Step #1: save the '03' codeyear into @mycodeyear variable
Step #2: use the same query as above, but replace the '03' parameter with '@mycodeyear'
Code:
SELECT (@mycodeyear:=codeyear) FROM codes WHERE (year='2003');
SELECT apple.*, grape.* FROM apple LEFT JOIN grape
ON apple.string=grape.string AND grape.codeyear=@mycodeyear
WHERE apple.year='2003' AND grape.string IS null;
When you use the queries above, remember to set
the year parameter ('2003') on both queries.
MySQL v4.1 supports nested select query
but it's still alpha version.