Databases
StackOverflow2013
Postsdbo
POUsing a Type I Nested Query for a Difference Problem
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUsing a Type I Nested Query for a Difference Problem
    text
    copied!<p>I'm working on my homework and am having trouble figuring out the correct syntax for the "NOT IN" functionality.</p> <p>The database is a small insurance database (carriers, members, employers, plans) made up for this assignment. I am supposed to figure out "Which Plans do not have any Member subscriptions? List the PlanID, the plnDescription and plnCost. (Hint: use the nested Select statement with a NOT IN clause.)"</p> <p>This is what I have written as of right now:</p> <pre><code>SELECT planid, plndescription, plncost FROM plans WHERE planid NOT IN (SELECT memberno FROM members); </code></pre> <p>And when I run that query, I get a 0 rows selected response.</p> <p>When I take out the WHERE statement, I get the following response:</p> <pre><code>PLANID PLNDESCRIPTION PLNCOST ---------------------- ----------------------------------- ---------------- 1 Single Basic Medical 72.5 2 2-Party Basic Medical 140 3 Family Basic Medical 225 4 Single SuperMed 110 5 2-Party SuperMed 200 6 Family SuperMed 350 7 Single Dental Only 35 8 Family Dental Only 70 9 Life Only 16.5 10 Single SuperMed with Dental 125 11 2-Party SuperMed with Dental 240 12 Family SuperMed with Dental 425 12 rows selected </code></pre> <p>I'm sure my error is in the WHERE statement because I'm sure I'm using the wrong field names in the WHERE statement as well as the second SELECT statement. Any help is very much appreciated.</p> <p>Members table structure:</p> <pre><code>Name Null Type ------------------------------ -------- ------------- MEMBERNO NOT NULL NUMBER MBRFIRSTNAME CHAR(35) MBRLASTNAME CHAR(35) MBRSTREET CHAR(50) MBRCITY CHAR(30) MBRSTATE CHAR(2) MBRZIP CHAR(10) MBRPHONENO CHAR(10) PLANID NUMBER MBREMAIL CHAR(255) MBRDATEEFFECTIVE DATE EMPLOYERID NUMBER 12 rows selected </code></pre> <p>Plans Table Structure:</p> <pre><code>Name Null Type ------------------- -------- ------------- PLANID NOT NULL NUMBER PLNDESCRIPTION CHAR(35) PLNCOST NUMBER(8,2) CARRIERID CHAR(4) 4 rows selected </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload