I have asked this question before here: Between two tables how does one SELECT the table where the id of a specific value exists mysql however I feel that I didn't phrase it well enough.
I have 2 tables in my "Hiking" database lets say table 1 is called "Forest" and table 2 is called "Mountain". Both tables have a FOREIGN KEY "Trip_id" which is a PRIMARY KEY in table "Trip" (or something, this is a made up example) that is AUTO_INCREMENT. A trip can either be Mountain or Forest, so the 2 tables do not share any Trip_ids. They also each have an attribute that they do not share with the other table. Mountains has an attribute "Temperature" and Forests has an attribute "Atmosphere". What I want to do, is extract either "Temperature" or "Atmosphere" depending on which, Mountains or Forests contains the Trip_id value 74.
SELECT Temperature FROM Mountain WHERE Trip_id = 74 OR
SELECT Atmosphere FROM Forest WHERE Trip_id = 74;
(I know the code above does not work).
I did end up solving this problem using Java:
String sqlStatement = "SELECT Trip_id FROM Mountains WHERE Trip_id = 74";
if (/*(execute sql statement) == null*/){
//Use Forest (and Atmosphere)
}
else{
//Use Mountain (and Temperature)
}
However there are no if statements in mysql, so I was wondering if it at all was possible to solve this using mysql.
What I was thinking was something like this: SELECT * FROM FOREST OR MOUNTAIN WHERE Trip_id = 74 EXISTS; (I know this code is rubbish and completely wrong but I hope it helps illustrate what I am aiming for).
P.S if the columns were the same, then this would be the best answer:
select m.*
from mountains m
where m.trip_id = 74
union all
select f.*
from forests f
where f.trip_id = 74;
Thanks to Gordon Linoff for providing that answer.
There ARE if statements in mysql. Anyway!
You can do this:
SELECT Temperature as Value FROM Mountain WHERE Trip_id = 74
Union All
SELECT Atmosphere as Value FROM Forest WHERE Trip_id = 74
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments