These little lines

[Mod]

Here's what i'm working on:

tblPrimer
PrimerID (PK)
PrimerName
OtherDataDealingWithThisPrimer

tblPrimerPair
PrimerPairID (PK)
Primer1 (FK)
Primer2 (FK)
OtherDataDealingWithThisPair


But i'm not sure how to relate these two tables. any advice?

 

[KenHigg]

It would help to know what the they represent in the real world...

ken

 

[farmersckn]

They could be shoes:

Shoe:
Shoe ID
Size
Foot

Pair:
Pair ID
Cost
Left Shoe -> Shoe.Shoe ID
Right Shoe -> Shoe.Shoe ID

Couldn't you relate the Shoe to the Pair that it belongs to?
Shoe:
Shoe ID
Pair ID -> Pair.Pair ID
Size
Foot // Use Foot to determine which shoe it is (#1 or #2?) if that is necesary.

Pair:
Pair ID
Cost

 

[Mod]

It would help to know what the they represent in the real world...

ken

i'm sorry, you're absolutely right. These primers are used to sequence genes. Each primer can be long to many PrimerPairs. Each pair, of course, has two primers.

My main problem is writing a query that will return all of the primer pairs in this form

PrimerPairID   PrimerName1    PrimerName2
------------   -----------    ------------
1              Ron            Nancy
2              Bill           Hillary

So the best I can do is something like this

SELECT tblPrimerPair.PrimerPairID, tblPrimer.PrimerName AS pname1, tblPrimer.PrimerName AS pname2 FROM tblPrimerPair, tblPrimer WHERE tblPrimerPair.primer1=tblPrimer.primerID And tblPrimerpair.primer2=tblPrimer.primerID;

but thats not right, that's not right at all.

 

[KenHigg]

Here's your problem:

The data in PrimerPairID 1 are valid
The data in PrimerPairID 2 are shallow, wanna be

ken

 

[Mod]

Here's your problem:

The data in PrimerPairID 1 are valid
The data in PrimerPairID 2 are shallow, wanna be

ken

bleh. double bleh.

 

[KenHigg]

I take u liked that...

 

[Mod]

I take u liked that...

very clever, that. Maybe i was thinking though that i need a junction table? Or maybe i just have no idea how to write this select statement...

 

[KenHigg]

Sorry...

Lets see.

1. You have a bunch of things called primers
2. Out of all these primers, each one is and only related to another primer
3. This relationship between the two primers is called a primer pair

tblPrimers
primer_id, pk
primer_info1
primer_info2

tblPrimerPair
primer_pair_id, pk
primer_pair_info_1
primer_pair_info_2

tblImtermedateLink
primer_id, cpk
primer_pair, cpk

???
I think this is correct
ken

 

[Mod]

1. You have a bunch of things called primers
2. Out of all these primers, each one is and only related to another primer
3. This relationship between the two primers is called a primer pair

Sorry, i'm still having problems explaining. Each primer can be a member of many pairs, so my example with the names was misleading (incomplete). The results of the query might very well be something like:

PrimerPairID   PrimerName1    PrimerName2
------------   -----------    ------------
1              Ron            Nancy
2              Bill           Hillary
3              Ron            Bill
4              Ron            Hillary
5              Bill           Nancy
6              Nancy          Hillary

To explain the problem in different terms, In table A I have two foreign keys pointing to table B:

tblA
Key
FKey_To_tblB1
Fkey_To_tblB2

tblB
Key
Name

the tables would be populated like this:
tblA

1	1	2
2	1	3
3	2	3
4	1	4

tblB

1	Fido
2	WuffWuff
3	Barky
4	Bitey

And i want to write a SELECT statement that will return the actual names in each pair, like so:

SELECT magicwordstomakedatacome;

+---------+----------+----------+
| dogpair | dog1     | dog2     |
+---------+----------+----------+
| 1       | Fido     | WuffWuff |
| 2       | Fido     | Barky    |
| 3       | WuffWuff | Barky    |
| 4       | Fido     | Bitey    |
+---------+----------+----------+

 

[KenHigg]

1. You have a bunch of things called primers
2. Out of all these primers, each one may be related to another primer
3. This relationship between the two primers is called a primer pair

tblPrimers
primer_id, pk
primer_info1
primer_info2

tblPrimerPair
primer_pair_id, pk
primer_pair_info_1
primer_pair_info_2

tblImtermedateLink
primer_pair, pk
primer_id, fk

???
ken

 

[Mod]

sorry, see my edit^^ above it took so long to make i should have just made a new post.

 

[KenHigg]

In the query builder, bring over the tblA, then bring down 2 instances of tableB.

Link tblA to one fk in the first instance and to the other fk in the other instance.

Bring down the flds to the grid?

???
ken

 

[Mod]

In the query builder, bring over the tblA, then bring down 2 instances of tableB.

Link tblA to one fk in the first instance and to the other fk in the other instance.

Bring down the flds to the grid?

???
ken

excellent beautiful thank you. It gave me this:

SELECT PrimerPairs.PrimerPairID, Primer.PrimerName, Primer_1.PrimerName
FROM (Primer INNER JOIN PrimerPairs ON Primer.PrimerID = PrimerPairs.Primer1) INNER JOIN Primer AS Primer_1 ON PrimerPairs.Primer2 = Primer_1.PrimerID;

The query seems to work ok, I never would have been able to devise that select statement myself.

Thanks again.

 

[KenHigg]

Cool...

(Did I really have an intelligent discussion with someone who knows what 'sequence genes' means)

 

[Mod]

Cool...

(Did I really have an intelligent discussion with someone who knows what 'sequence genes' means)

lol, no, the people i work for know. I just repeat the phrases like a well trained computer monkey.