problem with group by (?) clause

[eva_blue]

Hello to all,

I am quite new to sql, and so the time has come that I don't understand why a query has a certain output.I apologise for the long post but I'd rather describe exactly what I am doing in, hoping that someone can spot the problem that I fail to.
I have created and populated two tables in my database as:

create table binding_sites  (id integer, start_bindsite integer, end_bindisite integer);

And after loading the data it looks like:
id start_bindsite end_bindsite
1 10212250 10212257
2 10105334 10105368
3 101556063 101556073

And

create table genome  (gene_name varchar(50), gene_start integer, gene_end integer);

After population it looks like:

gene_start gene_end gene_name
10212248 10312253 gene1
10212258 10322250 gene2
101556070 102556060 gene3

What I want is to find which gene from table genome is closer to each of the sites 1, 2, 3 in table binding_sites.

I have created a nested query that calculates the linear distances between the gene_start and both the start_bindsite and end_bindsite and then with the outer select I wish to choose the minimum distance. The problem is that when I group by the start_bindsite field the output is indeed for each binding site the minimum distance but the gene_name is always gene1 , whereas for the last two binding sites it is gene2 and gene3 respectively that should be presented. And when grouping by other fields or combination of other fields doesn't seem to help.

The query:

select   temp.start, temp.end, temp.gene_name, min(temp.distance) as minimum_distance
          from (select  start_bindsite, end_bindsite, gene_name, abs(gene_start-start_bindsite) as distance
                   from genome, binding_sites
                   group by  gene_name, start_bindsite
                   union 
                  select  start_bindsite, end_bindsite, gene_name, abs(gene_start-end_bindsite) as distance
                  from genome, binding_sites 
                  group by  gene_name, start_bindsite) as temp
        group by  temp.start

And the output
+-----------+-----------+-----------+------------------+
| start | end | gene_name | minimum_distance |
+------------+-----------+-----------+------------------+
| 10105334 | 10105368 | gene1 | 106880 |
| 10212250 | 10212257 | gene1 | 1 |
| 101556063 | 101556073 | gene1 | 3 |
+-----------+-----------+------------+------------------+
3 rows in set (0.00 sec)

Does anyone have any idea what am I doing wrong here? Any hint or suggestion would be hugely appreciated!!
Thanks for your time in advance,

Eva

[tesuji]

Hello,

1. how come that in

>>> select temp.start, temp.end, temp.gene_name, min(temp.distance) as
>>> minimum_distance from (select start_bindsite, end_bindsite, gene_name,
>>> abs(gene_start-start_bindsite) as distance from genome, binding_sites group by
>>> gene_name, start_bindsite

group-by clause contains 2 columns only whereas you select 3 columns? this should produce a true error, if not, something is wrong with your database.

2. Because of
>>> from genome, binding_sites
you are doing inner join. Therefore you need a where clause with at least one join constraint. Because this is missing an undesirable cross join would be computed.
(I advice you a more modern join construct: from genome g INNER JOIN binding_sites b ON g.column = b.column. Here predicate g.column = b.column is the join constraint.)

krs,
tesu