|
|
Posted by Erland Sommarskog on 09/10/07 21:18
Rex (rakeshv01@gmail.com) writes:
> Below is a table with sample data to explain what I want to achieve.
>
> trackID member marker
> allele1
> ------- ------ --------------------------------------------------
> -----------------------------------------------------
> 4734 4577 01-D8S1179 13.5
> 4734 4577 02-D21S11 12.6
> 4734 4577 03-D7S820 2.0
> 4734 4577 04-CSF1PO 3.0
> 4734 4577 06-TH01 4.0
> 4734 4577 07-D13S317 5.0
> 4734 4577 08-D16S539 9.0
> 4734 4577 11-vWA 6.0
> 4734 4577 12-TPOX 7.0
> 4734 4577 13-D18S51 1.0
>
> 4734 4578 01-D8S1179 13.0
> 4734 4578 02-D21S11 12.6
> 4734 4578 03-D7S820 2.0
> 4734 4578 04-CSF1PO 4.0
> 4734 4578 06-TH01 3.0
> 4734 4578 07-D13S317 5.0
> 4734 4578 08-D16S539 9.0
> 4734 4578 11-vWA 7.0
> 4734 4578 12-TPOX 6.0
> 4734 4578 13-D18S51 1.0
>
> Okay I am trying to compare allele1 values for differences for two or
> three different member having the same trackID. The list of markers
> would always be same for all members belonging to a particular
> trackID.
>
> So in the above sample data. The trackID is same which is 4734. There
> are two members 4577 and 4578. The markers are same for each members
> but the corresponding allele1 values are different.
>
> I want to compare these two and state whether they are same or not. I
> would like to do this on a form containing subforms.
Don't really know where the form or the subform comes into the
picture. This is a group for a database engine. :-)
SELECT CASE WHEN EXISTS (SELECT 1
FROM tbl
WHERE member IN (@member1, @member2, @member3)
AND trackID = @trackID
GROUP BY marker
HAVING MIN(allelel) < MAX(allellel)
THEN 'There are difference'
ELSE 'All allellel are equal'
END
This is a bit of a guess, since it's not clear how you want the data.
For the future, a tip is that if you post:
o CREATE TABLE statement for your table.
o INSERT statements with sample data.
o The desired result given the sample.
Your odds for getting a tested query in respose are quite good.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|