Hi,

I have those two tables:

CREATE TABLE Student (
Name INTEGER UNSIGNED NOT NULL,
Gender ENUM('Male','Female') NOT NULL,
PRIMARY KEY(Name)
);

+--------+--------+
| Name | Gender |
+--------+--------+
| John | Male |
| Tom | Male |
| Mary | Female |
| George | Male |
| Kenny | Male |
+--------+--------+

CREATE TABLE Marks (
Name VARCHAR(50) NOT NULL,
Course VARCHAR(500) NOT NULL,
Mark INTEGER UNSIGNED NULL
PRIMARY KEY(Name, Course)
);

+--------+---------+------+
| Name | Course | Mark |
+--------+---------+------+
| John | French | 5 |
| John | Italian | 7 |
| John | Russian | 3 |
| Tom | Russian | 9 |
| Mary | French | 7 |
| Mary | Spanish | 3 |
| Mary | Italian | 9 |
| George | French | 3 |
| George | Russian | 6 |
| George | Italian | 8 |
| Kenny | French | 5 |
| Kenny | Italian | 3 |
+--------+---------+------+

How do I select the boys who passed (scored 5 or higher) the French
and the Italian course? In that case the search result is "John".

Thank you.

On 10 Apr, 14:30, Asteras Amaliadas <aste...@gmail.com> wrote:
> Hi,
>
> I have those two tables:
>
> CREATE TABLE Student (
> Name INTEGER UNSIGNED NOT NULL,
> Gender ENUM('Male','Female') NOT NULL,
> PRIMARY KEY(Name)
> );
>
> +--------+--------+
> | Name | Gender |
> +--------+--------+
> | John | Male |
> | Tom | Male |
> | Mary | Female |
> | George | Male |
> | Kenny | Male |
> +--------+--------+
>
> CREATE TABLE Marks (
> Name VARCHAR(50) NOT NULL,
> Course VARCHAR(500) NOT NULL,
> Mark INTEGER UNSIGNED NULL
> PRIMARY KEY(Name, Course)
> );
>
> +--------+---------+------+
> | Name | Course | Mark |
> +--------+---------+------+
> | John | French | 5 |
> | John | Italian | 7 |
> | John | Russian | 3 |
> | Tom | Russian | 9 |
> | Mary | French | 7 |
> | Mary | Spanish | 3 |
> | Mary | Italian | 9 |
> | George | French | 3 |
> | George | Russian | 6 |
> | George | Italian | 8 |
> | Kenny | French | 5 |
> | Kenny | Italian | 3 |
> +--------+---------+------+
>
> How do I select the boys who passed (scored 5 or higher) the French
> and the Italian course? In that case the search result is "John".
>
> Thank you.

Why have you titled this LEFT JOIN?

What makes you think the answer should be a LEFT JOIN?

What have you already tried?

I admit that I am not sure what the right answer is. I thought left
join could be an answer. Ideally, out of those two tables I want to
create a table like that:

+--------+--------+--------+---------+---------+---------+
| Name | Gender | French | Italian | Russian | Spanish |
+--------+--------+--------+---------+---------+---------+
| John | Male | 5 | 7 | NULL | NULL |
| Tom | Male | NULL | NULL | 9 | NULL |
| Mary | Female | 7 | 9 | NULL | 3 |
| George | Male | 3 | 8 | 6 | NULL |
| Kenny | Male | 5 | 3 | NULL | NULL |
+--------+--------+--------+---------+---------+---------+

It is then piece of cake to select the male students who scored >=5 in
French and Italian. This means that I could extent that to many
courses, not just 2.

On 10 Apr, 15:14, Asteras Amaliadas <aste...@gmail.com> wrote:
> I admit that I am not sure what the right answer is. I thought left
> join could be an answer. Ideally, out of those two tables I want to
> create a table like that:
>
> +--------+--------+--------+---------+---------+---------+
> | Name | Gender | French | Italian | Russian | Spanish |
> +--------+--------+--------+---------+---------+---------+
> | John | Male | 5 | 7 | NULL | NULL |
> | Tom | Male | NULL | NULL | 9 | NULL |
> | Mary | Female | 7 | 9 | NULL | 3 |
> | George | Male | 3 | 8 | 6 | NULL |
> | Kenny | Male | 5 | 3 | NULL | NULL |
> +--------+--------+--------+---------+---------+---------+
>
> It is then piece of cake to select the male students who scored >=5 in
> French and Italian. This means that I could extent that to many
> courses, not just 2.

Is there a finite number of courses?
You appear to be looking at a crosstab

On 10 Áðñ, 17:40, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> Is there a finite number of courses?
> You appear to be looking at a crosstab

In the real thing, there are 20 of such courses... And even worse,
there are more tables connected to "Student" like
"Marks" ("Studentships", "Activities", etc) and I have to select
students out of all those tables. Total mess!

On 10 Apr, 15:56, Asteras Amaliadas <aste...@gmail.com> wrote:
> On 10 Áðñ, 17:40, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
> > Is there a finite number of courses?
> > You appear to be looking at a crosstab
>
> In the real thing, there are 20 of such courses... And even worse,
> there are more tables connected to "Student" like
> "Marks" ("Studentships", "Activities", etc) and I have to select
> students out of all those tables. Total mess!

Are you building the queries dynamically using something like php?

On Apr 10, 6:44pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> Are you building the queries dynamically using something like php?
Yes... But I'd rather not use any scripting tricks...

Asteras Amaliadas wrote:
> On Apr 10, 6:44 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>> Are you building the queries dynamically using something like php?
> Yes... But I'd rather not use any scripting tricks...

Wat do you mean by "scripting tricks"?