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.
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;
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;
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;
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
;
| CARRIER_ID | LANE_ID | TL_RESOURCE_ID |
|------------|---------|----------------|
| PUROLATOR | 41828 | ONE |
| PUROLATOR | 29328 | ONE |
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.
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.
You have some rows tl_resources_carrier that have no matching Lane table rows.
johnsone used:
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
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
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.
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.
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.
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.
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:
7473 records:
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;
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;
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;
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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial