SQL SELECT across two tables

Question

Hi there, 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?

Answer 1

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.

Answer 2

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

Answer 3

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)

Answer 4

Before going further, you should read at least a basic reference on SQL. The join construct is one of the basics.

You may consider to start reading the answers to Learning SQL.

SQL is not very difficult to learn and you should see quicly the benefits.

Answer 5

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