CODEWITHSUNDEEP
sqlset
Jeffom
Jeffom

Reputation: 542

How to query sets and subsets and get a set that doesn't contain the other set

I think i have a somewhat simple problem but due to my lack of experience in DB modelling and SQL i'm unable to get the right solution, i can draw the basic concept but i'm having a hard time to implement a solution

Let's say i have some sets A,B and C.

A is inside B

B is inside C

A is the set of the 10 objects with greatest value inside B set

B is the set of all values greater than 100

C is the set of all values greater than 50

enter image description here

that's the easy part,

Now i need to get 50 objects with the greatst value from C that are not inside A How would i translate that problem to the SQL language, or what commands, tools or design patterns should i use to solve this problem?

I'm not sure if I was clear enough, again sorry if the problem is too trivial for some, but i'm trying to learn more about SQL and designs.

I'm looking for a general solution that it's not bound by a framework or OS

Thanks for any help

Upvotes: 1

Views: 1552

Answers (3)

Parvez
Parvez

Reputation: 111

Hi I am not sure if I understand your problem correctly. if I consider A, B and C your SQL tables and assume that they have some sort of relation between them then you can write your query something like

SELECT C.* FROM C WHERE C.ID NOT IN

(SELECT B.ID FROM B JOIN A ON B.ID = A.ID)

The subquery (Second line) gives you all the objects which are common between B and A (so essentially A).

Again I am not sure if this is what you are looking for, it would be helpful if you can share your real life scenario here.

For set theory and SQL understanding please refer to below article

http://seanmehan.globat.com/blog/2011/12/20/set-theory-and-sql-concepts/

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269933

Let me assume that you have a table t with a value column, and that the labels "A", "B", and "C" are based on these.

select t.*
from t left outer join
     (select value
      from t
      where value >= 100
      order by value desc
      limit 1
     ) A
     on t.value = A.value
where t.value >= 50 and A.value is null
order by value desc
limit 50;

The use of limit is database specific. It might be top or rownum in the where clause or something else depending on the database.

EDIT:

If the sets are large enough, you could just do:

select t.*
from t
where value >= 50 and value < 100
order by value desc
limit 50;

Upvotes: 2

Sonam
Sonam

Reputation: 3466

May be this query would help:

select Col1,Max(Col2) from
(select col1, col2 from TableC
Except
Select col1, col2 from TableA)as A
Group By Col1

Here the inner query would only select the records which are there in table A but not in Table C, and then outer query would take the max of those records.

Upvotes: 0

Related Questions