basic SQL question

[Gomez Fabre, Pedro Manuel]

Dear all,

I have the following problem,

I am trying to select records from two tables.

the tables are constructed like:
block
block_id
sequence_id
snp_required
first_polymorphism_index
last_polymorphism_index
first_reference_positio
last_reference_position
start_pos
end_pos
tiled_bp

polymorphism_block_map
block_id
polymorphism_index
polymorphism_id

When I try to do:

mysql> select first_polymorphism_index,last_polymorphism_index from
block where first_polymorphism_index like "14163" OR
first_polymorphism_index like "14750" OR first_polymorphism_index
like "14932";
+--------------------------+-------------------------+
| first_polymorphism_index | last_polymorphism_index |
+--------------------------+-------------------------+
| 14163 | 14235 |
| 14750 | 14802 |
| 14932 | 14980 |
+--------------------------+-------------------------+
3 rows in set (0.02 sec)

everything works fine, easy ;)

but if I try to link these two tables I got:
+--------------------------+--------------------+-----------------+-----
--------------------+--------------------+-----------------+
| first_polymorphism_index | polymorphism_index | polymorphism_id |
last_polymorphism_index | polymorphism_index | polymorphism_id |
+--------------------------+--------------------+-----------------+-----
--------------------+--------------------+-----------------+
| 14163 | 14163 | P0216196
| 14235 | 14235 | P0216288 |
| 14750 | 14750 | P0217007
| 14235 | 14235 | P0216288 |
| 14932 | 14932 | P0217251
| 14235 | 14235 | P0216288 |
+--------------------------+--------------------+-----------------+-----
--------------------+--------------------+-----------------+
3 rows in set (1 min 27.48 sec)

##
# this does not return the right values on the las polymorphism index
##

SELECT
b1.first_polymorphism_index,
p1.polymorphism_index,
p1.polymorphism_id,
b2.last_polymorphism_index,
p2.polymorphism_index,
p2.polymorphism_id
FROM
block as b1,
polymorphism_block_map as p1,
block as b2,
polymorphism_block_map as p2
WHERE
b1.first_polymorphism_index = p1.polymorphism_index
AND
b2.last_polymorphism_index = p2.polymorphism_index
LIMIT 5;
But the second polymorphism_index is always the same,

How I can get the right one?

Thanks in advance

P
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my***********...ie.nctu.edu.tw