----- Original Message ----- From: "Marko Knezevic" <[EMAIL PROTECTED]> To: "MySQL list" <[EMAIL PROTECTED]>; "Rhino" <[EMAIL PROTECTED]> Sent: Tuesday, August 31, 2004 6:11 PM Subject: Re: Multiple JOINs
> Rhino wrote: > > > Your design seems fine, although you haven't explained how the field > > and field_lookup tables join to the others so I am making some > > assumptions. > > Table field_lookup has two values defined as primary keys: ID_company and > ID_field > Thus, using simple query like: > > SELECT company.name, field > FROM company, field, field_lookup > WHERE company.ID = field_lookup.ID_company && field.ID = > field_lookup.ID_field > > i can get which company does what. In the matter of fact, i found some > solution to my problem by using following query: > > SELECT company.name, city, state, street, number, company_telephone.naziv AS > tel_naziv, field > FROM company, field, field_lookup > LEFT JOIN company_address ON company.ID = company_address.ID_company > LEFT JOIN company_telephone ON company_address.ID = > company_telephone.ID_company_address > WHERE company.ID = field_lookup.ID_company && field.ID = > field_lookup.ID_field > ORDER BY rank > > But now, if one company doesn't have its field of work entered it won't be > displayed. > > > I think your problem is that some of your Left Joins should be Inner > > Joins. Are you familiar with the difference? Remember, a left join > > will pick up "orphan rows" - rows that don't have an equal, non-null > > key in the other table - and add them to the result. I think that's > > what is happening here. > > But if i use INNER JOIN i will see only subjects who have all the data > entered. You can for instance have subject who has only address and doesn't > have phone number and it won't be listed. I am not so familiar with > different types of JOIN so any help is appreciated. > Okay, you've convinced me that you need some Left Joins ;-) I think you'll have to use a mix of Left Joins and Inner Joins. In the cases where you need a row even if something doesn't exist, like a subject who has an address but no phone number, use a Left Join. In any case where a row shouldn't appear unless there is a match between the keys, use an Inner Join. I wish I could be more precise than that but I don't know your particular business as well as you do. To help clarify the difference between Inner and Left Joins, I'm going to paste in part of an email I wrote last week helping someone with this point. You probably know a lot of the things in there already but I think this should help you understand the difference between an Inner Join and a Left Join. Please post again if you anything here isn't clear or you still need help despite my "instant tutorial". ---------------------------------------------------------------------------- -------------------------------------- I've put together some examples of inner and outer joins for you. I'll use a fixed font throughout so that the table columns are lined up for easier viewing. Before I show the examples to you, a quick review of the concept of a join. A join is the process of concatenating one row of a table to one or more rows of another table. [Concatenating is the process of adding something on to the end of something else. If I join a row from Table A to a row from Table B, the resulting row (or rows) has the columns of Table A followed by the columns of Table B, all in the same result set.] The two tables are still separate and distinct; they have not been permanently combined nor have they been changed in any way.] The usual basis for a join is equality of non-null values. This means that a row in Table A normally only joins to a row in Table B if the two rows have equal non-null keys. We'll see this in detail in a minute. Okay, let's imagine these two tables: Departments: +--------+-------------------+ | deptno | deptname | +--------+-------------------+ | A00 | Administration | | B01 | Accounting | | C01 | Education | | D11 | Manufacturing | | NULL | Future department | +--------+-------------------+ Employees: +-------+------------+----------+----------+ | empno | name | workdept | salary | +-------+------------+----------+----------+ | 1 | Allan Abel | C01 | 50000.00 | | 2 | Bert Baker | A00 | 60000.00 | | 3 | Cal Carter | B01 | 80000.00 | | 4 | Dave Doone | A00 | 40000.00 | | 5 | Ed Ellings | NULL | 60000.00 | | 6 | Fred Fermi | Z99 | 55000.00 | +-------+------------+----------+----------+ If you look at these two tables, they have only one column in common. (Tables that are being joined normally have at least one thing in common and sometimes more.) The thing that these tables have in common is that each table contains department numbers. The Departments table calls them 'deptno' and the Employees table calls them 'workdept' but they are the same thing. The Departments table contains one row for each of the departments in the company. The primary key of the table is the department number, deptno. The Employees table contains one row for each employee in the company. The primary key is the employee number. Each row of the Employees table contains the department where the employee works; this is found in the workdept column. It would be very reasonable to join the Departments table and the Employees table if we want to know something about both the departments and the employees. Let's say that we want to see each employee in the Employees table and then show information about that person's department. Traditionally, we only join rows that have equal and non-null keys. In this case, our key is the deptno/workdept. That means that for every row in the Employees table, we should find the corresponding row in the Departments table but only if both of those department numbers are equal and not null. This is usually called an inner join or sometimes a natural join. Here is the result of doing an inner join on this data: +--------+----------------+-------+------------+----------+ | deptno | deptname | empno | name | salary | +--------+----------------+-------+------------+----------+ | A00 | Administration | 2 | Bert Baker | 60000.00 | | A00 | Administration | 4 | Dave Doone | 40000.00 | | B01 | Accounting | 3 | Cal Carter | 80000.00 | | C01 | Education | 1 | Allan Abel | 50000.00 | +--------+----------------+-------+------------+----------+ Notice that only 4 of the 6 employees made it into the result set. Employee 5, Ed Elling didn't make it because his workdept value, null, is not equal to any of the deptno values in the Departments table. [Null is NOT considered equal to another null for the purposes of joining!! Therefore, you can't join the Ed Elling row to the Future Department row.] Employee 6, Fred Fermi, didn't make it because his workdept value, Z99, is not equal to any of the deptno values in the Departments table. Notice also that one row of the Departments table didn't make it into the result, the department whose name value is Future Department. All of this is working exactly right according to the theory that underlies relational databases. There are two ways to write the SQL for this inner join: select deptno, deptname, empno, name, salary from employees e, departments d where e.workdept = d.deptno order by deptno, empno; select deptno, deptname, empno, name, salary from employees e inner join departments d on e.workdept = d.deptno order by deptno, empno; These queries give the same result set and both are valid. The first query utilizes what is sometimes called the 'traditional' syntax while the second query uses the newer syntax. The left, right and full outer joins utilize only the newer syntax. Therefore, it may be best to write all inner and outer joins in the newer syntax to achieve maximum consistency of style. Then again, if you can keep the two formats straight in your own mind, there's no great harm in using traditional syntax for inner joins and the newer syntax for outer joins. Okay, let's consider the details of the two queries. In both queries, two table names are identified in the FROM clause. That means that those tables will be joined, regardless of what else you do in the query. However, other parts of the query will strongly affect *how* the tables are joined. Normally, we want to have an inner join or one of the three kinds of outer joins, right, left, or full. To achieve any of these, we need to specify a joining condition to tell the database manager which columns are going to be the basis of the join. In the traditional syntax, the joining condition is written in the WHERE clause; in this case it is: e.workdept = d.deptno. [The 'e' and the 'd' in the joining condition refer back to the FROM clause where we defined 'e' as a nickname for the Employees table and 'd' as a nickname for the Departments table.] In the second query, the joining condition appears in the ON clause, which is actually part of the FROM clause but is otherwise identical: e.workdept = d.deptno. Note that instead of having a comma between the two tables names, the second query has the phrase 'inner join' instead. The SELECT and ORDER BY clauses of the two queries are identical. [By the way, the second query has no WHERE clause but it *could* have a normal WHERE clause if you wanted one; it simply doesn't have a WHERE in this case because you don't have any other conditions to translate from the first query.] Is the inner join clear to you now? If not, you should probably stop reading here and ask me questions so that I can clarify the inner join. Otherwise, keep reading and we'll look at outer joins. There are three kinds of outer joins: left outer join, right outer join, and full outer join. They are usually abbreviated to: left join, right join, and full join. Any of these three outer joins contain all of the results of the inner join PLUS other stuff. Exactly which other stuff appears in the result depends on whether the outer join is a left join, right join or full join. That's the first critical piece of information you need. The second critical thing you need is the concept of the 'orphan row'. This, again, is a term I use; I don't recall if there is an 'official' term that is more widely used. The orphan rows in a table are all of the ones that aren't picked up by an inner join. In the case of our Employees table, the orphan rows were Employees 5 and 6; they were the ones whose workdept value didn't match any of the deptno values in the Departments table. In the case of our Departments table, the orphan row is 'Future Department' since its deptno value doesn't match any workdept value in the Employees table. [Remember: nulls are NOT considered equal when it comes to joining! That's why the null deptno for 'Future Department' doesn't match the null workdept for Employee 5.] Now, the easiest idea of all: the 'right' table and the 'left' table. When you write a FROM clause, the table you mention first (or on the left) is the 'left' table and the table you mention second (or on the right) is the 'right' table. It's just that easy. Okay, so what is a left join? Simple: a left join is all of the rows that would be obtained by an inner join PLUS all the orphan rows from the 'left' table. A right join is all of the rows that would be obtained by an inner join PLUS all of the orphan rows from the 'right' table. And a full join is all of the rows that would be obtained by an inner join PLUS all of the orphans from the 'left' table AND all of the orphans from the 'right' table. Here is a left join for our two tables, followed by the result set: select deptno, deptname, empno, name, salary from employees e left join departments d on e.workdept = d.deptno order by deptno, empno; +--------+----------------+-------+------------+----------+ | deptno | deptname | empno | name | salary | +--------+----------------+-------+------------+----------+ | NULL | NULL | 5 | Ed Ellings | 60000.00 | | NULL | NULL | 6 | Fred Fermi | 55000.00 | | A00 | Administration | 2 | Bert Baker | 60000.00 | | A00 | Administration | 4 | Dave Doone | 40000.00 | | B01 | Accounting | 3 | Cal Carter | 80000.00 | | C01 | Education | 1 | Allan Abel | 50000.00 | +--------+----------------+-------+------------+----------+ Notice the NULLs in the first two rows? Since employees 5 and 6 don't match to any of the rows in the Departments table, MySQL displays NULLs for the deptno and deptname. Here is a right join for our two tables, followed by the result set: select deptno, deptname, empno, name, salary from employees e right join departments d on e.workdept = d.deptno order by deptno, empno; +--------+-------------------+-------+------------+----------+ | deptno | deptname | empno | name | salary | +--------+-------------------+-------+------------+----------+ | NULL | Future department | NULL | NULL | NULL | | A00 | Administration | 2 | Bert Baker | 60000.00 | | A00 | Administration | 4 | Dave Doone | 40000.00 | | B01 | Accounting | 3 | Cal Carter | 80000.00 | | C01 | Education | 1 | Allan Abel | 50000.00 | | D11 | Manufacturing | NULL | NULL | NULL | +--------+-------------------+-------+------------+----------+ Notice the NULLs in the first and last rows again indicate information which can't be found since there is no corresponding row in the other table. Here is a full outer join for our two tables, NOT followed by a result set. Full joins are not supported yet in MySQL! select deptno, deptname, empno, name, salary from employees e full join departments d on e.workdept = d.deptno order by deptno, empno; You'll have to imagine the result but it would include the rows from the inner join and the orphans from BOTH the right and left tables with NULLs whenever a value isn't known. I don't recall when MySQL gets FULL JOINs. Two final thoughts: 1. There are other kinds of joins, including CROSS JOIN, but there is rarely a good reason to use them. 2. You can join umpteen tables together in a single query. You are already doing that using the traditional syntax. Here is how you join three tables with the newer syntax: Select .... from tableA a inner join tableB b on a.col1 = b.col2 inner join tableC c on c.col1 = b.col4 ---------------------------------------------------------------------------- ------ Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
