SQL SELECT across two tables

Question

I am a little confused as to how to approach this SQL query.

I have two tables (equal number of records), and I would like to return a column with which is the division between the two.

In other words, here is my not-working-correctly query:

SELECT( (SELECT v FROM Table1) / (SELECT DotProduct FROM Table2) );

How would I do this? All I want it a column where each row equals the same row in Table1 divided by the same row in Table2. The resulting table should have the same number of rows, but I am getting something with a lot more rows than the original two tables.

I am at a complete loss. Any advice?

You might want to clear up the tags a bit. Are you using MySQL, MSSQL (Microsoft SQL Server), or some other database? — JohnFx, Mar 25 '10 at 20:29

score 3 · Answer 1 · answered Mar 25 '10 at 20:24

3

It sounds like you have some kind of key between the two tables. You need an Inner Join:

select t1.v / t2.DotProduct
from Table1 as t1
inner join Table2 as t2
    on t1.ForeignKey = t2.PrimaryKey

Should work. Just make sure you watch out for division by zero errors.

answered Mar 25 '10 at 20:24

Justin Niessner

242,243
40
408
536

I am getting 4 copies of each result. You ever seen this? – Brett Spurrier Mar 25 '10 at 20:29
Are you using the inner join, or the hash join I posted originally (which was wrong...I misread the question)? – Justin Niessner Mar 25 '10 at 20:30

score 2 · Answer 2 · edited Mar 25 '10 at 20:28

2

You didn't specify the full table structure so I will assume a common ID column to link rows in the tables.

SELECT table1.v/table2.DotProduct
FROM Table1 INNER JOIN Table2 
            ON (Table1.ID=Table2.ID)

edited Mar 25 '10 at 20:28

Jesse Weigert

4,714
5
28
37

answered Mar 25 '10 at 20:27

JohnFx

34,542
18
104
162

score 1 · Answer 3 · answered Mar 25 '10 at 20:26

1

You need to do a JOIN on the tables and divide the columns you want.

SELECT (Table1.v / Table2.DotProduct) FROM Table1 JOIN Table2 ON something

You need to substitue something to tell SQL how to match up the rows: Something like: Table1.id = Table2.id

answered Mar 25 '10 at 20:26

Jesse Weigert

4,714
5
28
37

score 0 · Answer 4 · answered Mar 25 '10 at 20:38

In case your fileds are both integers you need to do this to avoid integer math:

select t1.v / (t2.DotProduct*1.00) 
from Table1 as t1 
inner join Table2 as t2 
    on t1.ForeignKey = t2.PrimaryKey

If you have multiple values in table2 relating to values in table1 you need to specify which to use -here I chose the largest one.

 select t1.v / (max(t2.DotProduct)*1.00) 
 from Table1 as t1 
 inner join Table2 as t2 
    on t1.ForeignKey = t2.PrimaryKey
 Group By t1.v

SQL SELECT across two tables

4 Answers4

Linked

Related