Plz help me to solve this query [Resolved]

Posted by Hmanjarawala under Sql Server on 11/2/2011 | Points: 10 | Views : 1280 | Status : [Member] | Replies : 3
Hello plz somebody help me to solve this query.

I've two table

table x:

id Type
=== ======
1 1
2 1
3 1
4 2
5 2

table y:
id Flag
=== =====
1 0
2 1
3 0
4 0
5 0

now my query is find out ids having same type but not contain same flag values.
i.e. as shown in example id 1,2,3 having same type 1 but their flag values are not same. but id 4 and 5 having same type and same flag values. so output of query will be:

id Type Flag
=== ===== =====
1 1 0
2 1 1
3 1 0

plz solve this query asap

Himanshu Manjarawala
Sr. Software Engineer@AutomationAnywhere
http://fieredotnet.wordpress.com/



Responses

Posted by: Hmanjarawala on: 11/3/2011 [Member] Bronze | Points: 25

Up
0
Down

Resolved
select x.*
from
(
select x.type
from y inner join x on y.id=x.id
group by x.type
having count(x.type)>1 and count(x.type)<>sum(y.type) and sum(y.type)<>0
) as a
where x.type=a.type

Himanshu Manjarawala
Sr. Software Engineer@AutomationAnywhere
http://fieredotnet.wordpress.com/

Hmanjarawala, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: PandianS on: 11/2/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
Can you pls try this...!
Select x.id, x.[Type], y.Flag from tablex x Join tabley y

On (x.id = y.id)
Where x.[Type] = 1


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Hmanjarawala, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ndebata on: 11/2/2011 [Member] Starter | Points: 25

Up
0
Down
SELECT TX.ID,TX.Type,TY.Flag FROM TABLEX TX

INNER JOIN
(SELECT [Type] FROM (
SELECT [Type],FLAG FROM TABLEX X
INNER JOIN TABLEY Y ON X.ID=Y.ID
GROUP BY [Type],Flag )ABC
Group By [Type]
HAVING COUNT(1)>1
)XYZ
ON TX.Type=XYZ.Type
INNER JOIN TABLEY TY ON TX.ID=TY.ID


Hmanjarawala, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response