MySQL JOIN Query--help

[Xenophobe]

I know this isn't a MySQL forum, but my question is related to a PHP
project.

I have two tables.

table1
table2

"table1" contains 2 columns, ID and FirstName:

1, John
2, Mary
3, Bill
4, Troy

"table2" is a bridge table and contains ID references to the first:

1
3

I would like to return a recordset of IDs from table1 that DO NOT
exist in table2:

2
4

I tried various combinations of the following query without success (i.e. no
results returned):

SELECT Table1.ID
FROM Table1 LEFT JOIN Table2
ON Table1.ID = Table2.ID
WHERE Table2.ID Is NULL

All suggestions/comments appreciated.

[steve]

"Xenophobe" wrote:

I know this isn’t a MySQL forum, but my question is related to a
PHP
project.

I have two tables.

table1
table2

"table1" contains 2 columns, ID and FirstName:

1, John
2, Mary
3, Bill
4, Troy

"table2" is a bridge table and contains ID references to the first:
1
3

I would like to return a recordset of IDs from table1 that DO NOT
exist in table2:

2
4

I tried various combinations of the following query without success
(i.e. no
results returned):

SELECT Table1.ID
FROM Table1 LEFT JOIN Table2
ON Table1.ID = Table2.ID
WHERE Table2.ID Is NULL

All suggestions/comments appreciated.

Looks good to me. The field in table 2 must be declared not null
http://dev.mysql.com/doc/mysql/en/JOIN.html
give that a try, it may fix it.

--
http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/PHP-MySQL-JO...ict138940.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=464522

[Xenophobe]

Steve,

Hmmm... it's the weirdest thing.

I can get a list of records that IS NOT NULL, but not a list that IS NULL.

I tried changing the JOIN direction, but no luck.

According to the MySQL docs the ID column must be set as NOT NULL. This is
the way my table is defined.

Any other suggestions?

"steve" <Us************@dbForumz.com> wrote in message
news:41********@news.athenanews.com...

"Xenophobe" wrote:

> I know this isn't a MySQL forum, but my question is related to a
> PHP
> project.
>
> I have two tables.
>
> table1
> table2
>
> "table1" contains 2 columns, ID and FirstName:
>
> 1, John
> 2, Mary
> 3, Bill
> 4, Troy
>
> "table2" is a bridge table and contains ID references to the first:

>
> 1
> 3
>
> I would like to return a recordset of IDs from table1 that DO NOT
> exist in table2:
>
> 2
> 4
>
> I tried various combinations of the following query without success
> (i.e. no
> results returned):
>
> SELECT Table1.ID
> FROM Table1 LEFT JOIN Table2
> ON Table1.ID = Table2.ID
> WHERE Table2.ID Is NULL
>
> All suggestions/comments appreciated.

Looks good to me. The field in table 2 must be declared not null
http://dev.mysql.com/doc/mysql/en/JOIN.html
give that a try, it may fix it.

--
http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL:

http://www.dbForumz.com/PHP-MySQL-JO...ict138940.html Visit Topic URL to contact author (reg. req'd). Report abuse:

http://www.dbForumz.com/eform.php?p=464522

[Andy Hassall]

On Thu, 12 Aug 2004 05:57:56 GMT, "Xenophobe" <xe*******@planetx.com> wrote:

I know this isn't a MySQL forum, but my question is related to a PHP
project.

I have two tables.

table1
table2

"table1" contains 2 columns, ID and FirstName:

1, John
2, Mary
3, Bill
4, Troy

"table2" is a bridge table and contains ID references to the first:

1
3

I would like to return a recordset of IDs from table1 that DO NOT
exist in table2:

2
4

I tried various combinations of the following query without success (i.e. no
results returned):

SELECT Table1.ID
FROM Table1 LEFT JOIN Table2
ON Table1.ID = Table2.ID
WHERE Table2.ID Is NULL

All suggestions/comments appreciated.

Works for me.

mysql> desc table1;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| ID | int(11) | YES | | NULL | |
| name | varchar(8) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> desc table2;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| ID | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> select * from table1;
+------+------+
| ID | name |
+------+------+
| 1 | John |
| 2 | Mary |
| 3 | Bill |
| 4 | Troy |
+------+------+
4 rows in set (0.00 sec)

mysql> select * from table2;
+------+
| ID |
+------+
| 1 |
| 3 |
+------+
2 rows in set (0.00 sec)

mysql> SELECT table1.ID
-> FROM table1 LEFT JOIN table2
-> ON table1.ID = table2.ID
-> WHERE table2.ID Is NULL;
+------+
| ID |
+------+
| 2 |
| 4 |
+------+
2 rows in set (0.00 sec)

--
Andy Hassall / <an**@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool
(v1.4.0 new 1st Aug 2004)

[Andy Hassall]

On 12 Aug 2004 03:48:37 -0400, steve <Us************@dbForumz.com> wrote:

Looks good to me. The field in table 2 must be declared not null
http://dev.mysql.com/doc/mysql/en/JOIN.html
give that a try, it may fix it.

Doesn't matter whether it's not null or nullable, just that the field tested
for nullity on the null-supplemented side of the outer join (i.e. Table2 in
this case) has non-null values for each row in the result set, so that null
values selected from the table aren't mistaken for the null-supplemented rows
generated by the outer join.

Being declared not null guarantees this, but isn't required, and would not
itself make any difference to the results.

--
Andy Hassall / <an**@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool
(v1.4.0 new 1st Aug 2004)

[Xenophobe]

Andy,

Thanks for your detailed reply. You're right, my example tables and data
work as expected.

I discovered the source of my query dilemma, but am uncertain on how to
solve it.

Allow me to skip the fictitious example and use the real tables and data.

There are still two tables, ServiceTypes (table1) and ServiceCompanies
(table2):

Table: ServiceTypes (table1)
Columns: ServiceTypeID, ServiceTypeName

Table: ServiceCompanies (table2)
Columns: ServiceTypeID, CompanyID

ServiceTypes looks just like the example, but ServiceCompanies has a second
column called "CompanyID". ServiceTypes and CompanyID record combinations
must be unique. In other words, a company is only allow to have one of each
service associated with it.

ServiceTypes contains 5 different service types:

ServiceTypeID, ServiceTypeName
1, Plumbing
2, Electrical
3, Flooring
4, Drywall
5, Landscaping

A single company can offer 1 or more services. These are stored in
ServiceCompanies bridge table:

ServiceTypeID, CompanyID
1, 1
2, 1
3, 1
1, 2
2, 2

....and so on.

So here's my challenge. I would like to return a recordset of services that
are NOT currently associated with a company. This would allow them to add
any additional services that aren't already defined.

I have played with many variations of the following query and read the docs,
but without success.

SELECT ST.ServiceTypeID FROM ServiceTypes ST
LEFT JOIN ServiceCompanies SC ON ST.ServiceTypeID = SC.ServiceTypeID
WHERE SC.ServiceTypeID IS NULL and SC.CompanyID = ?

No matter what, I always get zero results. The reason for this seems to be
that the query finds other references to ServiceTypeID in ServiceCompanies
and is ignoring the CompanyID specific portion of the WHERE clause.

This seems to be a query syntax related issue, but I have been unable to
find the right syntax.

Any suggestions would be greatly appreciated!
"Andy Hassall" <an**@andyh.co.uk> wrote in message
news:0s********************************@4ax.com...

On Thu, 12 Aug 2004 05:57:56 GMT, "Xenophobe" <xe*******@planetx.com> wrote:

I know this isn't a MySQL forum, but my question is related to a PHP
project.

I have two tables.

table1
table2

"table1" contains 2 columns, ID and FirstName:

1, John
2, Mary
3, Bill
4, Troy

"table2" is a bridge table and contains ID references to the first:

1
3

I would like to return a recordset of IDs from table1 that DO NOT
exist in table2:

2
4

I tried various combinations of the following query without success (i.e. noresults returned):

SELECT Table1.ID
FROM Table1 LEFT JOIN Table2
ON Table1.ID = Table2.ID
WHERE Table2.ID Is NULL

All suggestions/comments appreciated.

Works for me.

mysql> desc table1;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| ID | int(11) | YES | | NULL | |
| name | varchar(8) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> desc table2;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| ID | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> select * from table1;
+------+------+
| ID | name |
+------+------+
| 1 | John |
| 2 | Mary |
| 3 | Bill |
| 4 | Troy |
+------+------+
4 rows in set (0.00 sec)

mysql> select * from table2;
+------+
| ID |
+------+
| 1 |
| 3 |
+------+
2 rows in set (0.00 sec)

mysql> SELECT table1.ID
-> FROM table1 LEFT JOIN table2
-> ON table1.ID = table2.ID
-> WHERE table2.ID Is NULL;
+------+
| ID |
+------+
| 2 |
| 4 |
+------+
2 rows in set (0.00 sec)

--
Andy Hassall / <an**@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool
(v1.4.0 new 1st Aug 2004)

[Andy Hassall]

On Fri, 13 Aug 2004 04:02:13 GMT, "Xenophobe" <xe*******@planetx.com> wrote:

There are still two tables, ServiceTypes (table1) and ServiceCompanies
(table2):

Table: ServiceTypes (table1)
Columns: ServiceTypeID, ServiceTypeName

Table: ServiceCompanies (table2)
Columns: ServiceTypeID, CompanyID

ServiceTypeID, ServiceTypeName
1, Plumbing
2, Electrical
3, Flooring
4, Drywall
5, Landscaping

ServiceTypeID, CompanyID
1, 1
2, 1
3, 1
1, 2
2, 2

So here's my challenge. I would like to return a recordset of services that
are NOT currently associated with a company. This would allow them to add
any additional services that aren't already defined.

I have played with many variations of the following query and read the docs,
but without success.

SELECT ST.ServiceTypeID FROM ServiceTypes ST
LEFT JOIN ServiceCompanies SC ON ST.ServiceTypeID = SC.ServiceTypeID
WHERE SC.ServiceTypeID IS NULL and SC.CompanyID = ?

No matter what, I always get zero results. The reason for this seems to be
that the query finds other references to ServiceTypeID in ServiceCompanies
and is ignoring the CompanyID specific portion of the WHERE clause.

You're very close, but your problem is where you've put one of the conditions.
Solution first, then I'll have a go at explaining it:

mysql> select * from ServiceTypes;
+---------------+-----------------+
| ServiceTypeID | ServiceTypeName |
+---------------+-----------------+
| 1 | Plumbing |
| 2 | Electrical |
| 3 | Flooring |
| 4 | Drywall |
| 5 | Landscaping |
+---------------+-----------------+
5 rows in set (0.00 sec)

mysql> select * from ServiceCompanies;
+---------------+-----------+
| ServiceTypeID | CompanyID |
+---------------+-----------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 1 | 2 |
| 2 | 2 |
| 4 | 2 |
+---------------+-----------+
6 rows in set (0.00 sec)

mysql> SELECT ST.ServiceTypeID
-> FROM ServiceTypes ST
-> LEFT JOIN ServiceCompanies SC
-> ON (ST.ServiceTypeID = SC.ServiceTypeID
-> AND SC.CompanyID = 1)
-> WHERE SC.ServiceTypeID IS NULL;
+---------------+
| ServiceTypeID |
+---------------+
| 4 |
| 5 |
+---------------+
2 rows in set (0.00 sec)

Why does the condition need to be inside the LEFT JOIN join condition? Well,
you're looking for rows that are for CompanyID=1 that aren't in ServiceTypes,
so having the condition as an ordinary WHERE clause will never match - the rows
aren't there. Consider this, without the IS NULL test:

mysql> SELECT ST.ServiceTypeID,
-> SC.ServiceTypeID,
-> SC.CompanyID
-> FROM ServiceTypes ST
-> LEFT JOIN ServiceCompanies SC
-> ON (ST.ServiceTypeID = SC.ServiceTypeID)
-> WHERE SC.CompanyID = 1;
+---------------+---------------+-----------+
| ServiceTypeID | ServiceTypeID | CompanyID |
+---------------+---------------+-----------+
| 1 | 1 | 1 |
| 2 | 2 | 1 |
| 3 | 3 | 1 |
+---------------+---------------+-----------+
3 rows in set (0.00 sec)

Its filtered it down to those services that CompanyID=1 does - the WHERE
clause is applied after the outer join, and now you don't have the rows that
you wanted to pick, as the WHERE has filtered off the rows the outer join adds.

Putting the CompanyID as part of the outer join condition gives you:

mysql> SELECT ST.ServiceTypeID,
-> SC.ServiceTypeID,
-> SC.CompanyID
-> FROM ServiceTypes ST
-> LEFT JOIN ServiceCompanies SC
-> ON (ST.ServiceTypeID = SC.ServiceTypeID
-> AND SC.CompanyID = 1);
+---------------+---------------+-----------+
| ServiceTypeID | ServiceTypeID | CompanyID |
+---------------+---------------+-----------+
| 1 | 1 | 1 |
| 2 | 2 | 1 |
| 3 | 3 | 1 |
| 4 | NULL | NULL |
| 5 | NULL | NULL |
+---------------+---------------+-----------+
5 rows in set (0.01 sec)

Then you just filter off those with one of the SC columns that are null,
ending up with the solution above.

--
Andy Hassall / <an**@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool