473,691 Members | 2,476 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 3069
"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.athenanew s.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*******@plan etx.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.andyhsoftwa re.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.andyhsoftwa re.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 ServiceCompanie s
(table2):

Table: ServiceTypes (table1)
Columns: ServiceTypeID, ServiceTypeName

Table: ServiceCompanie s (table2)
Columns: ServiceTypeID, CompanyID

ServiceTypes looks just like the example, but ServiceCompanie s 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
ServiceCompanie s 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.ServiceTypeI D FROM ServiceTypes ST
LEFT JOIN ServiceCompanie s SC ON ST.ServiceTypeI D = SC.ServiceTypeI D
WHERE SC.ServiceTypeI D 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 ServiceCompanie s
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.c om...
On Thu, 12 Aug 2004 05:57:56 GMT, "Xenophobe" <xe*******@plan etx.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.andyhsoftwa re.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*******@plan etx.com> wrote:
There are still two tables, ServiceTypes (table1) and ServiceCompanie s
(table2):

Table: ServiceTypes (table1)
Columns: ServiceTypeID, ServiceTypeName

Table: ServiceCompanie s (table2)
Columns: ServiceTypeID, CompanyID

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

ServiceTypeI D, 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.ServiceTypeI D FROM ServiceTypes ST
LEFT JOIN ServiceCompanie s SC ON ST.ServiceTypeI D = SC.ServiceTypeI D
WHERE SC.ServiceTypeI D 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 ServiceCompanie s
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 ServiceCompanie s;
+---------------+-----------+
| ServiceTypeID | CompanyID |
+---------------+-----------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 1 | 2 |
| 2 | 2 |
| 4 | 2 |
+---------------+-----------+
6 rows in set (0.00 sec)

mysql> SELECT ST.ServiceTypeI D
-> FROM ServiceTypes ST
-> LEFT JOIN ServiceCompanie s SC
-> ON (ST.ServiceType ID = SC.ServiceTypeI D
-> AND SC.CompanyID = 1)
-> WHERE SC.ServiceTypeI D 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.ServiceTypeI D,
-> SC.ServiceTypeI D,
-> SC.CompanyID
-> FROM ServiceTypes ST
-> LEFT JOIN ServiceCompanie s SC
-> ON (ST.ServiceType ID = SC.ServiceTypeI D)
-> 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.ServiceTypeI D,
-> SC.ServiceTypeI D,
-> SC.CompanyID
-> FROM ServiceTypes ST
-> LEFT JOIN ServiceCompanie s SC
-> ON (ST.ServiceType ID = SC.ServiceTypeI D
-> 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.andyhsoftwa re.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
1508
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 read http://www.mysql.com/doc/en/JOIN.html, but I'm just not groking it. When I try to do the same thing, I can't "convert" one field into the data from another. Here's what I have. To test this concept I made two tables on a database called db_join:
1
1510
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| +--------+------+------+ | 1 | 7 | 110 | | 2 | 23 | 343 |
2
1440
by: G0ng | last post by:
I have the following tables: students id, name,.... lessons id,description,.... studentslessons id,studentid,lessonid,grade
2
1327
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 contains a list of tag names and tagIDs. When a user adds an entry to the Nodes table they can specify one or more tags for the entry. Then when the form is posted if there were multiple tags in the post their tagIDs are comma seperated before being inserted in the tagID column of the Nodes table....
1
2822
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, amount. A customer can be paid three times. for each payment, a record is added in STATEMENT and either cheque_no1, cheque_no2 or cheque_no3 updated with the value in STATEMENT.cheque_no in the new record. How can i get loan_serial, customer_id, and the total amount paid for each loan in mysql...
5
1231
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, table2.title, table2.category FROM table1, table2 WHERE table1.category = table2.category ORDER by table1.id DESC"); while($record = mysql_fetch_array($some_query))
2
4453
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 another contain withdrawals, both tables have date and amount columns. other columns are different. i want to select date and amount columns from both tables and view it as a single table, so i can sort it or get the sum please let me know whether it is possible and how? Thank you
2
1986
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 (each country has the same id in the different languages) id | country_id | name | lang 2. cities-> list of all cities in 4 languages (each city is associated with the country_id and has the same id for the different languages)
8
11661
by: superaktieboy | last post by:
Hi i have the following SQL tables: prefix_bookings: +---------+------------+-----------------+-------------+---------------+ | book_id | book_place | book_customerid | book_adults | book_children | +---------+------------+-----------------+-------------+---------------+ | 3 | 1 | 1 | 2 | 2 | | 42 | 1 | 1 | 2 | 2 | | 41 | 1 ...
0
8599
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
8531
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9079
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8951
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8794
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6457
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Duprť who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4322
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
2965
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
1952
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.