Plz help me to solve this query [Resolved]

Posted by Hmanjarawala under Sql Server on 11/2/2011 | Points: 10 | Views : 1196 | 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
 0 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.typeHimanshu 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
 0 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 SolutionsHmanjarawala, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Ndebata on: 11/2/2011 [Member] Starter | Points: 25
 0 ```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
Latest Posts