List:General Discussion« Previous MessageNext Message »
From:PJ Date:February 22 2009 6:58pm
Subject:Re: left joins & concat
View as plain text  
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.gourmet@stripped> 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 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.gourmet@stripped
> <mailto:af.gourmet@stripped>>
>>>
>>>     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 is not very helpful in any explanations...
>>>
>>>     I sure would like to hear some clear explanations...
>>>     TIA
>>>
>>>     --
>>>
>>>     Phil Jourdan --- pj@stripped <mailto:pj@stripped>
>>>       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=1
>>>
>>>
>>>       
>> --
>>
>> Phil Jourdan --- pj@stripped
>>   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=1
>>
>>
>>     
>
>   


-- 

Phil Jourdan --- pj@stripped
   http://www.ptahhotep.com
   http://www.chiccantine.com

Thread
left joins & concatPJ22 Feb
  • Re: left joins & concatClaudio Nanni22 Feb
    • Re: left joins & concatPJ22 Feb
    • Re: left joins & concatPJ22 Feb
      • Re: left joins & concatWalter Heck22 Feb
        • Re: left joins & concatPJ22 Feb