CODEWITHSUNDEEP
mysqlsqldatabase
Cornelius
Cornelius

Reputation: 361

Between two tables how does one SELECT the table where the id of a specific value exists mysql (duplicate)

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.

Upvotes: 0

Views: 93

Answers (3)

Sohail
Sohail

Reputation: 574

In my opinion there are two ways to solve this.
1: you can enforce user to tell either its a forest trip or mountain trip. It will cost just a single click and then send an extra arugment in query, 'F' for Forest and 'M' for mountain. like 

  CASE @lv_option 
   WHEN 'F' THEN 
       SELECT Atmosphere  FROM [FOREST]
   ELSE 
       SELECT Temperature FROM [MOUNTAIN]
  END as weather

2: if you don't want to change your screen view, you can use if-exists command on the basis receiving trip_id. like

  IF EXISTS (SELECT id FROM [FOREST] WHERE trip_id=@lv_trip_id)
     BEGIN
       SELECT Atmosphere as weather FROM [FOREST] WHERE trip_id=@lv_trip_id
     END
  ELSE
     BEGIN
        SELECT Temperature as weather FROM [MOUNTAIN] WHERE trip_id=@lv_trip_id
     END
  END

Upvotes: 0

Sam
Sam

Reputation: 3155

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

Upvotes: 4

Mayur Sawant
Mayur Sawant

Reputation: 11

You can use below the query.

Select
T.TripID,
M.Temperature,
F.Atmosphere
From
Trip T
LEFT OUTER JOIN Mountain M
ON T.TripID = M.TripID
LEFT OUTER JOIN Forest F
ON T.TripID = F.TripID
Where T.TripID = 74

So in the above query if both Mountain and Forest table have this ID then Temp and Atmosphere will be displayed. If either Mountain OR Forest table have this ID then Temp OR Atmosphere will be displayed.

Upvotes: 0

Related Questions