473,396 Members | 1,689 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

How do I select all records in one table that have NO related records in another table?


I can't get my head around this:

I want to select all IDs from table A that do not have a related record in
table B according to some condition:

Table A contains, say, Parents and table B contains Children. I want to
select all Parents that have no children called "Sally" (this is a noddy
example, reminds me of being at Uni again :) ).

Any ideas?
Thanks

Jul 20 '05 #1
2 2613
Try one of these:

SELECT A.id
FROM A
WHERE NOT EXISTS
(SELECT *
FROM B
WHERE B.id = A.id
AND B.name = 'Sally')

SELECT A.id
FROM A
LEFT JOIN B
ON A.id = B.id
AND B.name = 'Sally'
WHERE B.id IS NULL

--
David Portas
SQL Server MVP
--
Jul 20 '05 #2
Ahh, makes sense. Thankyou very much

"David Portas" <RE****************************@acm.org> wrote in message
news:G-********************@giganews.com...
Try one of these:

SELECT A.id
FROM A
WHERE NOT EXISTS
(SELECT *
FROM B
WHERE B.id = A.id
AND B.name = 'Sally')

SELECT A.id
FROM A
LEFT JOIN B
ON A.id = B.id
AND B.name = 'Sally'
WHERE B.id IS NULL

--
David Portas
SQL Server MVP
--

Jul 20 '05 #3

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

Similar topics

4
by: Denis St-Michel | last post by:
Hello All, Hope some Guru will be able to help me with this. Let's take this example table A ------------------------------------------------------------------------------- id | ...
6
by: Michael | last post by:
I have two tables with a 1-many relationship. I want to write a select statement that looks in the table w/many records and compares it to the records in the primary table to see if there are any...
3
by: William Gill | last post by:
I can't help but think I'm re-inventing the wheel if I have to code my own interface! Isn't there some script, php code, or something (modifiable / customizable) available that lets me select...
3
by: Wim Roffil | last post by:
Hi, When I do a select with a limit I get always the same records. Usually this is the desidered effect. But now I want to do a random select. So every time I do a select I should get a...
1
by: Andrew McNab | last post by:
Hi folks, I have a problem with an MS Access SQL query which is being used in an Access Report, and am wondering if anyone can help. Basically, my query (shown below) gets some records from a...
1
by: Dave | last post by:
I have a form which is based on a table (a) with over 6000 records.I have a button on this form which will open another form with related data from another table (b). What I want to do is open the...
8
by: skinnybloke | last post by:
Hi - I have a problem with a memo field being truncated to about 255 characters when running a Access 2002 query. This only seems to happen if I use SELECT DISTINCT. It works ok using SELECT by...
1
by: ET | last post by:
Please help with the query: There are two tables, A and B. A table has information about cell phones, like cell number, sim number, model, manufacturer etc... B table has user related...
4
by: jbrumbau | last post by:
Hello, I have been successfully using a database I've created for several months to populate an equipment list for a project we've been working on. However, the form has recently stopped working...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
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,...
0
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...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
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...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

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.