Re: SQL query!

From: Martin Gäckler <martin_at_gaeckler.de>
Date: Wed, 17 Jan 2007 23:55:48 +0100
Message-ID: <eom9hk$46i$1@online.de>

qazmlp1209_at_rediffmail.com schrieb:
> (Please ignore my previous thread.)
> I have considered all the requirements below. Please help in
> formulating an SQL for it. Thanks!
>
> I have a Table with the columns: Column-a, Column-b,
> Column-c,Column-d.....Column-z.
> But, for the below problem, we can ignore the values in the column-d,
> .....,Column-z.
>
> I would like to form an SQL selecting the distinct values of the
> Column-a where
> - Column-c is NOT NULL
> - There is more than 1 distinct association between Column-a, Column-b
> values, and
> Column-b values have 1:1 association with the Column-c values in
> that rows.
>
> E.g.
> Column-a Column-b Column-c
> -------------- --------- --------
> 1 10 S1
> 1 10 S2
> 2 20 S3
> 2 30 S4
> 3 40 S5
> 4 50 (NULL)
>
> Here,
> - '1' should not be selected, as there is only one association exists
> with Column-b: 1,10
> - '2' should be selected, as there are multiple associations exists
> i.e. 2,20 & 2,30 and 20, 30 have distinct values in Column-c.
> - '3' should not be selected, as there is only association exists with
> Column-b
> - '4' should not be selected, as there is a NULL in column 'c'
>
> How do we achieve it in the SQL?
>

Hello,

your test data are incomplete, here is my solution:

create table table_x
(

	column_a	integer,
	column_b	integer,
	column_c	varchar(2)

);

insert into table_x values ( 1, 10, 'S1' );
insert into table_x values ( 1, 10, 'S2' );
insert into table_x values ( 2, 20, 'S3' );
insert into table_x values ( 2, 30, 'S4' );
insert into table_x values ( 3, 40, 'S5' );
insert into table_x values ( 3, 40, 'S6' );
insert into table_x values ( 3, 50, 'S6' );
insert into table_x values ( 4, 40, 'S6' );
insert into table_x values ( 4, 40, 'S6' );
insert into table_x values ( 4, 50, 'S7' );
insert into table_x values ( 5, 50, null );

select distinct t1.column_a
from table_X t1
where t1.column_c is not null
and
(

	select count( distinct t2.column_b )
	from table_x t2
	where t1.column_a = t2.column_a

) <> 1
and t1.column_a not in
(

	select t3.column_a
	from table_x t3
	group by t3.column_a, t3.column_b
	having count(distinct column_c) > 1

)
;

1 will not be selected, because there is only one distinct value of column_b (10)

3 will not be selected, because the value pair (3.40) has two distinct values of column_c (S5 and S6)

5 will not be selected because the only record available has a null value in column-c

Another question which is not yet answered: What should happen, if there are othher records with a value of 5 in column_a? Perhaps this is a better solution:

select distinct t1.column_a
from table_X t1
where not exists
(

	select *
	from table_x t4
	where t4.column_a = t1.column_a
	and t4.column_c is null

)
and
(

	select count( distinct t2.column_b )
	from table_x t2
	where t1.column_a = t2.column_a

) <> 1
and t1.column_a not in
(

	select t3.column_a
	from table_x t3
	group by t3.column_a, t3.column_b
	having count(distinct column_c) > 1

)
;

This variant will not fetch the value 5 because there is at least one record with the unwanted null value.

-- 
Firma/Company:                                              CRESD GmbH
Phone: +49-89-65 30 95 63                      Fax: +49-89-65 30 95 64
WWW:                                               http://www.cresd.de
S-Mail:                                Freibadstr. 14, D-81543 München
PGP-Key:                            http://www.cresd.de/edv/pgpkey.txt
Open BC (Einladung)            http://www.openbc.com/go/invita/4561755

Received on Wed Jan 17 2007 - 16:55:48 CST