The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

SQL Puzzle

I've got a table as follows

A      B
46    38
46    77
68    38
68    77
103    38

I want to only return values of 'B' where there is an entry for 'B' for each different value of 'A'

In the above table, 'B'=38 is represented for each different value of A (46, 68, 103). However B=77 fails and should not be included because there is no entry for A=103, B=77.

How can I do this in SQL. It's driving me nuts.

Btw: Not homework or anything
Tuesday, March 14, 2006
I think this should do the trick, if I understand your question correctly:

SELECT table.b
FROM table
WHERE table.a IN (
  SELECT table.a
  FROM table
  table.b = 38
BenjiSmith Send private email
Tuesday, March 14, 2006
There may be more efficient ways, depending on your database engine, but

SQL> select * from boo;

        A          B                                                         
---------- ----------                                                         
        46        38                                                         
        46        77                                                         
        68        38                                                         
        68        77                                                         
      103        38                                                         

SQL> get /temp/puzzle
  2  FROM boo t1
  4    (SELECT 1
  5    FROM boo t2
  7      (SELECT 1 FROM boo t3
  8        WHERE t3.a = t2.a
  9*        AND t3.b = t1.b))
SQL> /


SQL> spool off
George Jansen Send private email
Tuesday, March 14, 2006
That was quick. Thanks heaps.
I'm not fussed about the efficiency.

It took me forever to come up with this alternative a moment ago which also seems to work:

Select B
From Table1
Group By B
Having Count(B) =
    Select Count(A) From
    (Select Distinct A From table1) T2

Sorry, didn't mention A-B is a primary key.
Tuesday, March 14, 2006
Yup. I misunderstood the question. LOL
BenjiSmith Send private email
Tuesday, March 14, 2006
That's wierd, a moment ago the order of posts in the thread was..
George Jansen

and now its...
George Jansen

Can't see how BenjiSmith's post slipped in a number 2 when I had a page loaded that started Kim, George Jenson.

Thanks for your help anyhow.
Tuesday, March 14, 2006
Actually, I replied first. It was several minutes later before I saw any other replies.

Evidently, the bayesian spam filter flagged my post and hid it from view, either until it was manually verified by a moderator or until I posted again, confirming that I'm a real person and not just a spambot.

I've been posting to these forums for four years now, and the spam filter still routinely hides my posts. It's annoying.
BenjiSmith Send private email
Tuesday, March 14, 2006
An analytic function solution:

create table my_table (a number,b number);
insert into my_table values ( 46,38);
insert into my_table values ( 46,77);
insert into my_table values ( 68,38);
insert into my_table values ( 68,77);
insert into my_table values (103,38);


select distinct b
select b,
      count(distinct a) over (partition by b) a_per_b,
      count(distinct a) over () total_a
from  my_table
where a_per_b = total_a

Very elegant, though I say so myself :D

What's that? Your DBMS doesn't support analytic functions?
David Aldridge Send private email
Wednesday, March 15, 2006
>> I'm not fussed about the efficiency <<

Erm ... ?
David Aldridge Send private email
Wednesday, March 15, 2006
Mmmm... I saw the same kind of request in the "Rent a Coder" web site about three months ago. The requester was asking to have some open source DB engine to be modified to have a special query syntax to deal with this problem.

I bid for the request, but not for modifying the DB engine but saying that I could provide the intended results using a standard SQL query. The requester seemed not to believe that this was possible and finally the request was cancelled.

I'm now wondering if the requester is trying to solve the problem in a cheaper way by using the forum. Probably he/she should have asked for help in a good forum (like this one) before commiting to no less than modifying the query syntax of an open source SQL engine.
How ironic Send private email
Tuesday, March 28, 2006

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz