[SOLVED] Trouble with simple JOIN

[benjudy]

Hi,

 

I'm a total newbie to the whole JOIN idea. Need some help with a simple one (I think) to get me started.

 

Here are my two MYSQL tables:

 

table name = designers

columns =

        designer_id

        first_name

        last_name

 

table name = projects

columns =

        id

        project_name

        designer_id

 

As you can see, the designers table just contains the individual's first name and last name, with a unique designer_id for each row.

 

The projects table has a unique id, a project_name and a designer_id. The designer_id is an integer which should correspond to a row from the designer table.

 

I'm having trouble coming up with a query that will select all projects assigned to an individual designer.

 

Example:

 

Let's say I want to return all projects assigned to a designer named Tom Smith.

 

According to my (limited) understanding of JOIN, the following query should give me all of the projects assigned to Tom:

SELECT * FROM projects

JOIN designers ON (projects.designer_id = designers.desginer_id)

WHERE designer.first_name='Tom' AND designer.last_name='Smith'

 

But... it doesn't seem to work. Any idea what I'm doing wrong?

 

Thanks!

[obsidian]

Try this:

SELECT * FROM projects
INNER JOIN designers USING (designer_id)
WHERE first_name = 'Tom' AND last_name = 'Smith';

 

Also, double check to see that the simple query for "Tom Smith" returns the expected results.

[benjudy]

Thanks, obsidian! You got me pointed in the right direction and I have solved the problem. Much appreciated!

[fenway]

Try this:

SELECT * FROM projects
INNER JOIN designers USING (designer_id)
WHERE first_name = 'Tom' AND last_name = 'Smith';

 

Also, double check to see that the simple query for "Tom Smith" returns the expected results.

I caution the use of USING... not only is this not sql-standard AFAIK, but SELECT * expansion works a bit differently in v5+:

 

Previously, a USING clause could be rewritten as an ON clause that compares corresponding columns. For example, the following two clauses were semantically identical:

 

a LEFT JOIN b USING (c1,c2,c3)

a LEFT JOIN b ON a.c1=b.c1 AND a.c2=b.c2 AND a.c3=b.c3

 

Now the two clauses no longer are quite the same:

 

    *      With respect to determining which rows satisfy the join condition, both joins remain semantically identical.

    *      With respect to determining which columns to display for SELECT * expansion, the two joins are not semantically identical. The USING join selects the coalesced value of corresponding columns, whereas the ON join selects all columns from all tables. For the preceding USING join, SELECT * selects these values:

 

      COALESCE(a.c1,b.c1), COALESCE(a.c2,b.c2), COALESCE(a.c3,b.c3)

 

      For the ON join, SELECT * selects these values:

 

      a.c1, a.c2, a.c3, b.c1, b.c2, b.c3

 

      With an inner join, COALESCE(a.c1,b.c1) is the same as either a.c1 or b.c1 because both columns will have the same value. With an outer join (such as LEFT JOIN), one of the two columns can be NULL. That column will be omitted from the result.