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"?

On Apr 11, 12:29am, "Paul Lautman" <paul.laut...@btinternet.com>
wrote:
> Wat do you mean by "scripting tricks"?

As it is part of a php project, I can always solve it using php. But
I'd rather do all of it in sql...

Asteras Amaliadas wrote:
> On Apr 11, 12:29 am, "Paul Lautman" <paul.laut...@btinternet.com>
> wrote:
>> Wat do you mean by "scripting tricks"?
>
> As it is part of a php project, I can always solve it using php. But
> I'd rather do all of it in sql...

You just said that you are not doing it all in SQL as you are building the
queries dynamically using php???
You can't have it both ways.
Please explain what you mean?

On Apr 11, 1:30am, "Paul Lautman" <paul.laut...@btinternet.com>
wrote:
> You just said that you are not doing it all in SQL as you are building the
> queries dynamically using php???
> You can't have it both ways.
> Please explain what you mean?

I wrote an interactive php page. This page creates sql queries
according to my input, to select eg students who scored 5 or more in
BOTH French and Italian. So, ideally, a single SQL query would give me
the student lists. If I fail to do that, I will make smaller queries
and then combine their outputs in php, eg the best I managed so far
(with much external assistance) was:

SELECT s.name,s.gender,m.course,m.mark from Student s Inner join marks
m on (m.name=s.name AND m.course='French' and m.mark>=5) or
(m.name=s.name AND m.course='Italian' and m.mark>=5) where s.gender =
'Male'

which shows the names of the students who passed French OR Italian.
This gives me the following list:

name gender course mark
George Male Italian 8
John Male French 5
John Male Italian 7
Kenny Male French 5

In the worst case scenario, I will write a php script that will count
the occurences of each name and only pick up those who occur twice (in
that case John).

Asteras Amaliadas wrote:
> On Apr 11, 1:30 am, "Paul Lautman" <paul.laut...@btinternet.com>
> wrote:
>> You just said that you are not doing it all in SQL as you are
>> building the queries dynamically using php???
>> You can't have it both ways.
>> Please explain what you mean?
>
> I wrote an interactive php page. This page creates sql queries
> according to my input, to select eg students who scored 5 or more in
> BOTH French and Italian. So, ideally, a single SQL query would give me
> the student lists. If I fail to do that, I will make smaller queries
> and then combine their outputs in php, eg the best I managed so far
> (with much external assistance) was:
>
> SELECT s.name,s.gender,m.course,m.mark from Student s Inner join marks
> m on (m.name=s.name AND m.course='French' and m.mark>=5) or
> (m.name=s.name AND m.course='Italian' and m.mark>=5) where s.gender =
> 'Male'
>
> which shows the names of the students who passed French OR Italian.
> This gives me the following list:
>
> name gender course mark
> George Male Italian 8
> John Male French 5
> John Male Italian 7
> Kenny Male French 5
>
> In the worst case scenario, I will write a php script that will count
> the occurences of each name and only pick up those who occur twice (in
> that case John).

You are not making sense to me. This query is possible in SQL. However I do
not understand what you mean by "scripting tricks". Where are you drawing
the line between using scripting to create a query and a "scripting trick"?

After spending some time, I eventually found a MySQL command that does
exactly that I want:

SELECT MarksSummary.Name FROM Student, (SELECT Name, count( * ) AS
Number FROM Marks WHERE (Course = 'French' AND Mark >=5) OR
(Course = 'Italian' AND Mark >=5) GROUP BY Name) AS MarksSummary WHERE
MarksSummary.Number=2 AND Student.Name=MarksSummary.Name AND
Gender='Male'

The idea is to create a named nested query MarksSummary that selects
the Name and the named (grouped by Name) Number of each Name's
occurrences which satisfy the preconditions (in my case, that people
who pass the french or italian course). Then the only thing is to join
the two tables (Student and MarksSummary) and select only the Names
that there Name is 2 (pass both french and italian exams) and Gender
is Male.

I do not know how efficient my query is (if there is anything better,
please let me know), but at least I do not need to have a single table
Student having attributes like
Name, Gender, French_Mark, Italian_Mark, Russian_Mark, Spanish_Mark
because it would be full of NULL values. My table, as they are
designed, have no NULL values. Do you think that this is a more
efficient design (as there are many students, many courses and
students tend to take few courses)?

I would like to thank all the people who gave my hints on how to carry
out this task.

On 11 Apr, 09:29, Asteras Amaliadas <aste...@gmail.com> wrote:
> After spending some time, I eventually found a MySQL command that does
> exactly that I want:
>
> SELECT MarksSummary.Name FROM Student, (SELECT Name, count( * ) AS
> Number FROM Marks WHERE (Course = 'French' AND Mark >=5) OR
> (Course = 'Italian' AND Mark >=5) GROUP BY Name) AS MarksSummary WHERE
> MarksSummary.Number=2 AND Student.Name=MarksSummary.Name AND
> Gender='Male'
>
> The idea is to create a named nested query MarksSummary that selects
> the Name and the named (grouped by Name) Number of each Name's
> occurrences which satisfy the preconditions (in my case, that people
> who pass the french or italian course). Then the only thing is to join
> the two tables (Student and MarksSummary) and select only the Names
> that there Name is 2 (pass both french and italian exams) and Gender
> is Male.
>
> I do not know how efficient my query is (if there is anything better,
> please let me know), but at least I do not need to have a single table
> Student having attributes like
> Name, Gender, French_Mark, Italian_Mark, Russian_Mark, Spanish_Mark
> because it would be full of NULL values. My table, as they are
> designed, have no NULL values. Do you think that this is a more
> efficient design (as there are many students, many courses and
> students tend to take few courses)?
>
> I would like to thank all the people who gave my hints on how to carry
> out this task.

You should do this doing joins rather than sub-selects