• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 446
  • Last Modified:

SQL Query Question

I have been asked by a friend who is using Borland Interbase 6 how to perform the following query, but I don'know much about the features in Interbase so perhaps someone can help.

The problem is...

>> >>>>say you have 2 tables as follows
>> >>>>
>> >>>>TABLE1
>> >>>>ID = 10
>> >>>>ID = 20
>> >>>>
>> >>>>TABLE2
>> >>>>ID = 10   QTY = 10
>> >>>>ID = 10   QTY = 20
>> >>>>ID = 10   QTY = 30
>> >>>>
>> >>>>ID = 20   QTY = 20
>> >>>>ID = 20   QTY = 30
>> >>>>
>> >>>>Foreign key being ID
>> >>>>
>> >>>>and i want to get all IDS that have QTY'S 10,20 and 30 what is
>> >>>>the SQL?
>> >>>>
>> >>>>In the above example I only want the result set to contain ID = 10,
>> >>>>because ID=20 does not have QTY = 10.

So the basic problem is we only want ID's that have a matching record in table 2 for all the existing quantities in table 2.

One way we have looked at is to do this...

SELECT
    t.id
from
    table1 as t
where
    t.id in
        (SELECT a.id from table2 as a where a.qty = 10)
and
    t.id in
        (SELECT b.id from table2 as b where b.qty = 20)
and
    t.id in
        (SELECT c.id from table2 as c where c.qty = 30);

This will get you all id's that have 10, 20 or 30 but you will either need
to generalise it if you want to look for more than 10, 20 and 30 or add new
sub queries in.

Not a good solution.

Ideally what we want to do is something like this:

select
    t.id
from
    table1 as t
where t.id not in
    (
        select
            s.id
        from
            table2 as s
        where
            t.id = s.id and s.qty in
            (
                (select distinct a.qty from table2 as a)
                minus
                (select distinct b.qty from table2 as b where t.id = b.id)
            )
    )

This will get the set of all quantities, see if this id has a match for each one. If it doesn't, then it becomes part of the set we don't want - if it does then it becomes part of the set we do want and we use not in to choose in the outer query.

However, Interbase does not support the SQL minus command so we need to find a different way of doing this.

If you have any idea how or any better way to perform the query let me know and the points are yours...
0
Jod
Asked:
Jod
1 Solution
 
gindeCommented:
This is not a ideal solution you are looking for, but it may be shorter one than what you have right now, assuming self joins works in Interbase.

select distinct a.id
from Table1 a, Table2 bb, Table2 c, Table2 d
where a.id = b.id
and a.id = c.id
and a.id = d.id
and b.qty = 10
and c.qty = 20
and d.qty = 30



0
 
paaskyCommented:
Hello Jod,

I have Delphi 4 with some version of Interbase but unfortunately I haven't installed it so I can't be sure if this works.. I tested it with Oracle.

SELECT T2.ID ID, COUNT(T2.QTY) DUMMY
FROM TABLE2 T2
GROUP BY T2.ID
HAVING COUNT(*)=(SELECT COUNT(DISTINCT QTY) FROM TABLE2);

       ID     DUMMY
--------- ---------
       10         3

Regards,
Paasky
0
 
sgantaCommented:
Hi,

You can do this in 2 ways.

If your RDBMS allows "EXISTS" clause, then try this query
or else try the next query.

select   t.id
from      table1 as t
where  not exists ( select distinct s.qty
                                   from   table2 as s
                                   where s.qty not in
                                                (select  q.qty
                                                  from    table2 as q
                                                  where q.id = t.id
                                                 )
                                  )

or

select   t.id
from      table1 as t
where  t.id not in  ( select distinct t.id
                                   from   table2 as s
                                   where s.qty not in
                                                (select  q.qty
                                                  from    table2 as q
                                                  where q.id = t.id
                                                 )
                                  )


Hope this helps you !
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
JodAuthor Commented:
Thanks for your answers - sganta, because the answer from paasky was first if it is used then I will award the points there. It looks OK but I have to see if this solution worked for my friend who passed on the query.

I'll let you know...
0
 
JodAuthor Commented:
SOrry sganta - the previous answer was used...
0
 
JodAuthor Commented:
Cheers Paasky - this has worked out well for my friends circumstances.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now