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: 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: 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],
left joins concat
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: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
