>The only problem I see is when there are concurrent access to the table
>"livreTemp", e.g. when there are multiple simultaneous requests of that
>kind. Is there a better way for achieving this under mySQL 3?
Yes, concurrent access would be a problem. I think I have the answer using a
single query (see below).
I used the ObjectStar RDBMS which has temp (TEM) and session (SES) table
types. Both were specific to the client (a private memory as opposed to a
all user memory). This was very useful, but I've found no mention of it in
the mysql documentation.
// the 2 step query that works...
CREATE TABLE livreTemp (IDLivre int(11));
INSERT INTO livreTemp (IDLivre)
SELECT book.IDLivre
FROM livre book, livreEcritPar ecr, auteur aut
WHERE aut.Prenom like '%$firstName%'
AND aut.Nom like '%$lastName%'
AND ecr.IDAuteur = aut.IDAuteur
AND book.IDLivre = ecr.IDLivre;
SELECT book.IDLivre, aut.Prenom, aut.Nom
FROM livreTemp book, livreEcritPar ecr, auteur aut
WHERE ecr.IDLivre= book.IDLivre
AND aut.IDAuteur=ecr.IDAuteur;
// workout what tables and fields are used...
livre book book
IDLivre book id
livreEcritPar ecr book written by
IDAuteur author id
IDLivre book id
auteur aut author
IDAuteur author id
Prenom firstname
Nom lastName
// create table sql...
drop table if exists livre;
create table livre (
IDLivre int(11),
title varchar(50),
PRIMARY KEY (IDLivre)
);
drop table if exists livreEcritPar;
create table livreEcritPar (
IDAuteur int(11),
IDLivre int(11)
);
drop table if exists auteur;
create table auteur (
IDAuteur int(11),
Prenom varchar(50),
Nom varchar(50),
PRIMARY KEY (IDAuteur)
);
// create some test data...
insert into livre (IDLivre,title) values('1','howto: MySQL');
insert into livre (IDLivre,title) values('2','howto: PHP');
insert into livre (IDLivre,title) values('3','History of Tayport');
insert into livre (IDLivre,title) values('4','History of Perth');
insert into livre (IDLivre,title) values('5','British Politics v French
Politics');
insert into livreEcritPar (IDAuteur,IDLivre) values('1','1');
insert into livreEcritPar (IDAuteur,IDLivre) values('1','2');
insert into livreEcritPar (IDAuteur,IDLivre) values('1','3');
insert into livreEcritPar (IDAuteur,IDLivre) values('3','4');
insert into auteur (IDAuteur,Prenom,Nom) values('1','Matthew','Gold');
insert into auteur (IDAuteur,Prenom,Nom) values('2','Jim','Smith');
insert into auteur (IDAuteur,Prenom,Nom) values('3','Scott','Another');
// workout what the query has to return...
select all book.id's
from book, book written by, author
where author.first_name like ...
and author.last_name like ...
and book written by.id = author.id
and book.id = book written by.id
select book.id, author.first_name, author.last_name
from temp, book written by, author
where book written by.id = book.id
and author.id = book written by.id
// okay, but it in plain english?
select book.id, author.first_name, author.last_name
where author.first_name like ... and author.last_name like ...
// thoughts...
we don't need to look at the livre (book) table, because book id exists in
the livreEcritPar (book written by) table. now our query is just concerned
with two tables.
livreEcritPar (book written by)
and,
auteur (author)
// solution 1: english version...
select book written by.book id, author.first_name, author.last_name
from author, book written by
where author.first_name like ...
and author.last_name like ...
and book written by.author id = author.author id;
// solution 1: french version...
select livreEcritPar.IDLivre, auteur.Prenom, auteur.Nom
from auteur, livreEcritPar
where auteur.Prenom like 'Matthew%'
and auteur.Nom like '%'
and livreEcritPar.IDAuteur = auteur.IDAuteur;
+---------+---------+------+
| IDLivre | Prenom | Nom |
+---------+---------+------+
| 1 | Matthew | Gold |
| 2 | Matthew | Gold |
| 3 | Matthew | Gold |
+---------+---------+------+
3 rows in set (0.02 sec)
// more thoughts...
what if he actually wanted the book title? we'll need to look at all three
tables
// solution 2: english version...
select book.title, author.first_name, author.last_name
from author, book written by, book
where author.first_name like ...
and author.last_name like ...
and book written by.author id = author.author id
and book.book id = book written by.book id;
// solution 2: french version...
select livre.title, auteur.Prenom, auteur.Nom
from auteur, livreEcritPar, livre
where auteur.Prenom like 'Matthew%'
and auteur.Nom like '%'
and livreEcritPar.IDAuteur = auteur.IDAuteur
and livre.IDLivre = livreEcritPar.IDLivre;
+--------------------+---------+------+
| title | Prenom | Nom |
+--------------------+---------+------+
| howto: MySQL | Matthew | Gold |
| howto: PHP | Matthew | Gold |
| History of Tayport | Matthew | Gold |
+--------------------+---------+------+
3 rows in set (0.02 sec)
----- Original Message -----
From: "Pascal Délisle" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Sunday, August 03, 2003 8:02 PM
Subject: Re: Nested SELECT statements problem
Finally, I solved my problem by creating a temporary table that holds
the result of the subquery. So, it looks like this:
CREATE TABLE livreTemp (IDLivre int(11));
INSERT INTO livreTemp (IDLivre) SELECT book.IDLivre
FROM livre book, livreEcritPar ecr, auteur aut WHERE aut.Prenom like
'%$firstName%' AND aut.Nom like '%$lastName%' AND ecr.IDAuteur =
aut.IDAuteur AND book.IDLivre = ecr.IDLivre;
SELECT book.IDLivre, aut.Prenom, aut.Nom FROM livreTemp book,
livreEcritPar ecr, auteur aut WHERE ecr.IDLivre= book.IDLivre AND
aut.IDAuteur=ecr.IDAuteur;
DROP TABLE livreTemp;
The only problem I see is when there are concurrent access to the table
"livreTemp", e.g. when there are multiple simultaneous requests of that
kind. Is there a better way for achieving this under mySQL 3?
Le dimanche, 3 aoû 2003, à 12:41 Canada/Eastern, Pascal Délisle a écrit
:
> Thanks for your input!
>
> First, I removed the quotation marks into the sub-query in order to
> fix syntax. Then, I tried to use different alliases from the main
> query. However, this doesn't work. I mean that mySQL return a syntax
> error. I checked the server version and it is 3.23.56. As someone
> else noticed, nested queries are not supported in mySQL until version
> 4 or so. Therefore, I assume that it would not work for me. Since
> I'm not the administrator of the mySQL server, I'm not able to upgrade
> it. So, I'm stucked with version 3.23.56.
>
> Now, someone suggested me to replace subqueries with something like
> this: http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html .
> However, I'm not able to figure out how to change my queries. Any
> idea?
>
>
>
>
> Le dimanche, 3 aoû 2003, à 01:07 Canada/Eastern, Lin Yu a écrit :
>
>> One problem is that you have quoted your "sub-query", which makes it
>> to return a
>> constant string.
>>
>> Another problem I saw in your code is that you used the same aliases
>> for tables
>> in the query and in the sub-query. In such case, the SQL parser
>> would take all
>> of them to refer to the same table, probably the ones in the query. My
>> suggestion would be to use different aliases in the query and
>> sub-query for the
>> same table. That way, in each of your where-clause, the SQL parser
>> will know
>> exactly which table reference you want.
>>
>> Also, be sure that your data is good so that your sub-query indeed
>> returns some
>> records to be matched; or otherwise the query will not return
>> anything.
>>
>> Hope this helps.
>> ________________________
>> Lin
>> -----Original Message-----
>> From: Pascal Délisle [mailto:[EMAIL PROTECTED]
>> Sent: Saturday, August 02, 2003 11:46 PM
>> To: [EMAIL PROTECTED]
>> Subject: Nested SELECT statements problem
>>
>> Hi!
>>
>> I try to figure out how to use a nested "SELECT" statement after the
>> "IN" predicate. For example, when I try this code, it doesn't return
>> anything although it should:
>>
>>
>> SELECT book.IDLivre, aut.Prenom, aut.Nom FROM livre book,
>> livreEcritPar
>> ecr, auteur aut WHERE ecr.IDLivre book.IDLivre AND aut.IDAuteur
>> ecr.IDAuteur AND book.IDLivre IN ("SELECT book.IDLivre FROM livre >
>> book,
>> livreEcritPar ecr, auteur aut WHERE aut.Prenom like '%$firstName%' AND
>> aut.Nom like '%$name%' AND ecr.IDAuteur aut.IDAuteur AND book.IDLivre
>> ecr.IDLivre");
>>
>>
>> So, my question is the following: How should I change syntax in order
>> to make this bunch of code work? I mean, under Oracle SQL, this
>> syntax
>> would be legal and work perfectly, so I'm confused how to solve my
>> problem.
>>
>> Thanks in advance!
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>> http://lists.mysql.com/[EMAIL PROTECTED]
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.506 / Virus Database: 303 - Release Date: 01/08/2003
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
