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...
LVL 5
JodAsked:
Who is Participating?
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.

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

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
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 Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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
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
Databases

From novice to tech pro — start learning today.