Select a random value from a table1 that doesn't exist in table2

I am having a complete lapse on this one and could use some sage advice.

I have two tables.

One is a list of contract codes covering work agreements.  The other is list of combinations of contract codes and work types assigned to each contract.

Dropping the rest of the content of the tables let's just call them:

contract_work_type:
contract_no char(4)
work_type char(2)

tab_work_types:

For some automated testing that's being developed we've been selecting random pieces of information from tables to feed into the testing tool along the lines of:
select col1
from
(
 select col1 from table1 order by dbms_random.random()
)
where rownum = 1;

Open in new window

Now however I need to do the following:

First select a random work type.  Then, select a random contract number out of the complete list of contracts where that work type is not assigned to that contract.  Then I need to return both the contract and the work type.

The code below is returning me a valid contract_no where the randomly selected work_type is not on the contract - but I can't work out how to make that randomly selected work type from the innermost select to float all the way to the top of the result set.

(Comments in the code should be read from the bottom up if you want to follow my convoluted thought process on this)
select contract_no   -- select the first one from that list
from
(
 select contract_no        -- give me a randomly sorted list of contracts that don't have the work type
 from contract_work_type 
 where not contract_no in
 (
  select distinct contract_no    -- give me a list of the contracts that have the work type
  from contract_work_type
  where work_type in
  (
   select work_type    -- this selects the first one of that list
   from 
   (
    select work_type   -- this gives a randomly ordered list of work types
    from tab_work_types 
    order by dbms_random.random()
   ) 
   where rownum = 1
  )
 )
 order by dbms_random.random()
 )
where rownum = 1

Open in new window

Anyone able to shed some light on this for me?
LVL 23
Steve WalesSenior Database AdministratorAsked:
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.

lcohanDatabase AnalystCommented:
Try use simple MINUS built in function (which is like EXCEPT in MSSQL) or INTERSECT to get the opposite like in example below:

SELECT a, b, c
FROM   table_a
MINUS
SELECT a, b, c
FROM   table_b
Steve WalesSenior Database AdministratorAuthor Commented:
That won't work here.  Work type only exists in one of the two tables.
sdstuberCommented:
why not simplify to something like this...

select a random work_type
find contracts that do NOT use that work_type
pick one of them randomly.

There is no need to do an additional subselect and distinct.  
There is no functionality added by doing so


SELECT contract_no, work_type
  FROM (  SELECT contract_no, work_type
            FROM contract_work_type
           WHERE work_type NOT IN (SELECT work_type
                                     FROM (  SELECT work_type
                                               FROM tab_work_types
                                           ORDER BY DBMS_RANDOM.random())
                                    WHERE ROWNUM = 1)
        ORDER BY DBMS_RANDOM.random())
 WHERE ROWNUM = 1

Open in new window

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Steve WalesSenior Database AdministratorAuthor Commented:
One part missing, Sean.  I need to have that work type filter up into the final result set.

That was the real crux of the question :)
sdstuberCommented:
sorry, I misunderstood the question but the answer it pretty easy
you only need to randomize once, the join will force that randomization to apply to both table results



SELECT work_type, contract_no
  FROM (  SELECT twt.work_type, cwt.contract_no
            FROM tab_work_types twt, contract_work_type cwt
           WHERE cwt.work_type != twt.work_type
        ORDER BY DBMS_RANDOM.random())
 WHERE ROWNUM = 1

Open in new window



you can verify the "randomness" by including the unmatched column in a test query and removing the rownum=1 criteria


SELECT work_type, contract_no, unmatched_work_type
  FROM (  SELECT twt.work_type, cwt.contract_no, cwt.work_type unmatched_work_type
            FROM tab_work_types twt, contract_work_type cwt
           WHERE cwt.work_type != twt.work_type
        ORDER BY DBMS_RANDOM.random())
--WHERE ROWNUM = 1

Open in new window

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
Steve WalesSenior Database AdministratorAuthor Commented:
Thanks a lot, that seems to be exactly what I needed.
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.