How can I Select records, using a join, that meets two criteria for the same column?

user_15154570 0
Viewed 33

I have a database with two (actually more) tables: Accounts & Assets. The relationship is many Assets to one Account.

Example of existing data:

Account     Asset
Abbot       EZ111
Abbot       7000261
Anderson    EZ431
Barton      EZ207
Barton      8000400
Charlie     8000465
Charlie     EZ576
Dickens     EZ420
Elmer       EZ982

I would like to find all Accounts that DO have an Asset that starts with 'EZ', but DO NOT also have an Asset that starts with a '7' or an '8'.

So for the data above, I'd be expecting the result of:

Account     Asset
Anderson    EZ431
Dickens     EZ420
Elmer       EZ982

Disclaimer: Obviously I don't do SQL for a living, but I'm learning a lot from the W3 site. What I can't figure out is the basic structure of this query. I know how to do the Join, but I don't know how to query the same Column twice.

Sorry if I'm a bit of a newbie for this site, but I mainly use create Groups within InforCRM. A query like this seems to be outside the capabilities of the Group's "Search Criteria" function.

Best Regards,

TimL

sql
3 Answers

The way you phrased it, I would suggests not exists:

select t.*
from t
where asset like 'EZ%' and
      not exists (select 1
                  from t t2
                  where t2.account = t.account and
                        (t2.asset like '7%' or t2.asset like '8%')
                 );
user_11440350

select * from tablename where asset like 'EZ%' and account not in (select account from tablename where asset like '7%' or asset like '8%')

user_86516010

Others beat me to it but here is a complete working example based on what I think your data could look like

https://www.db-fiddle.com/f/8R6CZ36vjZgCUCdqU3DRi/1

SELECT ACT.ACCOUNT, AST.ASSET
FROM ACCOUNT ACT
INNER JOIN ASSET AST ON ACT.ACCOUNT = AST.ACCOUNT
WHERE AST.ASSET LIKE 'EZ%' 
    AND ACT.ACCOUNT NOT IN (
      SELECT ACCOUNT FROM ASSET WHERE ASSET LIKE '7%' OR ASSET LIKE '8%'
    );
user_86789780
Related