Could mysql select 3 tables with nested "select" c
Posted: Wed Jul 23, 2003 1:49 am
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.
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.