Plz help me to solve this query [Resolved]

Posted by Hmanjarawala under Sql Server on 11/2/2011 | Points: 10 | Views : 2375 | Status : [Member] | Replies : 3

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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/

[Resolved]

Reply | Reply with Attachment

Alert Moderator

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.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

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 Solutions Hmanjarawala, 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

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