Re: left joins concat
Hi Phil,
you seem quite a bit confused!
I would not step to use joins before understanding the 'simple' logic
behind,
otherwise you will be always confused by the syntax.
There are many, many, many resources (thanks to Tim!)
I will try to give you a simple overview of joins but please get more
confortable with them!
In relational databases the information is(should!) logically separated into
entities which are no more no less represented by tables,
yes the tables you well know(mathematically speaking they are known as
[relations] the R in RDBMS).
So in some application,like yours, you could have the need to deal with
[books], [authors], [publishers].
These, as said, are the so called entities when we speak from an
abstract-logical point of view,
but eventually turn to be the tables in your database.
So you have a table with all the books, a table with all the authors, and a
table with the publishers.
How could we relate each book with his author?
One way is to have extra information on each book (1 book=1 record/row in
the [books] table),
if an author wrote 100 books you would have 100 times the same information
on each of his books.
another way(better!) is to add the extra information as well, but just an
identifier of the author,
an handle, a key, a UNIQUE value (Social Security Number?) , so that you
have only one place
with the author information (the [author] table) which is also great for
maintenance!
Imagine updating an information about the author on 100 rows of the [books]
table,
and update the same information just in one row of the [authors] table.
I think you can imagine also that the UNIQUE value you add to each book
which identifies the book author,
will be present in the [authors] table to be able to identify the author.
Until now we are just speaking about logic and you could do an excercise
with pen and paper,
drawing a line from the AUTHOR UNIQUE ID from the [books] table to the
AUTHOR UNIQUE ID from the [authors] table.
So you could easily find the author of each book by following the line the
links the two rows/records,
on the left you have the books and on the right you have the authors.
Reading from left to right, for instance, you would be able now to read
consequently the book title and the book author name.
Sit back, the JOIN is the line you have just draw.
It is the connection between two tables to be able to have on just one row
all the information that are split into two(or more) parts/table.
The ON clause that you find in the JOIN syntax is the place where you
specify
ON [books].AUTHOR UNIQUE ID = [authors].AUTHOR UNIQUE ID
( by the way the ID that points to the table with all the informations is
also knows as FOREIGN KEY, in this case the left operand)
The resulting table is a table that have each row like a concatenation of
two rows related from the two different tables.
The WHERE clause is used to FILTER, not to connect the two tables!!
After you connect(join) the two tables you could want to see only certain
rows, ok now you use the WHERE.
Forget about CONCAT/CONCAT_WS this is a string function, and is not related
to JOINS.
Please, let me know if this was useful to you.
Claudio Nanni
2009/2/22 PJ af.gour...@videotron.ca
I have been searching and searching for a clear and logical explanation
of JOINs and have found nothing that can be reasonably understood.
Perhaps I am dense or from another planet, but nothing seems to fall
into place.
I need to display all the books (with their respective authors and
publishers) -
the tables are book, author, publisher and book_author, book_publisher
for linking many to many books-authors and books-publishers.
Here is what I have (and it gives me rather confusing results:
SELECT b.title, b.sub_title, b.descr, b.comment, b.bk_cover,
b.copyright, b.ISBN, b.sellers, c.publisher,
CONCAT_WS(' ', first_name, last_name) AS Author
FROM book AS b, publishers AS c
NATURAL JOIN book_author AS ab
NATURAL JOIN author AS a
NATURAL JOIN book_publisher AS d
WHERE d.bookID = b.id
ORDER BY title ASC
First, I see what CONCAT_WS does (more or less) but I cannot grasp the
logic related to the author and book_author tables.
Second, I don't understand the logic of the linking tables since the
relationships seem to have to be stated outside the tables... (i.e. the
foreign key reference is in the table but seems to be necessary in a
WHERE clause as well ???
And lastly, I don't understand what conditions (ON or WHERE clauses)
need to be included to get the books matched up to their respective
authors and publishers.
One link that I have been trying to fathom is
http://www.java2s.com/Tutorial/MySQL/0100__Table-Join/Catalog0100__Table-Join.htm
but it is not very helpful in any explanations...
I sure would like to hear some clear explanations...
TIA
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
MySQL General Mailing List
For list archives:
Re: moving data to MySQL
Well, it is just to have a console that is able to connect to every database, ODBC and videogames are one of the few things good about microsoft! I used it to import export data from/to access/sql server/mysql/oracle. Cheers Claudio 2009/2/22 Haidong Ji haidong...@gmail.com Hi, Never thought of the MS Access solution, but it sounds interesting and I am intrigued. If the data volume is not that big, it could be a quick and dirty way of doing it. Sql Server 2005 and Sql Server 2008 Integration Service (SSIS) can do this, but as far as usability is concerned, it is a step back from DTS (Data Transformation Service from Sql Server 7 and Sql Server 2000 days). SSIS has a fairly steep learning curve. I am also for the CSV flat file approach. One could use Sql Server bcp utility to dump tables out as tab delimited file, then import it into MySQL with LOAD DATA INFILE. Sample of bcp command: bcp MyDb.dbo.t1 out c:\junk\t1.txt -c -q -SMySqlServerInstance -T Sample of LOAD DATA command: load data local infile '/home/MyFolder/t1.txt' into table MyDb.t1 lines terminated by 'r\n' It looks like your first task is to restore this backup into a Sql Server database. Sql Server Express management studio might have a GUI way of doing this. If not, here is a sample script: restore database MyDb from disk = 'c:\LocationOfMyDbBackup' with recovery, replace, move 'MyDbLogicalDataFile' to 'c:\MyDirectory\MyDb.mdf', move 'MyDbLogicalLogFile' to 'c:\MyDirectory\MyDb.ldf' Regards, Haidong Alex Ji http://www.HaidongJi.com/tech On Sat, Feb 21, 2009 at 5:48 PM, Claudio Nanni claudio.na...@gmail.comwrote: Hi, Using ODBC Drivers and MS Access you can do the job. This is a quick list of steps(if you need more in depth just ask): Download ODBC drivers for MySQL from www.mysql.com Install the ODBC drivers Create a System Data Source Name that point to the MySQL database (Control Panel-Administration Tools-ODBC Data Sources) Create a System Data Source Name that point to the MSSQL database (Control Panel-Administration Tools-ODBC Data Sources) Using Microsoft Access you can import-export-query the tables as you like. Cheers Claudio Nanni 2009/2/21 Chris Rehm ch...@javadisciple.com I want to write some programs to work with data from eveonline.com but my installed database is MySQL and the data format they provide is a backup of MSSQL. They recommend installing SQL Server 2005 Express and I've downloaded that and am willing to install it, I just want to know if there is a programmatic way of transferring the data to MySQL. Any help or insight would be appreciated, I have been away from coding for several years because of health issues and I'm trying to knock the rust off my brain and get going again. Chris Rehm ch...@javadisciple.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
Best RAID for a DB + LVM?
I currently have a RAID 5 setup for our database server. Our space is running out, so I'm looking to increase the disk space. Since I'm doing that anyway, I decided to re-evaluate our current disk array. I was told that RAID 5 isn't a good choice for databases since it's slower to write. In addition, I've also been considering setting up LVM to take quick db snapshots, after reading various links on the web (and posts to this list). So on to the questions! First, if that's what I eventually want to do (get a new RAID server with LVM), do I need to do anything special to set up LVM on the new system? Second, what is a good RAID setup for databases? RAID 10? 0+1? Third, I have the choice of using SATA or SCSI in conjuction with the RAID drives I choose. How much of a difference is there in using SATA instead of SCSI, especially in light of whatever RAID I end up going with? Thanks for any insights, Waynn
Re: left joins concat
Claudio Nanni wrote:
Hi Phil,
you seem quite a bit confused!
I would not step to use joins before understanding the 'simple' logic
behind,
otherwise you will be always confused by the syntax.
There are many, many, many resources (thanks to Tim!)
I will try to give you a simple overview of joins but please get more
confortable with them!
In relational databases the information is(should!) logically
separated into entities which are no more no less represented by tables,
yes the tables you well know(mathematically speaking they are known as
[relations] the R in RDBMS).
So in some application,like yours, you could have the need to deal
with [books], [authors], [publishers].
These, as said, are the so called entities when we speak from an
abstract-logical point of view,
but eventually turn to be the tables in your database.
So you have a table with all the books, a table with all the authors,
and a table with the publishers.
How could we relate each book with his author?
One way is to have extra information on each book (1 book=1 record/row
in the [books] table),
if an author wrote 100 books you would have 100 times the same
information on each of his books.
another way(better!) is to add the extra information as well, but just
an identifier of the author,
an handle, a key, a UNIQUE value (Social Security Number?) , so that
you have only one place
with the author information (the [author] table) which is also great
for maintenance!
Imagine updating an information about the author on 100 rows of the
[books] table,
and update the same information just in one row of the [authors] table.
I think you can imagine also that the UNIQUE value you add to each
book which identifies the book author,
will be present in the [authors] table to be able to identify the author.
Until now we are just speaking about logic and you could do an
excercise with pen and paper,
drawing a line from the AUTHOR UNIQUE ID from the [books] table to
the AUTHOR UNIQUE ID from the [authors] table.
So you could easily find the author of each book by following the line
the links the two rows/records,
on the left you have the books and on the right you have the authors.
Reading from left to right, for instance, you would be able now to
read consequently the book title and the book author name.
Sit back, the JOIN is the line you have just draw.
It is the connection between two tables to be able to have on just one
row all the information that are split into two(or more) parts/table.
The ON clause that you find in the JOIN syntax is the place where you
specify
ON [books].AUTHOR UNIQUE ID = [authors].AUTHOR UNIQUE ID
( by the way the ID that points to the table with all the informations
is also knows as FOREIGN KEY, in this case the left operand)
The resulting table is a table that have each row like a concatenation
of two rows related from the two different tables.
The WHERE clause is used to FILTER, not to connect the two tables!!
After you connect(join) the two tables you could want to see only
certain rows, ok now you use the WHERE.
Forget about CONCAT/CONCAT_WS this is a string function, and is not
related to JOINS.
Please, let me know if this was useful to you.
Claudio Nanni
2009/2/22 PJ af.gour...@videotron.ca mailto:af.gour...@videotron.ca
I have been searching and searching for a clear and logical
explanation
of JOINs and have found nothing that can be reasonably understood.
Perhaps I am dense or from another planet, but nothing seems to fall
into place.
I need to display all the books (with their respective authors and
publishers) -
the tables are book, author, publisher and book_author, book_publisher
for linking many to many books-authors and books-publishers.
Here is what I have (and it gives me rather confusing results:
SELECT b.title, b.sub_title, b.descr, b.comment, b.bk_cover,
b.copyright, b.ISBN, b.sellers, c.publisher,
CONCAT_WS(' ', first_name, last_name) AS Author
FROM book AS b, publishers AS c
NATURAL JOIN book_author AS ab
NATURAL JOIN author AS a
NATURAL JOIN book_publisher AS d
WHERE d.bookID = b.id http://b.id
ORDER BY title ASC
First, I see what CONCAT_WS does (more or less) but I cannot grasp the
logic related to the author and book_author tables.
Second, I don't understand the logic of the linking tables since the
relationships seem to have to be stated outside the tables...
(i.e. the
foreign key reference is in the table but seems to be necessary in a
WHERE clause as well ???
And lastly, I don't understand what conditions (ON or WHERE clauses)
need to be included to get the books matched up to their respective
authors and publishers.
One link that I have been trying to fathom is
http://www.java2s.com/Tutorial/MySQL/0100__Table-Join/Catalog0100__Table-Join.htm
but it
Re: left joins concat
Gentlemen,
and all those who care,
THE PROBLEM SEEMS TO BE SOLVED (for now, NEXT is HOW TO QUERY(SELECT)
MULTIPLE AUTHORS AND DISPLAY THAT and then HOW TO DEAL WITH MULTIPLE
CATEGORIES (heh... heh... heh ;-) :
SELECT b.title, b.sub_title, b.descr, b.comment, b.bk_cover,
b.copyright, b.ISBN, c.publisher,
CONCAT_WS(' ', first_name, last_name) AS Author
FROM book AS b
LEFT JOIN book_author AS ab ON b.id = ab.bookID
LEFT JOIN author AS a ON ab.authID=a.id
LEFT JOIN book_publisher AS abc ON b.id = abc.bookID
LEFT JOIN publishers AS c ON abc.publishers_id = c.id
ORDER BY title ASC
This works except I don't fully understand how the CONCAT AS Author
works, nor the AS ab and AS abc.
Does the order of ab and abc matter? Are they related... I think I got
this working by pure experimentation in trying all possible
combinations... kind of primitive, but it seems to work... long hours
and loss of sleep... etc...
And then there is the warning message at the bottom of the displayed web
page:
*Warning*: Unknown: Your script possibly relies on a session side-effect
which existed until PHP 4.2.3. Please be advised that the session
extension does not consider global variables as a source of data, unless
register_globals is enabled. You can disable this functionality and this
warning by setting session.bug_compat_42 or session.bug_compat_warn to
off, respectively. in *Unknown* on line *0
*I guess I'll have to research it on the web... :-)*
*
Claudio Nanni wrote:
Hi Phil,
you seem quite a bit confused!
I would not step to use joins before understanding the 'simple' logic
behind,
otherwise you will be always confused by the syntax.
There are many, many, many resources (thanks to Tim!)
I will try to give you a simple overview of joins but please get more
confortable with them!
In relational databases the information is(should!) logically
separated into entities which are no more no less represented by tables,
yes the tables you well know(mathematically speaking they are known as
[relations] the R in RDBMS).
So in some application,like yours, you could have the need to deal
with [books], [authors], [publishers].
These, as said, are the so called entities when we speak from an
abstract-logical point of view,
but eventually turn to be the tables in your database.
So you have a table with all the books, a table with all the authors,
and a table with the publishers.
How could we relate each book with his author?
One way is to have extra information on each book (1 book=1 record/row
in the [books] table),
if an author wrote 100 books you would have 100 times the same
information on each of his books.
another way(better!) is to add the extra information as well, but just
an identifier of the author,
an handle, a key, a UNIQUE value (Social Security Number?) , so that
you have only one place
with the author information (the [author] table) which is also great
for maintenance!
Imagine updating an information about the author on 100 rows of the
[books] table,
and update the same information just in one row of the [authors] table.
I think you can imagine also that the UNIQUE value you add to each
book which identifies the book author,
will be present in the [authors] table to be able to identify the author.
Until now we are just speaking about logic and you could do an
excercise with pen and paper,
drawing a line from the AUTHOR UNIQUE ID from the [books] table to
the AUTHOR UNIQUE ID from the [authors] table.
So you could easily find the author of each book by following the line
the links the two rows/records,
on the left you have the books and on the right you have the authors.
Reading from left to right, for instance, you would be able now to
read consequently the book title and the book author name.
Sit back, the JOIN is the line you have just draw.
It is the connection between two tables to be able to have on just one
row all the information that are split into two(or more) parts/table.
The ON clause that you find in the JOIN syntax is the place where you
specify
ON [books].AUTHOR UNIQUE ID = [authors].AUTHOR UNIQUE ID
( by the way the ID that points to the table with all the informations
is also knows as FOREIGN KEY, in this case the left operand)
The resulting table is a table that have each row like a concatenation
of two rows related from the two different tables.
The WHERE clause is used to FILTER, not to connect the two tables!!
After you connect(join) the two tables you could want to see only
certain rows, ok now you use the WHERE.
Forget about CONCAT/CONCAT_WS this is a string function, and is not
related to JOINS.
Please, let me know if this was useful to you.
Claudio Nanni
2009/2/22 PJ af.gour...@videotron.ca mailto:af.gour...@videotron.ca
I have been searching and searching for a clear and logical
explanation
of JOINs and have found nothing that can be reasonably understood.
Perhaps I am dense or from another
Re: left joins concat
The 'AS' keyword for tables is just to give a table an alias by which
you can then use it in the rest of the query. In your case, when you
say 'book as b' in your query, it means that you can use b in places
where you need to refer to the book table. eg. 'b.id' refers to the
'id' field of the 'book' table. It is good practice to always use an
alias for a table and refer to the table by that alias anywhere in the
query. It makes your query easier to read and understand.
As a side-effect (which you should probably not focus on just yet ;) )
it means you can use this construction to join a table on itself and
use aliasses in the rest of the query to uniquely identify the table
you are referring to.
The same idea goes for the as keyword on field names. You can use
those aliasses to refer to the column in the gorup by, order by and
having clauses of your query. It is mostly used to display a proper
column name though. In your example, the concat_ws expresion has an
alias so in the queries result this name will be used instead of the
expression used to select.
A propos: The problem with your first query was that you were using
natural joins. Natural joins match up all columns that have the same
name in both tables automatically. I consider it bad practice to use
it as it might have unexpected results, especially when you alter
tables later on.
I recommend you to read up on your SQL knowledge though, as these are
very basic problems and it is imperative to have a good understanding
of SQL to be able to work with (m)any relational databases. I haven't
looked at their specific SQL course, but w3schools generally has very
good basic introductions: http://www.w3schools.com/sql/
Have fun!
Walter
OlinData: Professional services for MySQL
Support * Consulting * Administration
http://www.olindata.com
On Sun, Feb 22, 2009 at 4:15 PM, PJ af.gour...@videotron.ca wrote:
Gentlemen,
and all those who care,
THE PROBLEM SEEMS TO BE SOLVED (for now, NEXT is HOW TO QUERY(SELECT)
MULTIPLE AUTHORS AND DISPLAY THAT and then HOW TO DEAL WITH MULTIPLE
CATEGORIES (heh... heh... heh ;-) :
SELECT b.title, b.sub_title, b.descr, b.comment, b.bk_cover,
b.copyright, b.ISBN, c.publisher,
CONCAT_WS(' ', first_name, last_name) AS Author
FROM book AS b
LEFT JOIN book_author AS ab ON b.id = ab.bookID
LEFT JOIN author AS a ON ab.authID=a.id
LEFT JOIN book_publisher AS abc ON b.id = abc.bookID
LEFT JOIN publishers AS c ON abc.publishers_id = c.id
ORDER BY title ASC
This works except I don't fully understand how the CONCAT AS Author
works, nor the AS ab and AS abc.
Does the order of ab and abc matter? Are they related... I think I got
this working by pure experimentation in trying all possible
combinations... kind of primitive, but it seems to work... long hours
and loss of sleep... etc...
And then there is the warning message at the bottom of the displayed web
page:
*Warning*: Unknown: Your script possibly relies on a session side-effect
which existed until PHP 4.2.3. Please be advised that the session
extension does not consider global variables as a source of data, unless
register_globals is enabled. You can disable this functionality and this
warning by setting session.bug_compat_42 or session.bug_compat_warn to
off, respectively. in *Unknown* on line *0
*I guess I'll have to research it on the web... :-)*
*
Claudio Nanni wrote:
Hi Phil,
you seem quite a bit confused!
I would not step to use joins before understanding the 'simple' logic
behind,
otherwise you will be always confused by the syntax.
There are many, many, many resources (thanks to Tim!)
I will try to give you a simple overview of joins but please get more
confortable with them!
In relational databases the information is(should!) logically
separated into entities which are no more no less represented by tables,
yes the tables you well know(mathematically speaking they are known as
[relations] the R in RDBMS).
So in some application,like yours, you could have the need to deal
with [books], [authors], [publishers].
These, as said, are the so called entities when we speak from an
abstract-logical point of view,
but eventually turn to be the tables in your database.
So you have a table with all the books, a table with all the authors,
and a table with the publishers.
How could we relate each book with his author?
One way is to have extra information on each book (1 book=1 record/row
in the [books] table),
if an author wrote 100 books you would have 100 times the same
information on each of his books.
another way(better!) is to add the extra information as well, but just
an identifier of the author,
an handle, a key, a UNIQUE value (Social Security Number?) , so that
you have only one place
with the author information (the [author] table) which is also great
for maintenance!
Imagine updating an information about the author on 100 rows of the
[books] table,
and update the same information just in one
Re: Best RAID for a DB + LVM?
I work for a high-volume web site and we use nothing but RAID 10 on all databases which requires a minimum of 4 disks. The write penalty for raid 5 is just too high for our application. Much of that space goes unused, but we need the stripe to keep up with the I/O. - michael dykman On Sun, Feb 22, 2009 at 7:46 AM, Waynn Lue waynn...@gmail.com wrote: I currently have a RAID 5 setup for our database server. Our space is running out, so I'm looking to increase the disk space. Since I'm doing that anyway, I decided to re-evaluate our current disk array. I was told that RAID 5 isn't a good choice for databases since it's slower to write. In addition, I've also been considering setting up LVM to take quick db snapshots, after reading various links on the web (and posts to this list). So on to the questions! First, if that's what I eventually want to do (get a new RAID server with LVM), do I need to do anything special to set up LVM on the new system? Second, what is a good RAID setup for databases? RAID 10? 0+1? Third, I have the choice of using SATA or SCSI in conjuction with the RAID drives I choose. How much of a difference is there in using SATA instead of SCSI, especially in light of whatever RAID I end up going with? Thanks for any insights, Waynn -- - michael dykman - mdyk...@gmail.com - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: left joins concat
Thank you gentlemen.
Questions /or comments annotated below:
Walter Heck wrote:
The 'AS' keyword for tables is just to give a table an alias by which
you can then use it in the rest of the query. In your case, when you
say 'book as b' in your query, it means that you can use b in places
where you need to refer to the book table. eg. 'b.id' refers to the
'id' field of the 'book' table. It is good practice to always use an
alias for a table and refer to the table by that alias anywhere in the
query. It makes your query easier to read and understand.
As a side-effect (which you should probably not focus on just yet ;) )
it means you can use this construction to join a table on itself and
use aliasses in the rest of the query to uniquely identify the table
you are referring to.
I do want to know, so I'll look more into it. :)
The same idea goes for the as keyword on field names. You can use
those aliasses to refer to the column in the gorup by, order by and
having clauses of your query. It is mostly used to display a proper
column name though. In your example, the concat_ws expresion has an
alias so in the queries result this name will be used instead of the
expression used to select.
A propos: The problem with your first query was that you were using
natural joins. Natural joins match up all columns that have the same
name in both tables automatically. I consider it bad practice to use
it as it might have unexpected results, especially when you alter
tables later on.
When I had my flash of enlightenment came when I noticed that in my many
concatenations I had used and posted the NATURAL join (which = NATURAL
LEFT join); with a little tweaking I got it to work.
But I still don't understand if aliasing AS a is any different from
aliasing AS ab (or, for that matter, if I substituted a number for the
table. I haven't experimented yet, but is there a difference between
using a or ab, cd, abc and just plain t1, t2, t3 or is this the same:
one two three. It may seem naive to be asking such questions, but they
are not readily answered in the tutorials.
I recommend you to read up on your SQL knowledge though, as these are
very basic problems and it is imperative to have a good understanding
of SQL to be able to work with (m)any relational databases. I haven't
looked at their specific SQL course, but w3schools generally has very
good basic introductions: http://www.w3schools.com/sql/
I have consulted w3shools and do find that they are not clear on a lot
of details... I may be picky about the small details but it is they that
make the whole possible.
Have fun!
Walter
OlinData: Professional services for MySQL
Support * Consulting * Administration
http://www.olindata.com
On Sun, Feb 22, 2009 at 4:15 PM, PJ af.gour...@videotron.ca wrote:
Gentlemen,
and all those who care,
THE PROBLEM SEEMS TO BE SOLVED (for now, NEXT is HOW TO QUERY(SELECT)
MULTIPLE AUTHORS AND DISPLAY THAT and then HOW TO DEAL WITH MULTIPLE
CATEGORIES (heh... heh... heh ;-) :
SELECT b.title, b.sub_title, b.descr, b.comment, b.bk_cover,
b.copyright, b.ISBN, c.publisher,
CONCAT_WS(' ', first_name, last_name) AS Author
FROM book AS b
LEFT JOIN book_author AS ab ON b.id = ab.bookID
LEFT JOIN author AS a ON ab.authID=a.id
LEFT JOIN book_publisher AS abc ON b.id = abc.bookID
LEFT JOIN publishers AS c ON abc.publishers_id = c.id
ORDER BY title ASC
This works except I don't fully understand how the CONCAT AS Author
works, nor the AS ab and AS abc.
Does the order of ab and abc matter? Are they related... I think I got
this working by pure experimentation in trying all possible
combinations... kind of primitive, but it seems to work... long hours
and loss of sleep... etc...
And then there is the warning message at the bottom of the displayed web
page:
*Warning*: Unknown: Your script possibly relies on a session side-effect
which existed until PHP 4.2.3. Please be advised that the session
extension does not consider global variables as a source of data, unless
register_globals is enabled. You can disable this functionality and this
warning by setting session.bug_compat_42 or session.bug_compat_warn to
off, respectively. in *Unknown* on line *0
*I guess I'll have to research it on the web... :-)*
*
Claudio Nanni wrote:
Hi Phil,
you seem quite a bit confused!
I would not step to use joins before understanding the 'simple' logic
behind,
otherwise you will be always confused by the syntax.
There are many, many, many resources (thanks to Tim!)
I will try to give you a simple overview of joins but please get more
confortable with them!
In relational databases the information is(should!) logically
separated into entities which are no more no less represented by tables,
yes the tables you well know(mathematically speaking they are known as
[relations] the R in RDBMS).
So in some application,like yours, you could have the need to deal
with [books],
HOW TO QUERY(SELECT) and display MULTIPLE AUTHORS
SELECT b.title, b.sub_title, b.descr, b.comment, b.bk_cover,
b.copyright, b.ISBN, c.publisher,
CONCAT_WS(' ', first_name, last_name) AS Author
FROM book AS b
LEFT JOIN book_author AS ab ON b.id = ab.bookID
LEFT JOIN author AS a ON ab.authID=a.id
LEFT JOIN book_publisher AS abc ON b.id = abc.bookID
LEFT JOIN publishers AS c ON abc.publishers_id = c.id
ORDER BY title ASC
the structure is rather simple:
book:id = primary key, field1 field8
author: id = primary key, first_name, last_name, ordinal
publishers: id = primary key, publisher
book_author:
authID = primary key, references author.id
bookID = primary key, references book.id
book_publisher:
bookID = primary key, references book.id
publishers_id = primary, key references publishers.id
Question 1 is: How to set up a query for a book with 2 authors?
There are a number of problems involved, but such issues as more than 2
authors, editors and authors in a dictionary of anthology or some
compilation can by revealed in the description field. So we come down to
the problem of just plain 2 authors: do we list the same book twice with
a different author in each instance with the ordinal field showing 1 for
the display priority of the first author and 2 for the second?
Question 2 is: How to retrieve and display the two authors on one line
as Author_1 and Author_2 without some incredible conditional
contortions to achieve the goal?
--
Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
book categories
Here's a tough one... In my library I have some 62 categories where a number of books can be in several categories. Now, to relate the categories table(AS c) to the book table (AS a), I assume that I need an intermediate table book_category (AS d) linking d.bookID to the b.bookID and d.categoryID to c.id. With so many categories and (probably) thousands of books d may become quite lengthy (but, I guess it's better than 62 * 100's of lengthy fields added to b). To enter the relationships I would add instructions on my addNewBooks.php form with the input as multiple choice dropdown box (listing the categories)... so far, so good (I hope)... Now, how do I SELECT and retrieve these categories to display on the web page? From the book_category table with a WHERE statement? If so, what then? CONCAT_WS the stuff to go into the html table td? I hope I'm on the right track... or am I in deep water? Help... -- Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Problem using deterministic stored function
Hi, I am using MySQL 5.0.51a. I've got a problem with a stored function. It reads as follows: CREATE FUNCTION `_contractRoot`(temp INT) RETURNS int(11) READS SQL DATA BEGIN DECLARE _parent_id INT; REPEAT SET _parent_id = temp; SELECT parent_id INTO temp FROM contract WHERE id = _parent_id; UNTIL temp IS NULL END REPEAT; RETURN _parent_id; END There is a table contract containing row groups that form a tree. In the table, the columns id, parent_id and number are defined. Each tree root has set the NULL value for the parent_id column, while the child rows have references to the ID of another row, and by following these references from any child, the root ID will be calculated by this function. Additionaly, there is a value set in the number column if parent_id IS NOT NULL, i.e. the root row has a value set, while the other rows have number = NULL. This maps each tree to a number. (There are 1.500 rows in the contract table.) Now, let's look at these queries: mysql SELECT _contractRoot(320); ++ | _contractRoot(320) | ++ |317 | ++ 1 row in set (0.00 sec) mysql SELECT number FROM contract WHERE id = _contractRoot(320); ++ | number | ++ | 93 | ++ 1 row in set (0.06 sec) As you see, _contractRoot(320) is run in a very short period of time, while the second statement seems to run the _contractRoot function for each line that is processed by the WHERE clause, and therefore takes longer. This was not the case before the upgrade to Debian Lenny. Before (MySQL 5.0.32), the second statement also took nearly 0.00 sec. So, is this a regression? I noticed that the problem can be fixed by specifying DETERMINISTIC as an additional keyword in the CREATE FUNCTION statement. In this case, the second statement also runs in 0.00 sec. But I'm not sure if DETERMINISTIC is legal here. As you see, the function reads data from the table, and it may be that these data changes. So the question is what DETERMINISTIC means! Two options: 1.) DETERMINISTIC means that the function does not depend on variable data and will _always_ return the same value. This corresponds to what the manual says: A procedure or function is considered “deterministic” if it always produces the same result for the same input parameters, and “not deterministic” otherwise. But I'm not sure if always can be construed that strictly here. It would be inconsistent with the READS SQL DATA characteristic. 2.) DETERMINISTIC means that the function does not use any non-constant input except data from the database tables (i.e. no CURRENT_DATE(), random numbers etc.). How would the caching mechanism work in this case? In the second case, I may declare my function DETERMINISTIC, in the first case I may not. Does anybody know what is right here? I did another observations that is closely related to this: Even with the DETERMINISTIC keyword, the following takes long: mysql SELECT id, _contractRoot(320) FROM contract; +--++ | id | _contractRoot(320) | +--++ ... | 1560 |317 | | 1561 |317 | +--++ 1477 rows in set (0.06 sec) The run time seems to be independent of the use of DETERMINISTIC, but to my understanding, there is no need to execute the function more often than when doing mysql SELECT number FROM contract WHERE id = _contractRoot(320); which returns after 0.00 sec. As I said, this behaviour happens despite of DETERMINISTIC. I'm not sure if this is a bug. Thanks, Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: book categories
Hi Phil, relax you are on track! First a little theory. In this case you have the so called N-to-M relationship. While if you need to relate one book to one author(given only one author per book!) you have the so called 1-to-N relationship, in this case you have the N-to-M relationship. If you are asking yourself why one book-one author is a 1-to-N relationship do not think of the single row/record, but reason this way(again, given only one author per book): Take one row on the [books] table and ask yourself: How many possible authors have written this book? Ans: 1 Take one row on the [authors] table and ask yourself: How many possible books have been written by this authors Ans: Many (N any number) so you have a 1-to-N relationship in your example you have [books] and [categories] Take one row on the [books] table and ask yourself: To how many possible categories belongs this book? Ans: Many (N) Take one row on the [categories] table and ask yourself: How many books can belong to this category? Ans: Many (M , N is already taken!) so you have a N-to-M relationship while to implement a 1-to-N relationship you use a Foreign Key on one table, to implement a N-to-M relationship you need to use a table (cross reference) This means that while you still have the two original tables you will build a third table that connect the former two ones. This third table(Cross reference) it is just composed, at least, by two fields(two foreign keys) one referring to the primary key of the first table [books] and one referring to the primary kay of the second table [categories]. Just insert for each book as many rows as the categories to which the book belongs, for example, if you have a book with book_id=1 and various categories(1,2,3,4,5,6,7,8,9,10), to assign to the book the categories 1,3,8 insert three rows in the cross-reference table: book_id | category_id --- 1 | 1 1 | 3 1 | 8 AGAIN: CONCAT is just a string function, it is not a sql operator, and it is not giving anything that you don't already know, dont focus on CONCAT, forget it for now. Let me know Claudio Nanni 2009/2/22 PJ af.gour...@videotron.ca Here's a tough one... In my library I have some 62 categories where a number of books can be in several categories. Now, to relate the categories table(AS c) to the book table (AS a), I assume that I need an intermediate table book_category (AS d) linking d.bookID to the b.bookID and d.categoryID to c.id. With so many categories and (probably) thousands of books d may become quite lengthy (but, I guess it's better than 62 * 100's of lengthy fields added to b). To enter the relationships I would add instructions on my addNewBooks.php form with the input as multiple choice dropdown box (listing the categories)... so far, so good (I hope)... Now, how do I SELECT and retrieve these categories to display on the web page? From the book_category table with a WHERE statement? If so, what then? CONCAT_WS the stuff to go into the html table td? I hope I'm on the right track... or am I in deep water? Help... -- Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
Re: book categories
Claudio Nanni wrote: Hi Phil, relax you are on track! First a little theory. In this case you have the so called N-to-M relationship. While if you need to relate one book to one author(given only one author per book!) you have the so called 1-to-N relationship, in this case you have the N-to-M relationship. If you are asking yourself why one book-one author is a 1-to-N relationship do not think of the single row/record, but reason this way(again, given only one author per book): OK, I understand. But in my case, there are a number of books with sever authors and I'm trying to figure that out in another thread. [HOW TO QUERY(SELECT) and display MULTIPLE AUTHORS] Take one row on the [books] table and ask yourself: How many possible authors have written this book? Ans: 1 Take one row on the [authors] table and ask yourself: How many possible books have been written by this authors Ans: Many (N any number) so you have a 1-to-N relationship in your example you have [books] and [categories] Take one row on the [books] table and ask yourself: To how many possible categories belongs this book? Ans: Many (N) Take one row on the [categories] table and ask yourself: How many books can belong to this category? Ans: Many (M , N is already taken!) so you have a N-to-M relationship while to implement a 1-to-N relationship you use a Foreign Key on one table, to implement a N-to-M relationship you need to use a table (cross reference) This means that while you still have the two original tables you will build a third table that connect the former two ones. This third table(Cross reference) it is just composed, at least, by two fields(two foreign keys) one referring to the primary key of the first table [books] and one referring to the primary kay of the second table [categories]. Just insert for each book as many rows as the categories to which the book belongs, for example, if you have a book with book_id=1 and various categories(1,2,3,4,5,6,7,8,9,10), to assign to the book the categories 1,3,8 insert three rows in the cross-reference table: book_id | category_id --- 1 | 1 1 | 3 1 | 8 This I understand too and I do appreciate the input which is quite clear and informative; but my real problem is to figure out how to use a dropdown table (62 choices) to enter the data into the reference table (linking categories and books from book_categories). In fact it would be great to be able to extract the information to select the categories from the categories table and use the id field as the value field and the category_name field as the for the option in the select section of the dropdown table. Otherwise it is rather tedious to enter by hand the id and the category. :-( and I am so lazy ;-) Once I can populate the book_category table from a php-mysql form page, the the problem remains on how to display the categories attributed to each book...This is not an earth-shaking need but rather a tough challenge since is is probably quite simple to do a php-mysql page to display the books based on the book_category table. And I am not sure that concat could work in this case... it still looks like the same problem as the multiple author thingie. Phil Jourdan AGAIN: CONCAT is just a string function, it is not a sql operator, and it is not giving anything that you don't already know, dont focus on CONCAT, forget it for now. Let me know Claudio Nanni 2009/2/22 PJ af.gour...@videotron.ca Here's a tough one... In my library I have some 62 categories where a number of books can be in several categories. Now, to relate the categories table(AS c) to the book table (AS a), I assume that I need an intermediate table book_category (AS d) linking d.bookID to the b.bookID and d.categoryID to c.id. With so many categories and (probably) thousands of books d may become quite lengthy (but, I guess it's better than 62 * 100's of lengthy fields added to b). To enter the relationships I would add instructions on my addNewBooks.php form with the input as multiple choice dropdown box (listing the categories)... so far, so good (I hope)... Now, how do I SELECT and retrieve these categories to display on the web page? From the book_category table with a WHERE statement? If so, what then? CONCAT_WS the stuff to go into the html table td? I hope I'm on the right track... or am I in deep water? Help... -- Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com -- Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:
MySQL log in issues
Hi everyone, I'm having some problems getting logged in to mysql to create a new database. I have to admit, my overall skill level with mysql is 'Entry level noob'. I just haven't ever had much need to mess with it in general, so if you want to laugh, feel free. I'll understand ;) At any rate, as it has been about a year since the last time I tried to do this, I've forgotten the root password and tried reset it using the following steps: 1. Stopped the mysqld daemon process. 2. Started the mysqld daemon process with the --skip-grant-tables option. 3. Started the mysql client with the -u root option. Once logged in as root, I did: UPDATE mysql.user SET Password='newpass' WHERE User='root'; FLUSH PRIVILEGES; exit When I try to log in now: # mysql -u root -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) Can someone give me a nudge/push/drag me kicking and screaming in the right direction? Thanks everyone :) -- Tim DeBoer
Re: MySQL log in issues
You should have typed ... set password = password('newpass')
You've set root's password to the encypted value of some other string.
You ought to be able to get around this by starting mysql (the server
process, not the client) using the --skip-grant-tables option and resetting
the password.
On Sun, Feb 22, 2009 at 9:38 PM, Tim DeBoer tdeb...@gmail.com wrote:
Hi everyone,
I'm having some problems getting logged in to mysql to create a new
database.
I have to admit, my overall skill level with mysql is 'Entry level noob'.
I just haven't ever had much need to mess with it in general, so if you
want
to laugh, feel free. I'll understand ;)
At any rate, as it has been about a year since the last time I tried to do
this, I've forgotten the root password and tried reset it using the
following steps:
1. Stopped the mysqld daemon process.
2. Started the mysqld daemon process with the --skip-grant-tables option.
3. Started the mysql client with the -u root option.
Once logged in as root, I did:
UPDATE mysql.user SET Password='newpass' WHERE User='root';
FLUSH PRIVILEGES;
exit
When I try to log in now:
# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using
password: YES)
Can someone give me a nudge/push/drag me kicking and screaming in the right
direction?
Thanks everyone :)
--
Tim DeBoer
--
Jim Lyons
Web developer / Database administrator
http://www.weblyons.com
Re: MySQL log in issues
Method 1（install mysql）:
/usr/local/mysql/bin/：
./mysqladmin -u root password 'new_password'
Method 2
mysqlUPDATE user SET password=PASSWORD('new_password') WHERE user='root';
mysqlFLUSH PRIVILEGES;
Method 3:
mysqlSET PASSWORD FOR root=PASSWORD('new_password');
On Mon, Feb 23, 2009 at 11:38 AM, Tim DeBoer tdeb...@gmail.com wrote:
Hi everyone,
I'm having some problems getting logged in to mysql to create a new
database.
I have to admit, my overall skill level with mysql is 'Entry level noob'.
I just haven't ever had much need to mess with it in general, so if you
want
to laugh, feel free. I'll understand ;)
At any rate, as it has been about a year since the last time I tried to do
this, I've forgotten the root password and tried reset it using the
following steps:
1. Stopped the mysqld daemon process.
2. Started the mysqld daemon process with the --skip-grant-tables option.
3. Started the mysql client with the -u root option.
Once logged in as root, I did:
UPDATE mysql.user SET Password='newpass' WHERE User='root';
FLUSH PRIVILEGES;
exit
When I try to log in now:
# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using
password: YES)
Can someone give me a nudge/push/drag me kicking and screaming in the right
direction?
Thanks everyone :)
--
Tim DeBoer
--
福建中金在线网络股份有限公司
中国人的财经门户网站
黄镜（John.huang）
http://www.cnfol.com
email：huangj...@cnfol.com
Tel：0591-87986223
Address：福建省福州市软件大道软件园A区25座中金在线大厦
