Selecting records with mentioned conditions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Selecting records with mentioned conditions

Author

Topic

m.ibbrahim
Starting Member

16 Posts

Posted - 2006-04-28 : 02:29:07

I am having four columns CircuitId,IsAddress,isAendAddress and isBendAddress.
Circuit Id IsAddress isAendAddress isBendAddress

aaa Y Y N
aaa N N N
aaa Y N Y

bbb Y N Y
bbb N N N
bbb N N N

ccc Y Y N
ccc Y N Y
ccc N N N

ddd Y Y N
ddd N N N
ddd Y N Y

Now, The objective is to find the Circuit's that have only one isAEndAddress and only one isBendAddress in it in a particular circuit.

For Ex: Circuit aaa,ccc and ddd are having only one 'Y' in isAAendAddress and only one not null value in isBendAddress, so it is eligible.

Output sholud be: aaa,ccc,ddd
Regards,

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-28 : 02:35:43

[code]
declare @table table
(
CircuitId varchar(10),
IsAddress char(1),
IsAendAddress char(1),
IsBendAddress char(1)
)

insert into @table
select 'aaa', 'Y', 'Y', 'N' union all
select 'aaa', 'N', 'N', 'N' union all
select 'aaa', 'Y', 'N', 'Y' union all
select 'bbb', 'Y', 'N', 'Y' union all
select 'bbb', 'N', 'N', 'N' union all
select 'bbb', 'N', 'N', 'N' union all
select 'ccc', 'Y', 'Y', 'N' union all
select 'ccc', 'Y', 'N', 'Y' union all
select 'ccc', 'N', 'N', 'N' union all
select 'ddd', 'Y', 'Y', 'N' union all
select 'ddd', 'N', 'N', 'N' union all
select 'ddd', 'Y', 'N', 'Y'

select CircuitId
from
(
select CircuitId,
sum(case when IsAendAddress = 'Y' then 1 else 0 end) Aend,
sum(case when IsBendAddress = 'Y' then 1 else 0 end) Bend
from @table
group by CircuitId
) c
where Aend = 1
and Bend = 1
[/code]

m.ibbrahim
Starting Member

16 Posts

Posted - 2006-04-28 : 02:38:31

Thanks,
the table was just a sample. Actually I m having 15,000 records with about 400 distinct Circuit Id's. So, I need a generalised one.

Regards,
Mohd.Ibrahim

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-28 : 02:41:21

select id,max(isaendaddress) as 'isaendaddress',max(isbendaddress) as 'isbendaddress'
from circuit
group by id
having max(isaendaddress)='Y' and max(isbendaddress)='Y'

--------------------
keeping it simple...

m.ibbrahim
Starting Member

16 Posts

Posted - 2006-04-28 : 03:04:27

I came out with the following script after RnD:

SELECT DISTINCT(Main.CIRCUIT)
FROM dbo.FinalInventry as Main
LEFT OUTER JOIN
(
SELECT CIRCUIT,COUNT(AendAddress) AS cntAendAddress
FROM dbo.FinalInventry
WHERE AendAddress='Y'
GROUP BY circuit
) AendAddress on Main.circuit = AendAddress.circuit

LEFT OUTER JOIN
(
SELECT CIRCUIT,COUNT(BEndAddress) AS cntBEndAddress
FROM dbo.FinalInventry
WHERE BEndAddress IS NOT NULL
GROUP BY circuit
) BendAddress ON Main.circuit = BendAddress.circuit

WHERE AendAddress.cntAendAddress =1 AND BendAddress.cntBEndAddress=1

Is the above script logically correct to get my output..??

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-28 : 03:19:44

isn't that overkill?
i mean a simple group by would have sufficed right?
--edit, not tested but something like this

select f.id from finalinventory f
join (select id,max(isaendaddress) as 'isaendaddress',max(isbendaddress) as 'isbendaddress'
from circuit
group by id
having max(isaendaddress)='Y' and max(isbendaddress)='Y')s
on f.id=s.id

--------------------
keeping it simple...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-28 : 03:22:38

Yes. It should work. Why don't you give it a try ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-28 : 03:31:33

quote:
Originally posted by jen

isn't that overkill?
i mean a simple group by would have sufficed right?
--edit, not tested but something like this

select f.id from finalinventory f
join (select id,max(isaendaddress) as 'isaendaddress',max(isbendaddress) as 'isbendaddress'
from circuit
group by id
having max(isaendaddress)='Y' and max(isbendaddress)='Y')s
on f.id=s.id

--------------------
keeping it simple...

I don't think this is what m.ibbrahim wants

quote:
The objective is to find the Circuit's that have only one isAEndAddress and only one isBendAddress in it in a particular circuit.

But i agreed m.ibbrahim method might not be efficient as it is accessing the same table 3 times

m.ibbrahim
Starting Member

16 Posts

Posted - 2006-04-28 : 03:33:56

I 'll check it out.. it will take time to check as our server is down.. well,right now we are having a nice break..!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-28 : 03:35:45

"right now we are having a nice break"
That's good. Cheers !

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-28 : 03:36:37

correct, based on his example...


declare @final table(id varchar(3))
insert into @final(id)
select 'aaa'

declare @Circuit table(id varchar(3),IsAddress varchar(2),isAendAddress varchar(2),
isBendAddress varchar(2))
insert into @circuit(id,isaddress,isaendaddress,isbendaddress)
select
'aaa', 'Y', 'Y', 'N'
union select
'aaa', 'N', 'N', 'N' 
union select
'aaa', 'Y', 'N' ,'Y'
union select
'bbb', 'Y', 'N', 'Y' 
union select
'bbb', 'N' ,'N', 'N'
union select 
'bbb', 'N', 'N', 'N' 
union select
'ccc', 'Y', 'Y', 'N'
union select
'ccc', 'Y', 'N', 'Y'
union select
'ccc', 'N', 'N', 'N' 
union select
'ddd', 'Y', 'Y', 'N'
union select
'ddd', 'N' ,'N' ,'N' 
union select
'ddd', 'Y', 'N' ,'Y'

select f.id from @final f
join (
select id,max(isaendaddress) as 'isaendaddress',max(isbendaddress) as 'isbendaddress'
from @circuit
group by id
having max(isaendaddress)='Y' and max(isbendaddress)='Y')s
on f.id=s.id

but hey, he should verify it first, tea break! thanks for reminding

--------------------
keeping it simple...

m.ibbrahim
Starting Member

16 Posts

Posted - 2006-04-28 : 06:16:41

Hi Khtan,
It's working perfectly.

Even,my script is working perfectly.. for all tables..
however,I agree that it is not efficient.. Any suggestions..??

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-28 : 09:17:01

Have you try the one i posted ?

m.ibbrahim
Starting Member

16 Posts

Posted - 2006-05-02 : 01:53:32

Thanks for the feedback,
Khtan, I tried the script you sent.. however,it is not giving me the result I need..

it's returning two circuits:
CCC
DDD

The desired output is:
AAA
CCC
DDD

Regards,
Mohd.Ibrahim

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-02 : 02:06:43

if you just copy and past the code that i posted you will get 3 records aaa, ccc & ddd.

m.ibbrahim
Starting Member

16 Posts

Posted - 2006-05-02 : 03:15:26

If copy,pasted the output is :

declare @final table(id varchar(3))
insert into @final(id)
select 'aaa'

aaa

For:
declare @final table(id varchar(3))
insert into @final(id)
select 'bbb'

No records..

For:
declare @final table(id varchar(3))
insert into @final(id)
select 'ccc'

ccc

For:
declare @final table(id varchar(3))
insert into @final(id)
select 'ddd'

ddd

Regards

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-02 : 03:40:58

the query i posted didn't work also?

--------------------
keeping it simple...

m.ibbrahim
Starting Member

16 Posts

Posted - 2006-05-02 : 03:49:27

I am so sorry guys...
actually, my previous rpely was to the code sent by Jen.
Yes Jen,your code did not worked..
declare @final table(id varchar(3))
insert into @final(id)
select 'aaa'

declare @Circuit table(id varchar(3),IsAddress varchar(2),isAendAddress varchar(2),
isBendAddress varchar(2))
insert into @circuit(id,isaddress,isaendaddress,isbendaddress)
select
'aaa', 'Y', 'Y', 'N'
union select
'aaa', 'N', 'N', 'N'
union select
'aaa', 'Y', 'N' ,'Y'
union select
'bbb', 'Y', 'N', 'Y'
union select
'bbb', 'N' ,'N', 'N'
union select
'bbb', 'N', 'N', 'N'
union select
'ccc', 'Y', 'Y', 'N'
union select
'ccc', 'Y', 'N', 'Y'
union select
'ccc', 'N', 'N', 'N'
union select
'ddd', 'Y', 'Y', 'N'
union select
'ddd', 'N' ,'N' ,'N'
union select
'ddd', 'Y', 'N' ,'Y'

select f.id from @final f
join (
select id,max(isaendaddress) as 'isaendaddress',max(isbendaddress) as 'isbendaddress'
from @circuit
group by id
having max(isaendaddress)='Y' and max(isbendaddress)='Y')s
on f.id=s.id

Khtan,
Your's code is not working as I had needed...

Lunck time folks... enjoy...

Thanks...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-02 : 06:01:14

Think Jen missed out the "ONLY ONE" criteria

quote:
the Circuit's that have only one isAEndAddress and only one isBendAddress in it in a particular circuit.

m.ibbrahim
Starting Member

16 Posts

Posted - 2006-05-02 : 06:04:12

Hi Friends,
How about writing two cursors which will work as follows:
1. The first cursor will iterate thru the table and return the unique circuit id's in the table. i.e it will return aaa,bbb,ccc,ddd to the second cursor.
2.Now, the second cursor will keep a count of the isAEndAddrss and isBEndAddress for that particular circuit id. If, the count of isAendAddress is 1 and the count of isBendAddress is 1,then we will display that circuit id.

Need your opinion..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources