Could mysql select 3 tables with nested "select" c

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
sian hwa
Forum Newbie
Posts: 3
Joined: Wed Jul 23, 2003 1:49 am
Location: Indonesia
Contact:

Could mysql select 3 tables with nested "select" c

Post by sian hwa »

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.
User avatar
Stoker
Forum Regular
Posts: 782
Joined: Thu Jan 23, 2003 9:45 pm
Location: SWNY
Contact:

Post by Stoker »

You basically said it yourself.. and I believe you want the exisiting year-constraint to be done in WHERE rather than in the JOIN?

SELECT grape.*, codes.year
FROM grape
LEFT JOIN codes on grape.codeyear = codes.codeyear
WHERE apples.year = 2003 AND codes.year = 2003
sian hwa
Forum Newbie
Posts: 3
Joined: Wed Jul 23, 2003 1:49 am
Location: Indonesia
Contact:

Post by sian hwa »

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. :)
andis93
Forum Newbie
Posts: 1
Joined: Thu Jul 24, 2003 11:56 am

Post by andis93 »

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.
:wink:
sian hwa
Forum Newbie
Posts: 3
Joined: Wed Jul 23, 2003 1:49 am
Location: Indonesia
Contact:

Post by sian hwa »

Thank you very much for your help. I know I could count on you :wink:
Post Reply