CODEWITHSUNDEEP
sqloracle-database
david mah
david mah

Reputation: 315

Find tuples that appear less than 3 times

I need to write an sql query in oracle to find tuples that only appear less than 3 times in the table. Now if I'm only working with one table then the solution is easy:

Select class
From Ships
GROUP BY class
Having COUNT(class) < 3;

However it's a bit more complicated because of the tables I'm working with. The two tables are Classes and Ships. The classes table lists out what class a certain ship belongs to and the ships table lists out the name of the ship as well as the class. Neither table has any foreign keys which means we might get a certain class in the classes table that's not in the ships table. Here's what the tables look like:

Create Table Classes (
    class Varchar(40),
    type Char (2),
    country Varchar(15)
);
Create Table Ships (
    name Varchar(40),
    class Varchar(40)
);

And we might get tables that look like this:

Classes:
('Bismarck','bb','Germany');
('Kongo','bc','Japan');
('Renown','bc','Gt. Britain');

Ships:
('Hiei','Kongo');
('Haruna','Kongo');
('Renown','Renown');
('Repulse','Renown');
('Kongo','Kongo');
('Kirishima','Kongo');

So Renown would get listed out since it appears in the ships table only twice however I also want the Bismarck class to get listed out as well since it's only listed out once in the classes table and therefore is listed out less than 3 times. What I don't understand is that the classes in the Classes table will always only get listed once however if the class appears more than 3 times in the ships table then it doesn't matter how many times it's listed in the classes table. I think I need to do an outer join to make this work but I'm not sure what that would look like.

So the results I expect are

Renown
Bismarck

Upvotes: 2

Views: 225

Answers (2)

Zero
Zero

Reputation: 1646

If I understood you goal correctly, all you need to do is replace your FROM Ships to a FROM Ships s [join type] Classes c on s.class = c.class. I'm not really 100% sure what join to use, because I wasn't sure what kind of result set you were looking for.

--Selects only matches, so "Bismarck" wouldn't be shown
Select class 
From Ships s inner join Classes c on s.class = c.class 
GROUP BY class
Having COUNT(s.class) < 3;

If your requirements are not reflected with left/right/inner join types you can always go for full outer join' and specify conditions inWhere` clause.

Select class 
From Ships s full outer join Classes c on s.class = c.class 
WHERE s.class IS NOT NULL AND [other conditions]
GROUP BY class
Having COUNT(s.class) < 3;

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133390

You could use a left join and count(*)

select c.class, count(*)
From Class as c 
left join   Ships as s on c.class = s.class
GROUP BY c.class
Having COUNT(*) < 3;

Upvotes: 0

Related Questions