CODEWITHSUNDEEP
phpsqlpostgresql
Brett
Brett

Reputation: 12007

Joining Two SQL Tables in a query

I have two tables (with the same schema)

Table1
id   title   D0    D1    D2    D3
------------------------------------
1    Title1  0.12  3.23  4.90 -0.12
1    Title1  0.22  0.32 -4.90  0.12
1    Title1  0.13  1.24  3.50 -0.22
...
1    TitleN  1.22  2.33  3.90 -1.56

and 

Table2
id   title   D0    D1    D2    D3
------------------------------------
1    Title1  1.42 -0.93 -2.99  3.22
1    Title1  0.52  3.32 -4.90  0.54
1    Title1  2.13  1.14  3.50 -0.22
...
1    TitleN  3.42  4.37  3.90 -1.26

I am trying to figure out how to do a query like can do this math:

SELECT title FROM Table2 WHERE (Table1_Row1_D0*Table2_Row1_D0)+(Table1_Row1_D1*Table2_Row1_D1)+(Table1_Row1_D2*Table2_Row1_D2) < 0.5;

However, I would like the query to iterate through the rows of Table1 and perform the SELECT against the entire Table2. Basically, I want to select the titles from Table2 where the calculation inequality is met against ALL the row combination of Table1 and Table 2.

Is this possible???

Not sure it matters, but I am using Postgre.

Upvotes: 2

Views: 449

Answers (3)

RichardTheKiwi
RichardTheKiwi

Reputation: 107736

Basically, I want to select the titles from Table2 where the calculation inequality is met against ALL the row combination of Table1 and Table 2.

For that you will want the reverse condition, where there does NOT exist an equality in Table1 for that Table2 row.

SELECT distinct title
FROM Table2
WHERE NOT EXISTS (
    SELECT *
    FROM Table1
    WHERE (Table1.D0*Table2.D0)+(Table1.D1*Table2.D1)
            +(Table1.D2*Table2.D2) >= 0.5
)

Upvotes: 2

anon
anon

Reputation: 4618

You'll want a CROSS JOIN

SELECT Table2.title 
FROM Table2 
CROSS JOIN Table1
WHERE (Table1.D0*Table2.D0)+(Table1.D1*Table2.D1)+(Table1.D2*Table2.D2) < 0.5;

Upvotes: 1

Geoffrey Wagner
Geoffrey Wagner

Reputation: 818

You should be using a union. The only caveat is your returning fields from your selects must match

 (SELECT * FROM Table1 WHERE conditions) UNION (SELECT * FROM Table2 WHERE conditions)

Do your checks on the script side as long as your not pulling up too much data. You also have the option to add sub selects to the where condition to limit both sides of this union query.

Upvotes: 0

Related Questions