CODEWITHSUNDEEP
mysqlsql
Script47
Script47

Reputation: 14539

Retrieving data from one table while comparing value from another table

Overview

I have two tables as can be seen below:

user_planes
----------------------------------
|id |user_id|plane_id|fuel|status|
----------------------------------
| 2     1        1     1   Ready |
----------------------------------

shop_planes
------------------------
|id |name|fuel_capacity|
------------------------
| 1   bob       3      |
------------------------

Foreign Key              Primary Key
user_planes.plane_id <-> shop_planes.id

I want to be able to get every field (SELECT *) in user_planes and name and fuel_capacity based on the following criteria:

The Issue and My Attempts

I've tried JOIN however it retrieves data which doesn't fit that criteria, meaning it gets extra data which is from shop_planes and not user_planes.

SELECT * FROM `user_planes` WHERE fuel IN (SELECT shop_planes.fuel_capacity FROM shop_planes WHERE fuel < shop_planes.fuel_capacity) AND user_planes.user_id = 1 AND status = 'Ready'

and

SELECT * FROM `user_planes` INNER JOIN `shop_planes` ON user_planes.fuel < shop_planes.fuel_capacity AND user_planes.user_id = 1 AND user_planes.status = 'Ready'

The desired result is that the query should use the data stored in user_planes to retrieve data from shop_planes while at the same time not getting any excess data from shop_planes.

Disclaimer

I really struggle using JOIN queries, I could use multiple separate queries however I wish to optimise my queries hence I'm trying to bring it in to one query.

If their isn't clarity in the question, please do say, I'll update it to the best of my ability.

Note - Is there an easy query builder option available either through phpmyadmin or an alternative software?

Thanks in advance.

Upvotes: 2

Views: 182

Answers (3)

Shawn
Shawn

Reputation: 4786

Try something like:

SELECT 
      up.id AS User_Plane_ID
    , up.[user_id]
    , up.plane_id
    , up.fuel
    , up.[status]
    , sp.name AS shop_Plane_Name
    , sp.fuel_capacity AS shop_Plane_Fuel_Capacity
FROM User_Planes up
INNER JOIN Shop_Planes sp ON up.plane_id = sp.id  
    AND up.fuel < sp.Fuel_Capacity
WHERE up.[status] = 'Ready'
    AND up.[user_id] = ?

Definitely find a tutorial for JOINs, and don't use SELECT *. With SELECT *, you may end up querying much more than you actually need and it can cause problems if the table changes. You'll enjoy your day much more if you explicitly name the columns you want in your query.

I've aliased some of the columns (with AS) since some of those column names may be reserved words. I've also moved the JOIN criteria to include a filter on fuel

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

First you need the base JOIN

  SELECT up.*  -- only user_plane fields
  FROM shop_planes sp  -- CREATE alias for table or field
  JOIN user_planes up
    ON sp.id  = up.plane_id

Case 1: apply a filter in where condition with php parameter.

  SELECT up.*
  FROM shop_planes sp
  JOIN user_planes up
    ON sp.id  = up.plane_id 
  WHERE up.user_id = ?

Case 2: apply a filter in where condition with string constant

  SELECT up.*
  FROM shop_planes sp
  JOIN user_planes up
    ON sp.id  = up.plane_id 
  WHERE user_planes.status = 'Ready'

Case 3: aply filter comparing fields from both tables

  SELECT up.*
  FROM shop_planes sp
  JOIN user_planes up
    ON sp.id  = up.plane_id
  WHERE up.fuel < sp.fuel_capacity

Upvotes: 0

Shadow
Shadow

Reputation: 34231

Your last attempt was not a bad one, the only thing you missed there was the join criteria you described at the beginning of your post. I also moved the other filters to the where clause to better distinguish between join condition and the filters.

SELECT `user_planes`.*
FROM `user_planes`
INNER JOIN `shop_planes` ON user_planes.plane_id = shop_planes.id 
WHERE user_planes.fuel < shop_planes.fuel_capacity AND user_planes.user_id = 1 AND user_planes.status = 'Ready'

Upvotes: 1

Related Questions