RE: [PHP-DB] Left Join is producing duplicate results - MySQL & relational tables
Mike--
I hate to suggest quick fixes :) but if all else fails, you can add a
DISTINCT to the query. I've had to do it before...
If you keep running into problems, and EXPLAIN ... doesn't resolve them,
then perhaps you could send us a dump of the db structure...
# mysqldump -d [database_name] > dumpfile.sql
Or if you need a UN/PW:
# mysqldump -d [database_name] -u[username] -p > dumpfile.sql
That will dump the structure of the db, but not the actual data.
Good luck!
-Original Message-
From: Mike Gifford [mailto:[EMAIL PROTECTED]]
Sent: Thursday, July 12, 2001 10:48 AM
To: Matthew Loff
Cc: 'Dobromir Velev'; [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: [PHP-DB] Left Join is producing duplicate results - MySQL &
relational tables
Hi Matthew,
Matthew Loff wrote:
> What about...
> SELECT WLPbib.bibID,
> WLPbib.title,
> WLPbib.publisher,
> WLPbib.publicationDate,
> WLPaddress.city,
> WLPaddress.state,
> WLPprofile.firstName,
> WLPprofile.lastName,
> WLPprofile.organization,
> WLPcountry.languageName
> FROM WLPprofile, WLPaddress, WLPcountry
> WHERE WLPprofile.profileID = WLPbib.profileID
> AND WLPaddress.publisherID = WLPbib.publisherID
> AND WLPcountry.countryID = WLPaddress.countryID;
I think that this is working now.
> I had a similar problems with a database I was working with... It had
> a main table with 29,000 listings, and I decided to normalize it to
> improve query times (split the records into diff. tables, one for each
> attribute of the record, associate the records back together by a
> common ID than spanned all the tables)...
This one will likely have 10,000 to start with, so it is good to build
in
normalization... Didn't even know what the term normalization referred
to
earlier today. However, that is what I was doing with the table.
> I ended up with an SQL query that
> spanned like 10 tables-- but it was -way- faster than one big table.
> There's an excellent article on Normalization on PHPbuilder --
> http://www.phpbuilder.com/columns/barry2731.php3
This is a good article. Even addressed the question that I fired off to
Dobromir about linking various profiles to the same bibliography.
One person can write many articles & an article can have many authors,
so I need
to create a many-many table to link the articles to profiles.
I think that this wil work:
CREATE TABLE WLParticle2profile (
a2pID mediumint(9) NOT NULL auto_increment,
bibID mediumint(9),
profileID mediumint(9),
PRIMARY KEY (a2pID)
)
I'll then need to Re-jig the WHERE command to limit the number of
returns...
This would become
SELECT WLPbib.bibID,
WLPbib.title,
WLPbib.publisher,
WLPbib.publicationDate,
WLPaddress.city,
WLPaddress.state,
WLPprofile.firstName,
WLPprofile.lastName,
WLPprofile.organization,
WLPcountry.languageName
FROMWLPprofile, WLPaddress, WLPcountry, WLParticle2profile
WHERE WLParticle2profile.profileID = WLPbib.profileID
AND WLPaddress.publisherID = WLPbib.publisherID
AND WLPcountry.countryID = WLPaddress.countryID;
I'm not sure this will work to tie in the relational table
and then we come back to the problem with duplicate entries again...
> If your WLP tables are very large, you may want to try using mySQL's
> EXPLAIN SELECT [rest of select query]... function to figure out the
> best
> (read: efficient) ways of performing this query... Indexes are
> definitely a must if you are dealing with a lot of rows... If not, you
> should be just fine with the above query-- which I -think- is
> equivilent...
I didn't know about this option:
http://www.mysql.com/doc/E/X/EXPLAIN.html
I find the MySQL.com site to be hard to read through.. php.net is much
easier
to understand in my experience.
> I don't have a ton of experience with SQL, so perhaps someone can
> better elaborate.
I think that you did a good job.. However I think I'm still stuck with
the same
duplicate error now (well when I've expanded the code.
Mike
> -----Original Message-
> From: Dobromir Velev [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, July 12, 2001 3:01 AM
> To: [EMAIL PROTECTED]; Mike Gifford
> Subject: Re: [PHP-DB] Left Join is producing duplicate results - MySQL
&
> relational tables
>
>
> Hi,
>
> Did you try to use something like this.
>
> mysql_query("SELECT DISTINCT
>WLPbib.bibID,
> WLPbib.title,
> WLPbib.publisher,
> WLPbib.publicationDate,
> WLPaddress.city,
> WLPaddress.state,
> WLPprofile.firstName,
> WLPprofile.lastName,
> WLPprofil
Re: [PHP-DB] Left Join is producing duplicate results - MySQL & relational tables
Hi Matthew,
Matthew Loff wrote:
> What about...
> SELECT WLPbib.bibID,
> WLPbib.title,
> WLPbib.publisher,
> WLPbib.publicationDate,
> WLPaddress.city,
> WLPaddress.state,
> WLPprofile.firstName,
> WLPprofile.lastName,
> WLPprofile.organization,
> WLPcountry.languageName
> FROM WLPprofile, WLPaddress, WLPcountry
> WHERE WLPprofile.profileID = WLPbib.profileID
> AND WLPaddress.publisherID = WLPbib.publisherID
> AND WLPcountry.countryID = WLPaddress.countryID;
I think that this is working now.
> I had a similar problems with a database I was working with... It had a
> main table with 29,000 listings, and I decided to normalize it to
> improve query times (split the records into diff. tables, one for each
> attribute of the record, associate the records back together by a common
> ID than spanned all the tables)...
This one will likely have 10,000 to start with, so it is good to build in
normalization... Didn't even know what the term normalization referred to
earlier today. However, that is what I was doing with the table.
> I ended up with an SQL query that
> spanned like 10 tables-- but it was -way- faster than one big table.
> There's an excellent article on Normalization on PHPbuilder --
> http://www.phpbuilder.com/columns/barry2731.php3
This is a good article. Even addressed the question that I fired off to
Dobromir about linking various profiles to the same bibliography.
One person can write many articles & an article can have many authors, so I need
to create a many-many table to link the articles to profiles.
I think that this wil work:
CREATE TABLE WLParticle2profile (
a2pID mediumint(9) NOT NULL auto_increment,
bibID mediumint(9),
profileID mediumint(9),
PRIMARY KEY (a2pID)
)
I'll then need to Re-jig the WHERE command to limit the number of returns...
This would become
SELECT WLPbib.bibID,
WLPbib.title,
WLPbib.publisher,
WLPbib.publicationDate,
WLPaddress.city,
WLPaddress.state,
WLPprofile.firstName,
WLPprofile.lastName,
WLPprofile.organization,
WLPcountry.languageName
FROMWLPprofile, WLPaddress, WLPcountry, WLParticle2profile
WHERE WLParticle2profile.profileID = WLPbib.profileID
AND WLPaddress.publisherID = WLPbib.publisherID
AND WLPcountry.countryID = WLPaddress.countryID;
I'm not sure this will work to tie in the relational table
and then we come back to the problem with duplicate entries again...
> If your WLP tables are very large, you may want to try using mySQL's
> EXPLAIN SELECT [rest of select query]... function to figure out the best
> (read: efficient) ways of performing this query... Indexes are
> definitely a must if you are dealing with a lot of rows... If not, you
> should be just fine with the above query-- which I -think- is
> equivilent...
I didn't know about this option:
http://www.mysql.com/doc/E/X/EXPLAIN.html
I find the MySQL.com site to be hard to read through.. php.net is much easier
to understand in my experience.
> I don't have a ton of experience with SQL, so perhaps someone can better
> elaborate.
I think that you did a good job.. However I think I'm still stuck with the same
duplicate error now (well when I've expanded the code.
Mike
> -Original Message-----
> From: Dobromir Velev [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, July 12, 2001 3:01 AM
> To: [EMAIL PROTECTED]; Mike Gifford
> Subject: Re: [PHP-DB] Left Join is producing duplicate results - MySQL &
> relational tables
>
>
> Hi,
>
> Did you try to use something like this.
>
> mysql_query("SELECT DISTINCT
>WLPbib.bibID,
> WLPbib.title,
> WLPbib.publisher,
> WLPbib.publicationDate,
> WLPaddress.city,
> WLPaddress.state,
> WLPprofile.firstName,
> WLPprofile.lastName,
> WLPprofile.organization,
> WLPcountry.languageName
> FROM ((WLPbib
> LEFT JOIN WLPprofile ON WLPprofile.profileID =
> WLPbib.profileID)
> LEFT JOIN WLPaddress ON WLPaddress.publisherID =
> WLPbib.publisherID)
> LEFT JOIN WLPcountry ON WLPcountry.countryID =
> WLPaddress.countryID");
>
> The other thing that may help is to rearange the order of the tables in
> the FROM clause. Please check if some of the joins return more than one
> result - if you have more than one address for a publisher the query
> will return one row for every address.
>
> Hope this helps
> Dobromir Velev
>
>
> -Original Message-
> From: Mike Gifford <[EMAIL PROTECTED]>
> To: [EMAIL PROTECTED] <[EMA
Re: [PHP-DB] Left Join is producing duplicate results - MySQL & relational tables
Hello Dobromir,
Dobromir Velev wrote:
> Did you try to use something like this.
> mysql_query("SELECT DISTINCT
>WLPbib.bibID,
The only item that I want to have distinct is the first one. However, I think
that Matthew's response (to your response) is working now.
> The other thing that may help is to rearange the order of the tables in the
> FROM clause. Please check if some of the joins return more than one result -
> if you have more than one address for a publisher the query will return one
> row for every address.
Actually, I'd like to have more than one record (WLPprofile rather than WLPaddress in
this case) per bibliography item (WLPbib)
You see a book can have many authors, but I'm not sure how to build the tables
so that I can link to multiple authors.. Any sugestions? Right now there is
one profileID listed. I don't know how to organize the data so that
WLPbib.profileID can equal WLPprofile.profileID=3, WLPprofile.profileID=7, &
WLPprofile.profileID=10232.
Would Matthew's suggestion work in places where there are multiple profiles
(authors) referred to within the bib table?
Mike
--
Mike Gifford, OpenConcept Consulting, http://openconcept.ca
Offering everything your organization needs for an effective web site.
Abolish Nuclear Weapons Now!: http://pgs.ca/petition/
It is a miracle that curiosity survives formal education. - A Einstein
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] Left Join is producing duplicate results - MySQL & relational tables
What about...
SELECT WLPbib.bibID,
WLPbib.title,
WLPbib.publisher,
WLPbib.publicationDate,
WLPaddress.city,
WLPaddress.state,
WLPprofile.firstName,
WLPprofile.lastName,
WLPprofile.organization,
WLPcountry.languageName
FROMWLPprofile, WLPaddress, WLPcountry
WHERE WLPprofile.profileID = WLPbib.profileID
AND WLPaddress.publisherID = WLPbib.publisherID
AND WLPcountry.countryID = WLPaddress.countryID;
I had a similar problems with a database I was working with... It had a
main table with 29,000 listings, and I decided to normalize it to
improve query times (split the records into diff. tables, one for each
attribute of the record, associate the records back together by a common
ID than spanned all the tables)... I ended up with an SQL query that
spanned like 10 tables-- but it was -way- faster than one big table.
There's an excellent article on Normalization on PHPbuilder --
http://www.phpbuilder.com/columns/barry2731.php3
If your WLP tables are very large, you may want to try using mySQL's
EXPLAIN SELECT [rest of select query]... function to figure out the best
(read: efficient) ways of performing this query... Indexes are
definitely a must if you are dealing with a lot of rows... If not, you
should be just fine with the above query-- which I -think- is
equivilent...
I don't have a ton of experience with SQL, so perhaps someone can better
elaborate.
Best of luck!
-Original Message-
From: Dobromir Velev [mailto:[EMAIL PROTECTED]]
Sent: Thursday, July 12, 2001 3:01 AM
To: [EMAIL PROTECTED]; Mike Gifford
Subject: Re: [PHP-DB] Left Join is producing duplicate results - MySQL &
relational tables
Hi,
Did you try to use something like this.
mysql_query("SELECT DISTINCT
WLPbib.bibID,
WLPbib.title,
WLPbib.publisher,
WLPbib.publicationDate,
WLPaddress.city,
WLPaddress.state,
WLPprofile.firstName,
WLPprofile.lastName,
WLPprofile.organization,
WLPcountry.languageName
FROM ((WLPbib
LEFT JOIN WLPprofile ON WLPprofile.profileID =
WLPbib.profileID)
LEFT JOIN WLPaddress ON WLPaddress.publisherID =
WLPbib.publisherID)
LEFT JOIN WLPcountry ON WLPcountry.countryID =
WLPaddress.countryID");
The other thing that may help is to rearange the order of the tables in
the FROM clause. Please check if some of the joins return more than one
result - if you have more than one address for a publisher the query
will return one row for every address.
Hope this helps
Dobromir Velev
-Original Message-
From: Mike Gifford <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED] <[EMAIL PROTECTED]>
Date: Thursday, July 12, 2001 4:31 AM
Subject: [PHP-DB] Left Join is producing duplicate results - MySQL &
relational tables
>Hello,
>
>I posted this to the general list this morning & got a couple of good
leads, but
>they weren't able to actually fix the problem, so I'm posting here to
>the
db list.
>
>I'm making some headway on joining three MySQL tables.
>
>However, when I run this query:
>
>mysql_query("SELECT
> WLPbib.bibID,
> WLPbib.title,
> WLPbib.publisher,
> WLPbib.publicationDate,
> WLPaddress.city,
> WLPaddress.state,
> WLPprofile.firstName,
> WLPprofile.lastName,
> WLPprofile.organization,
> WLPcountry.languageName
> FROM WLPbib
> LEFT JOIN WLPprofile ON WLPprofile.profileID =
WLPbib.profileID
> LEFT JOIN WLPaddress ON WLPaddress.publisherID =
WLPbib.publisherID
> LEFT JOIN WLPcountry ON WLPcountry.countryID =
WLPaddress.countryID");
>
>I now get results in triplicate. ie. I'm getting three copies of the
>same title, firstName, organization, etc
>
>I somehow suspected that this should be the result with LEFT JOIN, but
>I'm
not
>sure how to return a query without duplication.
>
>This is far better than what I had this morning (which was no response
>from
the
>server).
>
>Thanks. I'm new to joining tables...
>
>Someone wrote back suggesting that SELECT DISTINCT could be used to to
>the
job.
>
>Another person suggested that using UNIQUE(profileID) would make it
>look
nicer.
> I wasn't sure how to use UNIQUE with the last JOIN as it isn't
> directly
linked
>to WLPbib..
>
>Any suggestions would be useful.
>
>Mike
>
>--
>Mike Gifford, OpenConcept Consulting, http://openconcept.ca Offering
>everything your organization needs for an effective web site. Abolish
>Nuclear Weapons Now!: http://pgs.ca/petition/ It is a miracle that
>curiosity survives formal education. - A Einstein
>
>
>--
>P
Re: [PHP-DB] Left Join is producing duplicate results - MySQL & relational tables
Hi,
Did you try to use something like this.
mysql_query("SELECT DISTINCT
WLPbib.bibID,
WLPbib.title,
WLPbib.publisher,
WLPbib.publicationDate,
WLPaddress.city,
WLPaddress.state,
WLPprofile.firstName,
WLPprofile.lastName,
WLPprofile.organization,
WLPcountry.languageName
FROM ((WLPbib
LEFT JOIN WLPprofile ON WLPprofile.profileID = WLPbib.profileID)
LEFT JOIN WLPaddress ON WLPaddress.publisherID =
WLPbib.publisherID)
LEFT JOIN WLPcountry ON WLPcountry.countryID =
WLPaddress.countryID");
The other thing that may help is to rearange the order of the tables in the
FROM clause. Please check if some of the joins return more than one result -
if you have more than one address for a publisher the query will return one
row for every address.
Hope this helps
Dobromir Velev
-Original Message-
From: Mike Gifford <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED] <[EMAIL PROTECTED]>
Date: Thursday, July 12, 2001 4:31 AM
Subject: [PHP-DB] Left Join is producing duplicate results - MySQL &
relational tables
>Hello,
>
>I posted this to the general list this morning & got a couple of good
leads, but
>they weren't able to actually fix the problem, so I'm posting here to the
db list.
>
>I'm making some headway on joining three MySQL tables.
>
>However, when I run this query:
>
>mysql_query("SELECT
> WLPbib.bibID,
> WLPbib.title,
> WLPbib.publisher,
> WLPbib.publicationDate,
> WLPaddress.city,
> WLPaddress.state,
> WLPprofile.firstName,
> WLPprofile.lastName,
> WLPprofile.organization,
> WLPcountry.languageName
> FROM WLPbib
> LEFT JOIN WLPprofile ON WLPprofile.profileID = WLPbib.profileID
> LEFT JOIN WLPaddress ON WLPaddress.publisherID =
WLPbib.publisherID
> LEFT JOIN WLPcountry ON WLPcountry.countryID =
WLPaddress.countryID");
>
>I now get results in triplicate. ie. I'm getting three copies of the same
>title, firstName, organization, etc
>
>I somehow suspected that this should be the result with LEFT JOIN, but I'm
not
>sure how to return a query without duplication.
>
>This is far better than what I had this morning (which was no response from
the
>server).
>
>Thanks. I'm new to joining tables...
>
>Someone wrote back suggesting that SELECT DISTINCT could be used to to the
job.
>
>Another person suggested that using UNIQUE(profileID) would make it look
nicer.
> I wasn't sure how to use UNIQUE with the last JOIN as it isn't directly
linked
>to WLPbib..
>
>Any suggestions would be useful.
>
>Mike
>
>--
>Mike Gifford, OpenConcept Consulting, http://openconcept.ca
>Offering everything your organization needs for an effective web site.
>Abolish Nuclear Weapons Now!: http://pgs.ca/petition/
>It is a miracle that curiosity survives formal education. - A Einstein
>
>
>--
>PHP Database Mailing List (http://www.php.net/)
>To unsubscribe, e-mail: [EMAIL PROTECTED]
>For additional commands, e-mail: [EMAIL PROTECTED]
>To contact the list administrators, e-mail: [EMAIL PROTECTED]
>
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Left Join is producing duplicate results - MySQL & relational tables
phpMyAdmin is great! And I hear that they are coming out with a new version
pretty soon (or is it here - haven't checked)..
However I don't think that you can join tables in phpMyAdmin.. Perhaps I don't
know the app well enough though.
Mike
Beau Lebens wrote:
> altho this may not help at all, rather than bashing away with php+SQL
> statements, i usually just pilot my sql in phpmyadmin, then once it works
> with set values (ie SELECT * FROM people WHERE personID='3' rahter than
> personID='$personID' or something) i can then drop that SQL into my script.
>
> HTH
> Beau
>
> // -Original Message-
> // From: Mike Gifford [mailto:[EMAIL PROTECTED]]
> // Sent: Thursday, 12 July 2001 11:01 AM
> // To: Ken
> // Cc: [EMAIL PROTECTED]
> // Subject: Re: [PHP-DB] Left Join is producing duplicate
> // results - MySQL &
> // relational tables
> //
> //
> // Sorry Ken,
> //
> // I'm trying to learn a few too many things at once.
> // Switching it around
> // eliminated the error message but not the duplication.
> //
> // I'm eliminating as much of the code as I can to see that it
> // isn't coming from PHP.
> //
> // Thanks again for your help.
> //
> // Mike
> //
> // Ken wrote:
> //
> // > In general, you should always be reading the manual first.
> // From the SELECT syntax at
> // > http://www.mysql.com/doc/S/E/SELECT.html
> // > GROUP BY must be indicated before ORDER BY. So reverse
> // the order of those portions.
> // >
> // > - Ken
> // >
> // > At 10:35 PM 7/11/01 -0400, Mike Gifford wrote:
> // >
> // >>It looked good, but it gave me an error.. Sorry
> // >>...
> // >>WLPcountry.countryID ORDER BY WLPbib.title GROUP BY WLPbib.bibID
> // >>
> // >>MySQL Error: 1064 (You have an error in your SQL syntax
> // near 'GROUP BY WLPbib.bibID ' at line 10)
> // >>Please contact the webmaster and report the exact error message.
> // >>Session halted.
> // >>
> // >>Ken wrote:
> // >>
> // >>
> // >>>Mike -
> // >>>I'm not certain but it sounds like you might be looking
> // for "GROUP BY". Do GROUP BY and then the columns that are
> // identical in your results.
> // >>>- Ken
> // >>>At 09:46 PM 7/11/01 -0400, Mike Gifford wrote:
> // >>>
> // >>>
> // >>>>Hello,
> // >>>>
> // >>>>I posted this to the general list this morning & got a
> // couple of good leads, but they weren't able to actually fix
> // the problem, so I'm posting here to the db list.
> // >>>>
> // >>>>I'm making some headway on joining three MySQL tables.
> // >>>>
> // >>>>However, when I run this query:
> // >>>>
> // >>>>mysql_query("SELECT
> // >>>> WLPbib.bibID,
> // >>>>WLPbib.title,
> // >>>>WLPbib.publisher,
> // >>>>WLPbib.publicationDate,
> // >>>>WLPaddress.city,
> // >>>>WLPaddress.state,
> // >>>>WLPprofile.firstName,
> // >>>>WLPprofile.lastName,
> // >>>>WLPprofile.organization,
> // >>>>WLPcountry.languageName
> // >>>>
> // >>>>FROM WLPbib
> // >>>
> // >>>>LEFT JOIN WLPprofile ON WLPprofile.profileID =
> // WLPbib.profileID
> // >>>>LEFT JOIN WLPaddress ON WLPaddress.publisherID =
> // WLPbib.publisherID
> // >>>>LEFT JOIN WLPcountry ON WLPcountry.countryID =
> // WLPaddress.countryID");
> // >>>>
> // >>>>I now get results in triplicate. ie. I'm getting three
> // copies of the same title, firstName, organization, etc
> // >>>>
> // >>>>I somehow suspected that this should be the result with
> // LEFT JOIN, but I'm not sure how to return a query without
> // duplication.
> // >>>>
> // >>>>This is far better than what I had this morning (which
> // was no response from the server).
> // >>>>
> // >>>>Thanks. I'm new to joining tables...
> // >>>>
> // >>>>Someone wrote back suggesting that SELECT DISTINCT could
> // be used to to the job.
> // >>>>
> // >>>>Another person suggested that using UNIQUE(profileID)
> // would make it look nicer.
RE: [PHP-DB] Left Join is producing duplicate results - MySQL & relational tables
altho this may not help at all, rather than bashing away with php+SQL
statements, i usually just pilot my sql in phpmyadmin, then once it works
with set values (ie SELECT * FROM people WHERE personID='3' rahter than
personID='$personID' or something) i can then drop that SQL into my script.
HTH
Beau
// -Original Message-
// From: Mike Gifford [mailto:[EMAIL PROTECTED]]
// Sent: Thursday, 12 July 2001 11:01 AM
// To: Ken
// Cc: [EMAIL PROTECTED]
// Subject: Re: [PHP-DB] Left Join is producing duplicate
// results - MySQL &
// relational tables
//
//
// Sorry Ken,
//
// I'm trying to learn a few too many things at once.
// Switching it around
// eliminated the error message but not the duplication.
//
// I'm eliminating as much of the code as I can to see that it
// isn't coming from PHP.
//
// Thanks again for your help.
//
// Mike
//
// Ken wrote:
//
// > In general, you should always be reading the manual first.
// From the SELECT syntax at
// > http://www.mysql.com/doc/S/E/SELECT.html
// > GROUP BY must be indicated before ORDER BY. So reverse
// the order of those portions.
// >
// > - Ken
// >
// > At 10:35 PM 7/11/01 -0400, Mike Gifford wrote:
// >
// >>It looked good, but it gave me an error.. Sorry
// >>...
// >>WLPcountry.countryID ORDER BY WLPbib.title GROUP BY WLPbib.bibID
// >>
// >>MySQL Error: 1064 (You have an error in your SQL syntax
// near 'GROUP BY WLPbib.bibID ' at line 10)
// >>Please contact the webmaster and report the exact error message.
// >>Session halted.
// >>
// >>Ken wrote:
// >>
// >>
// >>>Mike -
// >>>I'm not certain but it sounds like you might be looking
// for "GROUP BY". Do GROUP BY and then the columns that are
// identical in your results.
// >>>- Ken
// >>>At 09:46 PM 7/11/01 -0400, Mike Gifford wrote:
// >>>
// >>>
// >>>>Hello,
// >>>>
// >>>>I posted this to the general list this morning & got a
// couple of good leads, but they weren't able to actually fix
// the problem, so I'm posting here to the db list.
// >>>>
// >>>>I'm making some headway on joining three MySQL tables.
// >>>>
// >>>>However, when I run this query:
// >>>>
// >>>>mysql_query("SELECT
// >>>> WLPbib.bibID,
// >>>>WLPbib.title,
// >>>>WLPbib.publisher,
// >>>>WLPbib.publicationDate,
// >>>>WLPaddress.city,
// >>>>WLPaddress.state,
// >>>>WLPprofile.firstName,
// >>>>WLPprofile.lastName,
// >>>>WLPprofile.organization,
// >>>>WLPcountry.languageName
// >>>>
// >>>>FROM WLPbib
// >>>
// >>>>LEFT JOIN WLPprofile ON WLPprofile.profileID =
// WLPbib.profileID
// >>>>LEFT JOIN WLPaddress ON WLPaddress.publisherID =
// WLPbib.publisherID
// >>>>LEFT JOIN WLPcountry ON WLPcountry.countryID =
// WLPaddress.countryID");
// >>>>
// >>>>I now get results in triplicate. ie. I'm getting three
// copies of the same title, firstName, organization, etc
// >>>>
// >>>>I somehow suspected that this should be the result with
// LEFT JOIN, but I'm not sure how to return a query without
// duplication.
// >>>>
// >>>>This is far better than what I had this morning (which
// was no response from the server).
// >>>>
// >>>>Thanks. I'm new to joining tables...
// >>>>
// >>>>Someone wrote back suggesting that SELECT DISTINCT could
// be used to to the job.
// >>>>
// >>>>Another person suggested that using UNIQUE(profileID)
// would make it look nicer. I wasn't sure how to use UNIQUE
// with the last JOIN as it isn't directly linked to WLPbib..
// >>>>
// >>>>Any suggestions would be useful.
// >>>>
// >>>>Mike
// >>>>
// >>
// >>
// >>--
// >>Mike Gifford, OpenConcept Consulting, http://openconcept.ca
// >>Offering everything your organization needs for an
// effective web site.
// >>Abolish Nuclear Weapons Now!: http://pgs.ca/petition/
// >>It is a miracle that curiosity survives formal education.
// - A Einstein
// >>
// >>
// >>--
// >>PHP Database Mailing List (http://www.php.net/)
// >>To unsubscribe, e-mail: [EMAIL PROTECTED]
// >>For additional commands, e-mail: [EMAIL PROTECTED]
// >>To contact the list administrators, e-mail:
// [EMAIL PROTECTED]
// >>
// >>
// >
//
//
//
// --
// Mike Gifford, OpenConcept Consulting, http://openconcept.ca
// Offering everything your organization needs for an effective
// web site.
// Abolish Nuclear Weapons Now!: http://pgs.ca/petition/
// It is a miracle that curiosity survives formal education. -
// A Einstein
//
//
// --
// PHP Database Mailing List (http://www.php.net/)
// To unsubscribe, e-mail: [EMAIL PROTECTED]
// For additional commands, e-mail: [EMAIL PROTECTED]
// To contact the list administrators, e-mail:
// [EMAIL PROTECTED]
//
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Left Join is producing duplicate results - MySQL & relational tables
Just build the SQL statement piece by piece instead of all at once.
Be sure that your tables are setup properly, allowing you to make joins
properly. Try not to rush, cause you might miss a couple of little things
along the way.
Kevin Johnson
- Original Message -
From: "Mike Gifford" <[EMAIL PROTECTED]>
To: "Ken" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Wednesday, July 11, 2001 08:00 PM
Subject: Re: [PHP-DB] Left Join is producing duplicate results - MySQL &
relational tables
> Sorry Ken,
>
> I'm trying to learn a few too many things at once. Switching it around
> eliminated the error message but not the duplication.
>
> I'm eliminating as much of the code as I can to see that it isn't coming
from PHP.
>
> Thanks again for your help.
>
> Mike
>
> Ken wrote:
>
> > In general, you should always be reading the manual first. From the
SELECT syntax at
> > http://www.mysql.com/doc/S/E/SELECT.html
> > GROUP BY must be indicated before ORDER BY. So reverse the order of
those portions.
> >
> > - Ken
> >
> > At 10:35 PM 7/11/01 -0400, Mike Gifford wrote:
> >
> >>It looked good, but it gave me an error.. Sorry
> >>...
> >>WLPcountry.countryID ORDER BY WLPbib.title GROUP BY WLPbib.bibID
> >>
> >>MySQL Error: 1064 (You have an error in your SQL syntax near 'GROUP BY
WLPbib.bibID ' at line 10)
> >>Please contact the webmaster and report the exact error message.
> >>Session halted.
> >>
> >>Ken wrote:
> >>
> >>
> >>>Mike -
> >>>I'm not certain but it sounds like you might be looking for "GROUP BY".
Do GROUP BY and then the columns that are identical in your results.
> >>>- Ken
> >>>At 09:46 PM 7/11/01 -0400, Mike Gifford wrote:
> >>>
> >>>
> >>>>Hello,
> >>>>
> >>>>I posted this to the general list this morning & got a couple of good
leads, but they weren't able to actually fix the problem, so I'm posting
here to the db list.
> >>>>
> >>>>I'm making some headway on joining three MySQL tables.
> >>>>
> >>>>However, when I run this query:
> >>>>
> >>>>mysql_query("SELECT
> >>>> WLPbib.bibID,
> >>>>WLPbib.title,
> >>>>WLPbib.publisher,
> >>>>WLPbib.publicationDate,
> >>>>WLPaddress.city,
> >>>>WLPaddress.state,
> >>>>WLPprofile.firstName,
> >>>>WLPprofile.lastName,
> >>>>WLPprofile.organization,
> >>>>WLPcountry.languageName
> >>>>
> >>>>FROM WLPbib
> >>>
> >>>>LEFT JOIN WLPprofile ON WLPprofile.profileID =
WLPbib.profileID
> >>>>LEFT JOIN WLPaddress ON WLPaddress.publisherID =
WLPbib.publisherID
> >>>>LEFT JOIN WLPcountry ON WLPcountry.countryID =
WLPaddress.countryID");
> >>>>
> >>>>I now get results in triplicate. ie. I'm getting three copies of the
same title, firstName, organization, etc
> >>>>
> >>>>I somehow suspected that this should be the result with LEFT JOIN, but
I'm not sure how to return a query without duplication.
> >>>>
> >>>>This is far better than what I had this morning (which was no response
from the server).
> >>>>
> >>>>Thanks. I'm new to joining tables...
> >>>>
> >>>>Someone wrote back suggesting that SELECT DISTINCT could be used to to
the job.
> >>>>
> >>>>Another person suggested that using UNIQUE(profileID) would make it
look nicer. I wasn't sure how to use UNIQUE with the last JOIN as it isn't
directly linked to WLPbib..
> >>>>
> >>>>Any suggestions would be useful.
> >>>>
> >>>>Mike
> >>>>
> >>
> >>
> >>--
> >>Mike Gifford, OpenConcept Consulting, http://openconcept.ca
> >>Offering everything your organization needs for an effective web site.
> >>Abolish Nuclear Weapons Now!: http://pgs.ca/petition/
> >>It is a miracle that curiosity survives formal education. - A Einstein
> >>
> >>
> >>--
> >>PHP Database Mailing List (http://www.php.net/)
> >>To unsubscribe, e-mail: [EMAIL PROTECTED]
> >>For additional commands, e-mail: [EMAIL PROTECTED]
> >>To contact the list administrators, e-mail: [EMAIL PROTECTED]
> >>
> >>
> >
>
>
>
> --
> Mike Gifford, OpenConcept Consulting, http://openconcept.ca
> Offering everything your organization needs for an effective web site.
> Abolish Nuclear Weapons Now!: http://pgs.ca/petition/
> It is a miracle that curiosity survives formal education. - A Einstein
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
> To contact the list administrators, e-mail: [EMAIL PROTECTED]
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Left Join is producing duplicate results - MySQL & relational tables
Sorry Ken,
I'm trying to learn a few too many things at once. Switching it around
eliminated the error message but not the duplication.
I'm eliminating as much of the code as I can to see that it isn't coming from PHP.
Thanks again for your help.
Mike
Ken wrote:
> In general, you should always be reading the manual first. From the SELECT syntax at
> http://www.mysql.com/doc/S/E/SELECT.html
> GROUP BY must be indicated before ORDER BY. So reverse the order of those portions.
>
> - Ken
>
> At 10:35 PM 7/11/01 -0400, Mike Gifford wrote:
>
>>It looked good, but it gave me an error.. Sorry
>>...
>>WLPcountry.countryID ORDER BY WLPbib.title GROUP BY WLPbib.bibID
>>
>>MySQL Error: 1064 (You have an error in your SQL syntax near 'GROUP BY WLPbib.bibID
>' at line 10)
>>Please contact the webmaster and report the exact error message.
>>Session halted.
>>
>>Ken wrote:
>>
>>
>>>Mike -
>>>I'm not certain but it sounds like you might be looking for "GROUP BY". Do GROUP
>BY and then the columns that are identical in your results.
>>>- Ken
>>>At 09:46 PM 7/11/01 -0400, Mike Gifford wrote:
>>>
>>>
Hello,
I posted this to the general list this morning & got a couple of good leads, but
>they weren't able to actually fix the problem, so I'm posting here to the db list.
I'm making some headway on joining three MySQL tables.
However, when I run this query:
mysql_query("SELECT
WLPbib.bibID,
WLPbib.title,
WLPbib.publisher,
WLPbib.publicationDate,
WLPaddress.city,
WLPaddress.state,
WLPprofile.firstName,
WLPprofile.lastName,
WLPprofile.organization,
WLPcountry.languageName
FROM WLPbib
>>>
LEFT JOIN WLPprofile ON WLPprofile.profileID = WLPbib.profileID
LEFT JOIN WLPaddress ON WLPaddress.publisherID = WLPbib.publisherID
LEFT JOIN WLPcountry ON WLPcountry.countryID = WLPaddress.countryID");
I now get results in triplicate. ie. I'm getting three copies of the same title,
>firstName, organization, etc
I somehow suspected that this should be the result with LEFT JOIN, but I'm not
>sure how to return a query without duplication.
This is far better than what I had this morning (which was no response from the
>server).
Thanks. I'm new to joining tables...
Someone wrote back suggesting that SELECT DISTINCT could be used to to the job.
Another person suggested that using UNIQUE(profileID) would make it look nicer. I
>wasn't sure how to use UNIQUE with the last JOIN as it isn't directly linked to
>WLPbib..
Any suggestions would be useful.
Mike
>>
>>
>>--
>>Mike Gifford, OpenConcept Consulting, http://openconcept.ca
>>Offering everything your organization needs for an effective web site.
>>Abolish Nuclear Weapons Now!: http://pgs.ca/petition/
>>It is a miracle that curiosity survives formal education. - A Einstein
>>
>>
>>--
>>PHP Database Mailing List (http://www.php.net/)
>>To unsubscribe, e-mail: [EMAIL PROTECTED]
>>For additional commands, e-mail: [EMAIL PROTECTED]
>>To contact the list administrators, e-mail: [EMAIL PROTECTED]
>>
>>
>
--
Mike Gifford, OpenConcept Consulting, http://openconcept.ca
Offering everything your organization needs for an effective web site.
Abolish Nuclear Weapons Now!: http://pgs.ca/petition/
It is a miracle that curiosity survives formal education. - A Einstein
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Left Join is producing duplicate results - MySQL & relational tables
Mike -
In general, you should always be reading the manual first. From the SELECT syntax at
http://www.mysql.com/doc/S/E/SELECT.html
GROUP BY must be indicated before ORDER BY. So reverse the order of those portions.
- Ken
At 10:35 PM 7/11/01 -0400, Mike Gifford wrote:
>It looked good, but it gave me an error.. Sorry
>...
>WLPcountry.countryID ORDER BY WLPbib.title GROUP BY WLPbib.bibID
>
>MySQL Error: 1064 (You have an error in your SQL syntax near 'GROUP BY WLPbib.bibID '
>at line 10)
>Please contact the webmaster and report the exact error message.
>Session halted.
>
>Ken wrote:
>
>>Mike -
>>I'm not certain but it sounds like you might be looking for "GROUP BY". Do GROUP BY
>and then the columns that are identical in your results.
>>- Ken
>>At 09:46 PM 7/11/01 -0400, Mike Gifford wrote:
>>
>>>Hello,
>>>
>>>I posted this to the general list this morning & got a couple of good leads, but
>they weren't able to actually fix the problem, so I'm posting here to the db list.
>>>
>>>I'm making some headway on joining three MySQL tables.
>>>
>>>However, when I run this query:
>>>
>>>mysql_query("SELECT
>>> WLPbib.bibID,
>>> WLPbib.title,
>>> WLPbib.publisher,
>>> WLPbib.publicationDate,
>>> WLPaddress.city,
>>> WLPaddress.state,
>>> WLPprofile.firstName,
>>> WLPprofile.lastName,
>>> WLPprofile.organization,
>>> WLPcountry.languageName
>>>FROM WLPbib
>>> LEFT JOIN WLPprofile ON WLPprofile.profileID = WLPbib.profileID
>>> LEFT JOIN WLPaddress ON WLPaddress.publisherID = WLPbib.publisherID
>>> LEFT JOIN WLPcountry ON WLPcountry.countryID = WLPaddress.countryID");
>>>
>>>I now get results in triplicate. ie. I'm getting three copies of the same title,
>firstName, organization, etc
>>>
>>>I somehow suspected that this should be the result with LEFT JOIN, but I'm not sure
>how to return a query without duplication.
>>>
>>>This is far better than what I had this morning (which was no response from the
>server).
>>>
>>>Thanks. I'm new to joining tables...
>>>
>>>Someone wrote back suggesting that SELECT DISTINCT could be used to to the job.
>>>
>>>Another person suggested that using UNIQUE(profileID) would make it look nicer. I
>wasn't sure how to use UNIQUE with the last JOIN as it isn't directly linked to
>WLPbib..
>>>
>>>Any suggestions would be useful.
>>>
>>>Mike
>
>
>
>--
>Mike Gifford, OpenConcept Consulting, http://openconcept.ca
>Offering everything your organization needs for an effective web site.
>Abolish Nuclear Weapons Now!: http://pgs.ca/petition/
>It is a miracle that curiosity survives formal education. - A Einstein
>
>
>--
>PHP Database Mailing List (http://www.php.net/)
>To unsubscribe, e-mail: [EMAIL PROTECTED]
>For additional commands, e-mail: [EMAIL PROTECTED]
>To contact the list administrators, e-mail: [EMAIL PROTECTED]
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Left Join is producing duplicate results - MySQL & relational tables
It looked good, but it gave me an error.. Sorry
Database error:
Invalid SQL: SELECT WLPbib.bibID,
WLPbib.title,
WLPbib.publisher,
WLPbib.publicationDate,
WLPaddress.city,
WLPaddress.state,
WLPprofile.firstName,
WLPprofile.lastName,
WLPprofile.organization,
WLPcountry.languageName FROM WLPbib LEFT JOIN WLPprofile ON
WLPbib.profileID = WLPprofile.profileID LEFT JOIN WLPaddress ON
WLPbib.publisherID = WLPaddress.publisherID LEFT JOIN WLPcountry ON
WLPaddress.countryID = WLPcountry.countryID ORDER BY WLPbib.title GROUP BY
WLPbib.bibID
MySQL Error: 1064 (You have an error in your SQL syntax near 'GROUP BY
WLPbib.bibID ' at line 10)
Please contact the webmaster and report the exact error message.
Session halted.
Ken wrote:
> Mike -
>
> I'm not certain but it sounds like you might be looking for "GROUP BY". Do GROUP BY
>and then the columns that are identical in your results.
>
> - Ken
>
> At 09:46 PM 7/11/01 -0400, Mike Gifford wrote:
>
>>Hello,
>>
>>I posted this to the general list this morning & got a couple of good leads, but
>they weren't able to actually fix the problem, so I'm posting here to the db list.
>>
>>I'm making some headway on joining three MySQL tables.
>>
>>However, when I run this query:
>>
>>mysql_query("SELECT
>> WLPbib.bibID,
>> WLPbib.title,
>> WLPbib.publisher,
>> WLPbib.publicationDate,
>> WLPaddress.city,
>> WLPaddress.state,
>> WLPprofile.firstName,
>> WLPprofile.lastName,
>> WLPprofile.organization,
>> WLPcountry.languageName
>> FROM WLPbib
>> LEFT JOIN WLPprofile ON WLPprofile.profileID = WLPbib.profileID
>> LEFT JOIN WLPaddress ON WLPaddress.publisherID = WLPbib.publisherID
>> LEFT JOIN WLPcountry ON WLPcountry.countryID = WLPaddress.countryID");
>>
>>I now get results in triplicate. ie. I'm getting three copies of the same title,
>firstName, organization, etc
>>
>>I somehow suspected that this should be the result with LEFT JOIN, but I'm not sure
>how to return a query without duplication.
>>
>>This is far better than what I had this morning (which was no response from the
>server).
>>
>>Thanks. I'm new to joining tables...
>>
>>Someone wrote back suggesting that SELECT DISTINCT could be used to to the job.
>>
>>Another person suggested that using UNIQUE(profileID) would make it look nicer. I
>wasn't sure how to use UNIQUE with the last JOIN as it isn't directly linked to
>WLPbib..
>>
>>Any suggestions would be useful.
>>
>>Mike
>>
>
--
Mike Gifford, OpenConcept Consulting, http://openconcept.ca
Offering everything your organization needs for an effective web site.
Abolish Nuclear Weapons Now!: http://pgs.ca/petition/
It is a miracle that curiosity survives formal education. - A Einstein
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Left Join is producing duplicate results - MySQL & relational tables
Mike -
I'm not certain but it sounds like you might be looking for "GROUP BY". Do GROUP BY
and then the columns that are identical in your results.
- Ken
At 09:46 PM 7/11/01 -0400, Mike Gifford wrote:
>Hello,
>
>I posted this to the general list this morning & got a couple of good leads, but they
>weren't able to actually fix the problem, so I'm posting here to the db list.
>
>I'm making some headway on joining three MySQL tables.
>
>However, when I run this query:
>
>mysql_query("SELECT
> WLPbib.bibID,
> WLPbib.title,
> WLPbib.publisher,
> WLPbib.publicationDate,
> WLPaddress.city,
> WLPaddress.state,
> WLPprofile.firstName,
> WLPprofile.lastName,
> WLPprofile.organization,
> WLPcountry.languageName
> FROM WLPbib
> LEFT JOIN WLPprofile ON WLPprofile.profileID = WLPbib.profileID
> LEFT JOIN WLPaddress ON WLPaddress.publisherID = WLPbib.publisherID
> LEFT JOIN WLPcountry ON WLPcountry.countryID = WLPaddress.countryID");
>
>I now get results in triplicate. ie. I'm getting three copies of the same title,
>firstName, organization, etc
>
>I somehow suspected that this should be the result with LEFT JOIN, but I'm not sure
>how to return a query without duplication.
>
>This is far better than what I had this morning (which was no response from the
>server).
>
>Thanks. I'm new to joining tables...
>
>Someone wrote back suggesting that SELECT DISTINCT could be used to to the job.
>
>Another person suggested that using UNIQUE(profileID) would make it look nicer. I
>wasn't sure how to use UNIQUE with the last JOIN as it isn't directly linked to
>WLPbib..
>
>Any suggestions would be useful.
>
>Mike
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
