472,351 Members | 1,520 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,351 software developers and data experts.

MySQL JOIN Query--help

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.
Jul 17 '05 #1
6 2970
"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
Jul 17 '05 #2
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
Jul 17 '05 #3
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)
Jul 17 '05 #4
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)
Jul 17 '05 #5
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)

Jul 17 '05 #6
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
Jul 17 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: LRW | last post by:
This is mySQL 101. I know it's supposed to be super simple, but I'm an idiot. I can't get my join to work (1st time I've tried doing joins.) I've...
1
by: anonymous | last post by:
Hey guys, Putting it simply, here is my situation I have one table with records like this: +--------+------+------+ | index | name | style|...
2
by: G0ng | last post by:
I have the following tables: students id, name,.... lessons id,description,.... studentslessons id,studentid,lessonid,grade
2
by: JP2006 | last post by:
I have a situation where I have 2 tables - NODES and TAGS. Nodes is the main table and contains various columns one of which is tagID. Tags...
1
by: wakello | last post by:
Hi I have two tables: LOANS: loan_serial, customer_id, loan_amount, cheque_no1, cheque_no2, cheque_no3, period STATEMENT: customer_id, cheque_no,...
5
by: = poster = | last post by:
Hi all , I have the following problem : $some_query = mysql_query( "SELECT table1.id, table1.category, table1.author, table1.title,...
2
chathura86
by: chathura86 | last post by:
i have two tables with some common column names, i want to create a table joining these tables vertically ex. one table contain deposits and...
2
bilibytes
by: bilibytes | last post by:
hi all, i am trying to get all the names of the restaurants in a city. i have 3 tables: 1. countries -> list of countries in 4 languages...
8
by: superaktieboy | last post by:
Hi i have the following SQL tables: prefix_bookings: +---------+------------+-----------------+-------------+---------------+ | book_id |...
0
better678
by: better678 | last post by:
Question: Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct? Answer: Java is an object-oriented...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
0
jalbright99669
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
0
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
2
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
0
hi
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific...
0
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web...
0
Oralloy
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the...
0
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.