CODEWITHSUNDEEP
sqlsqlitejoin
pips404
pips404

Reputation: 3

SQL - Query conditional from multiple tables

I am trying to find the moments in which column5 data is greater than column6. The following command works for me for results in one table

SELECT * FROM table1 WHERE column5 > column6;

But I would like to query a return from other tables using the same conditional column5 > column6 and still select all because some columns provide additional relevant information and reference. For example, something like 

SELECT * FROM table1, table2 WHERE column5 > column6;

but I get an error complaining from an ambiguous named table column5... I switched the command over to something like 

SELECT * FROM table1, table2 WHERE table1.column5 > table1.column6 AND table2.column5 > table2.column6;

The above command does not produce and error but the query returns an empty result. I have also tried the INNER JOIN command as such

SELECT * FROM table1 INNER JOIN table2 ON table1.seconds = table2.seconds AND table1.column5 > table1.column6 AND table2.column5 > table2.column6;

This yields a result that is similar to the first command but the query results in all rows from table1 from that operator conditional repeated twice in the same row, but table2 results are not shown.

Is there another way to achieve this? Most of my attempts did not return the desired results. Again, I just wanted my query to result the rowid of multiple tables WHERE *.column5 > *.column6

.sql file of the database pasted on codeshare.io to provide sample-data to this problem

Put sample data here:

Table1:
Column5    
"139062.6"
"115080"
"279718.5"
"106184"
"109483"
"152253"
"159030.3333"
"144092.5"
"154913.8333"
"52166.83333"
"18257.5"
"8907"

Column6
"224340.8"
"154723.6667"
"202486.8333"
"107184.8333"
"110674"
"257038.6667"
"151057"
"190702.6667"
"229714"
"37816.16667"
"18061.83333"
"6606.666667"

Table2:
Column5    
"7544.6"
"10165.16667"
"11574.16667"
"9400.833333"
"11421.5"
"11368.5"
"11925.83333"
"9108.833333"
"8276.666667"
"8650.5"
"14998.16667"
"16229.83333"

Column6
"10109"
"14526.83333"
"12070.66667"
"7819.333333"
"9247.833333"
"7201.833333"
"8166.833333"
"4928"
"9135.5"
"9666.166667"
"8201.166667"
"10186"     

Expected Result "WHERE column5 > column6" on table 1 and 2:

    TABLE 1      
Column5      
"279718.5"   
"159030.3333"
"52166.83333"
"18257.5"    
"8907"       

  Column6     
"202486.8333" 
"151057"      
"37816.16667" 
"18061.83333" 
"6606.666667" 


    TABLE 2 
  Column5        
 "9400.833333"    
 "11421.5"          
 "11368.5"      
"11925.83333"       
 "9108.833333"    
 "14998.16667"      
  "16229.83333"     

Column6

"7819.333333"
"9247.833333"
"7201.833333"
"8166.833333"
"4928"
"8201.166667"
"10186"

Upvotes: 0

Views: 900

Answers (1)

martonbognar
martonbognar

Reputation: 462

I think you are looking for the union operator (assuming your tables have the same number of columns):

SELECT * FROM table1 WHERE column5 > column6 
UNION 
SELECT * FROM table2 WHERE column5 > column6;

If you only want the rowids listed, SELECT rowid instead (it depends on how you want to use the result of the query later, as this way you have no way of telling which table the rowid refers to).

Upvotes: 1

Related Questions