473,320 Members | 1,993 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,320 software developers and data experts.

Queries/joins with more than 2 tables

Trying to..: find all IDNumbers from one table that are not in EITHER
of TWO other tables. Doing one is no sweat, via a left join. Tried
adding a second left join, but doesn't get the right results. Is there
a statement in SQL I can throw in to get the job done, or am I looking
at 2 separate queries?

To clarify, here's an example with simplified data, where yes or no
indicates the presence or absence of the given Idnumber:
IDNUMBER(from TableA) TABLE B TABLE C
1 yes no
2 no yes
3 no no

The only record I'd want is #3.

Thanks
Martin Lacoste

Nov 13 '05 #1
2 1247
Martin,

You may use the following sample to find out differences between more
than one table. Key point is using IN word in SQL command.

Regards.

Sample:
Select TA.ID From TA
Where TA.ID Not In (Select ID From TB) And TA.ID Not In (Select ID From
TC)

Nov 13 '05 #2
Hi Martin,

I didn't test, but would have thought
the following would have worked...
(of course I could be wrong...)

SELECT
A.AID
FROM
(A LEFT JOIN B ON A.AID = B.BID)
LEFT JOIN C ON A.AID = C.CID
WHERE
B.BID IS NULL
AND
C.CID IS NULL;

<ma*******@rogers.com> wrote:
Trying to..: find all IDNumbers from one table that are not in EITHER
of TWO other tables. Doing one is no sweat, via a left join. Tried
adding a second left join, but doesn't get the right results. Is there
a statement in SQL I can throw in to get the job done, or am I looking
at 2 separate queries?

To clarify, here's an example with simplified data, where yes or no
indicates the presence or absence of the given Idnumber:
IDNUMBER(from TableA) TABLE B TABLE C
1 yes no
2 no yes
3 no no

The only record I'd want is #3.

Thanks
Martin Lacoste

Nov 13 '05 #3

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

Similar topics

6
by: Andreas Lauffer | last post by:
I changed from Access97 to AccessXP and I have immense performance problems. Details: - Access XP MDB with Jet 4.0 ( no ADP-Project ) - Linked Tables to SQL-Server 2000 over ODBC I used...
3
by: csomberg | last post by:
SQL Server 2000 Howdy All. Is it going to be faster to join several tables together and then select what I need from the set or is it more efficient to select only those columns I need in...
3
by: Kenneth | last post by:
I have 3 different tables that I want Queries to sum up, but I do not get all the information into my Queriy-file. The 1st table have 9 different products. The 2nd table have 3 of those 9...
4
by: John Smith | last post by:
Isn't life a bitch! You know what you want but you don't know how to get it. I have produced 12 queries that calculate a payment profile over 12 months. For a number of the records (ie with...
1
by: Alex Satrapa | last post by:
Is there any (simple? har!) way to optimise a particular SQL query? At this stage, I'm more interested in making the query more readable, so I've started hunting down references about relational...
6
by: SR | last post by:
As a starter project for learning Python/PostgreSQL, I am building a Books database that stores information on the books on my bookshelf. Say I have three tables. Table "books" contains rows...
7
by: lawrence k | last post by:
Okay, I just backed up my database, just in case. The whole schema for the database is here: http://www.accumulist.com/index.php?whatPage=db.php You can run any SELECT query against this...
0
by: Christoph Haas | last post by:
Hi, list... I have written an application in Perl some time ago (I was young and needed the money) that parses multiple large text files containing nested data structures and allows the user to...
2
ADezii
by: ADezii | last post by:
Create Indexes on all Columns used in 'ad hoc' Query Joins, restrictions, and sorts (Jet already creates Indexes for Enforced Relationships). Use Primary Keys instead of Unique Indexes wherever...
2
by: jaffar.kazi | last post by:
Hi All. This might be the wrong group to post this query, since it is ADO.NET related, but I couldn't find any ADO.NET group. My problem is that I want to write some queries, which use temp...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

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.