Oracle - Query where no result in a table

Hi

I'm trying to query all carriers where CARRIER_ID, LANE_ID and TL_RESOURCE_ID from table TL_RESOURCE_CARRIER will not show in table  TL_RESOURCE_TRLR.

In the below query, i pulls records where it does show a record in both tables.

In the attachment, you will see the 3 tables and what should be the final result for the query needed.

Thanks for your help.

SELECT DISTINCT TRC.CARRIER_ID,
  TRC.LANE_ID,
  L.NAME
FROM LANE L,
  TL_RESOURCE_CARRIER TRC,
  TL_RESOURCE_TRLR TRT
WHERE TRT.LANE_ID           = L.ID (+)
AND TRC.LANE_ID             = TRT.LANE_ID (+)
AND TRC.CARRIER_ID NOT     IN TRT.CARRIER_ID
AND TRC.TL_RESOURCE_ID NOT IN TRT.TL_RESOURCE_ID
AND TRC.LANE_ID             = L.ID
ORDER BY TRC.CARRIER_ID;

Open in new window

tl-resources-carrier-table.xlsx
LVL 11
Wilder1626Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
For those that come along later, this should create the sample data:
create table tl_resources_carrier (
tl_resource_id varchar2(10),
lane_id number,
carrier_id varchar2(10),
percent_commit number,
contract_max_teams number,
current_max_teams number,
period number,
max_loads number,
type varchar2(100));
insert into tl_resources_carrier values('ONE',	8087,	'PUROLATOR',	0,	99,	99,	0,	null,	'DISTR');
insert into tl_resources_carrier values('ONE',	29328,	'PUROLATOR',	0,	0,	0,	0,	null,	'DISTR');
insert into tl_resources_carrier values('ONE',	29329,	'PUROLATOR',	0,	0,	0,	0,	null,	'DISTR');
insert into tl_resources_carrier values('ONE',	23906,	'PUROLATOR',	0,	0,	0,	0,	null,	'DISTR');
insert into tl_resources_carrier values('ONE',	23915,	'PUROLATOR',	0,	0,	0,	0,	null,	'DISTR');
insert into tl_resources_carrier values('ONE',	1562,	'PUROLATOR',	0,	0,	0,	0,	null,	'DISTR');
insert into tl_resources_carrier values('ONE',	15007,	'PUROLATOR',	0,	0,	50,	0,	null,	'DISTR');
insert into tl_resources_carrier values('ONE',	41127,	'PUROLATOR',	0,	0,	99,	0,	null,	'DISTR');
insert into tl_resources_carrier values('ONE',	1740,	'PUROLATOR',	0,	0,	99,	0,	null,	'DISTR');
insert into tl_resources_carrier values('ONE',	8286,	'PUROLATOR',	0,	0,	0,	0,	null,	'DISTR');
insert into tl_resources_carrier values('ONE',	703,	'PUROLATOR',	0,	0,	0,	0,	null,	'DISTR');
insert into tl_resources_carrier values('ONE',	41128,	'PUROLATOR',	0,	0,	0,	0,	null,	'DISTR');
insert into tl_resources_carrier values('ONE',	41828,	'PUROLATOR',	0,	100,	100,	0,	null,	'DISTR');
insert into tl_resources_carrier values('ONE',	44187,	'PUROLATOR',	0,	0,	0,	0,	null,	'DISTR');
insert into tl_resources_carrier values('ONE',	47606,	'PUROLATOR',	0,	0,	0,	0,	null,	'DISTR');
insert into tl_resources_carrier values('ONE',	67306,	'PUROLATOR',	0,	0,	0,	1,	null,	'DISTR');
commit;
create table tl_resources_trlr (
tl_resource_id varchar2(10),
lane_id number,
carrier_id varchar2(10),
trlr_type_id varchar2(100),
effective date,
contract_num_vehicles number,
current_num_vehicles number);
insert into tl_resources_trlr values ('ONE',	703,	'PUROLATOR',	'TRAILER 1',	to_date('10-01-01','mm-dd-yy'),	0,	3);
insert into tl_resources_trlr values ('ONE',	44187,	'PUROLATOR',	'TRAILER 2',	to_date('10-01-01','mm-dd-yy'),	0,	5);
insert into tl_resources_trlr values ('ONE',	47606,	'PUROLATOR',	'TRAILER 3',	to_date('10-01-01','mm-dd-yy'),	0,	10);
insert into tl_resources_trlr values ('ONE',	47606,	'PUROLATOR',	'TRAILER 4',	to_date('10-01-01','mm-dd-yy'),	0,	10);
insert into tl_resources_trlr values ('ONE',	1740,	'PUROLATOR',	'TRAILER 5',	to_date('10-01-01','mm-dd-yy'),	0,	50);
insert into tl_resources_trlr values ('ONE',	1740,	'PUROLATOR',	'TRAILER 6',	to_date('10-01-01','mm-dd-yy'),	0,	50);
insert into tl_resources_trlr values ('ONE',	1562,	'PUROLATOR',	'TRAILER 7',	to_date('10-01-01','mm-dd-yy'),	0,	50);
insert into tl_resources_trlr values ('ONE',	1562,	'PUROLATOR',	'TRAILER 8',	to_date('10-01-01','mm-dd-yy'),	0,	50);
insert into tl_resources_trlr values ('ONE',	15007,	'PUROLATOR',	'TRAILER 9',	to_date('10-01-01','mm-dd-yy'),	0,	50);
insert into tl_resources_trlr values ('ONE',	15007,	'PUROLATOR',	'TRAILER 10',	to_date('10-01-01','mm-dd-yy'),	0,	50);
insert into tl_resources_trlr values ('ONE',	15007,	'PUROLATOR',	'TRAILER 11',	to_date('10-01-01','mm-dd-yy'),	0,	50);
insert into tl_resources_trlr values ('ONE',	41127,	'PUROLATOR',	'TRAILER 12',	to_date('10-01-01','mm-dd-yy'),	0,	50);
insert into tl_resources_trlr values ('ONE',	8087,	'PUROLATOR',	'TRAILER 13',	to_date('10-01-01','mm-dd-yy'),	0,	50);
insert into tl_resources_trlr values ('ONE',	8087,	'PUROLATOR',	'TRAILER 14',	to_date('10-01-01','mm-dd-yy'),	0,	50);
insert into tl_resources_trlr values ('ONE',	8286,	'PUROLATOR',	'TRAILER 15',	to_date('10-01-01','mm-dd-yy'),	0,	50);
insert into tl_resources_trlr values ('ONE',	8286,	'PUROLATOR',	'TRAILER 16',	to_date('10-01-01','mm-dd-yy'),	0,	50);
insert into tl_resources_trlr values ('ONE',	29329,	'PUROLATOR',	'TRAILER 17',	to_date('10-01-01','mm-dd-yy'),	0,	2);
insert into tl_resources_trlr values ('ONE',	23906,	'PUROLATOR',	'TRAILER 18',	to_date('10-01-01','mm-dd-yy'),	0,	50);
insert into tl_resources_trlr values ('ONE',	23915,	'PUROLATOR',	'TRAILER 19',	to_date('10-01-01','mm-dd-yy'),	0,	50);
insert into tl_resources_trlr values ('ONE',	29329,	'PUROLATOR',	'TRAILER 20',	to_date('10-01-01','mm-dd-yy'),	0,	5);
insert into tl_resources_trlr values ('ONE',	41128,	'PUROLATOR',	'TRAILER 21',	to_date('10-01-01','mm-dd-yy'),	0,	50);
insert into tl_resources_trlr values ('ONE',	8087,	'PUROLATOR',	'TRAILER 22',	to_date('10-01-01','mm-dd-yy'),	0,	2);
insert into tl_resources_trlr values ('ONE',	67306,	'PUROLATOR',	'TRAILER 23',	to_date('10-01-01','mm-dd-yy'),	0,	3);
commit;
create table lane(
id number,
name varchar2(100),
from_region_id number,
to_region_id number);
insert into lane values (703,	'V5X2S5-V5X',	133,	269);
insert into lane values (1562,	'R3E_2T4-CANADA',	452,	451);
insert into lane values (1740,	'S7J_4M6-CANADA',	455,	432);
insert into lane values (8087,	'T5V1B2-CANADA',	514,	513);
insert into lane values (8286,	'T3J_4X9-CANADA',	572,	571);
insert into lane values (15007,	'V3S_3W6-CANADA',	1632,	1631);
insert into lane values (23906,	'B3T_1L9-CANADA',	5092,	5091);
insert into lane values (23915,	'B3T_1A1-CANADA',	5110,	5109);
insert into lane values (29328,	'L1Z_0B2-CANADA',	929,	5671);
insert into lane values (29329,	'N3H_4R7-CANADA',	865,	5672);
insert into lane values (41127,	'T2C_1A5-CANADA',	616,	10451);
insert into lane values (41128,	'T3J_5H6-CANADA',	8480,	10452);
insert into lane values (41828,	'L7C_2X3-03',	10640,	10639);
insert into lane values (44187,	'V5X_2S5-GFS',	4819,	11371);
insert into lane values (47606,	'M1X_1B8-ON',	12631,	671);
insert into lane values (67306,	'L1Z 0B2-60RXD',	929,	16331);
commit;

Open in new window

I believe that this query gives you what you are looking for:
SELECT TRC.carrier_id, 
       TRC.lane_id, 
       L.name 
FROM   (lane L 
        join tl_resources_carrier TRC 
          ON trc.lane_id = l.id) 
       left outer join tl_resources_trlr TRT 
                    ON trc.carrier_id = trt.carrier_id 
                       AND TRC.tl_resource_id = TRT.tl_resource_id 
                       AND l.id = trt.lane_id 
WHERE  trt.tl_resource_id IS NULL;

Open in new window

PortletPaulEE Topic AdvisorCommented:
no points please, this is just a small adjustment to the above solution.

CARRIER_ID, LANE_ID and TL_RESOURCE_ID of table TL_RESOURCE_CARRIER  
not present in table  TL_RESOURCE_TRLR 

SELECT
    TRC.carrier_id
  , TRC.lane_id
  , TRC.tl_resource_id
FROM tl_resources_carrier TRC
LEFT OUTER JOIN tl_resources_trlr TRT
        ON TRC.carrier_id = TRT.carrier_id
        AND TRC.tl_resource_id = TRT.tl_resource_id
        AND TRC.lane_id = TRT.lane_id
WHERE trt.tl_resource_id IS NULL
;

Open in new window


| CARRIER_ID | LANE_ID | TL_RESOURCE_ID |
|------------|---------|----------------|
|  PUROLATOR |   41828 |            ONE |
|  PUROLATOR |   29328 |            ONE |

Open in new window

Wilder1626Author Commented:
Thanks for the help.

I will validate both as i get 2 different results:
johnsone = 7473 records
PortletPaul = 7505 records

I need to see why i don't have the same result.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Get Your eBook
PortletPaulEE Topic AdvisorCommented:
You have some rows tl_resources_carrier that have no matching Lane table rows.

johnsone used:

FROM   lane L [INNER] join tl_resources_carrier TRC ON trc.lane_id = l.id

That the inner join requires that TRC rows must match to Lane;
whereas I did not reference the table Lane, so that table cannot restrict the results
Wilder1626Author Commented:
Ok.

In my case, i need to get both, the carrier and the lane name match with no trailers. This will guide me on the records i will need to delete from the database.
johnsoneSenior Oracle DBACommented:
I did not use an outer join on LANE on purpose.  I assumed there was a constraint there to not allow missing records and that the NAME was needed as it provides the uniqueness.  If you want the extra records, then you can change the first join in my query to an RIGHT OUTER JOIN.  However, that would give you blank values in the NAME column.

Based on author's last response, I think my original would be what is needed.  If the name is required, the outer join to LANE is going to give records that cannot be used.
Wilder1626Author Commented:
Still doing some test but i have tweak PortletPaul's query to also give me the lane name. and i still arrive at 7505

7505 records:
SELECT
    TRC.CARRIER_ID
  , TRC.LANE_ID
  , L.NAME
FROM LANE L , TL_RESOURCE_CARRIER TRC 
LEFT OUTER JOIN TL_RESOURCE_TRLR TRT
        ON TRC.CARRIER_ID = TRT.CARRIER_ID
        AND TRC.TL_RESOURCE_ID = TRT.TL_RESOURCE_ID
        AND TRC.LANE_ID = TRT.LANE_ID
        
WHERE TRT.TL_RESOURCE_ID IS NULL
AND L.ID = TRC.LANE_ID 
ORDER BY TRC.CARRIER_ID;

Open in new window



7473 records:
 SELECT DISTINCT TRC.CARRIER_ID, 
       TRC.LANE_ID, 
       L.NAME 
FROM   (LANE L 
        JOIN TL_RESOURCE_CARRIER TRC 
          ON TRC.LANE_ID = L.ID) 
       LEFT OUTER JOIN TL_RESOURCE_TRLR TRT 
                    ON TRC.CARRIER_ID = TRT.CARRIER_ID 
                       AND TRC.TL_RESOURCE_ID = TRT.TL_RESOURCE_ID 
                       AND L.ID = TRT.LANE_ID 
WHERE  TRT.TL_RESOURCE_ID IS NULL
ORDER BY TRC.CARRIER_ID;

Open in new window

johnsoneSenior Oracle DBACommented:
If you want the 7505 from my query, as I said, change it to:
 SELECT DISTINCT TRC.CARRIER_ID, 
       TRC.LANE_ID, 
       L.NAME 
FROM   (LANE L 
        RIGHT OUTER JOIN TL_RESOURCE_CARRIER TRC 
          ON TRC.LANE_ID = L.ID) 
       LEFT OUTER JOIN TL_RESOURCE_TRLR TRT 
                    ON TRC.CARRIER_ID = TRT.CARRIER_ID 
                       AND TRC.TL_RESOURCE_ID = TRT.TL_RESOURCE_ID 
                       AND L.ID = TRT.LANE_ID 
WHERE  TRT.TL_RESOURCE_ID IS NULL
ORDER BY TRC.CARRIER_ID;

Open in new window

Just be aware that you will have records where NAME is null.  There is no way you can get the name when the id doesn't exist in the LANE table.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Wilder1626Author Commented:
just found out why i was getting 2 different results.

Just by adding DISTINCT in the query i was pulling 7505, i now get 7473 records and both have the same records.

johnsone, i have also tried your last query and i also get the same results.

Now i'm just finishing the manual validation. See if it really match 100%.

I will be back shortly.
Wilder1626Author Commented:
Hi Sorry for the delay.

This is all good. Thanks a lot for your help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.