开发者

SQL SELECT across two tables

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 origi开发者_如何学Pythonnal two tables.

I am at a complete loss. Any advice?


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.


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)


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


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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜