CODEWITHSUNDEEP
sql
user6611764
user6611764

Reputation:

How to use join to combine two query into one?

How to combine this two query to one? First I select id from doorbots

select id --> 2576673
from doorbots 
where device_id = '38d269cf5d1f';

Then I using this id to select id from dings table

select id 
from dings 
where doorbot_id = 2576673 and deleted_at is NULL;

How to combine this two query to one?

Upvotes: 0

Views: 58

Answers (5)

Jibin Balachandran
Jibin Balachandran

Reputation: 3441

You can achieve the same in many ways. Using JOIN, EXISTS or IN. Both EXISTS and IN uses subquery.So I would suggest you go for JOIN. You can do something like:

SELECT d.id
FROM dings d
INNER JOIN doorbots db ON d.doorbot_id=db.id
WHERE db.device_id='38d269cf5d1f' AND d.deleted_at IS NULL

Upvotes: 0

Damith
Damith

Reputation: 437

You may achieve the same result in tree ways but still in a single query using IN, EXISTS and JOIN.

USING IN:

SELECT id
FROM dings
WHERE doorbot_id IN
  (SELECT id
  FROM doorbots
  WHERE device_id = '38d269cf5d1f')
AND deleted_at IS NULL;

USING EXISTS:

SELECT id
FROM dings
WHERE doorbot_id EXISTS
  (SELECT 1
  FROM doorbots
  WHERE dings.doorbot_id =id and device_id = '38d269cf5d1f')
AND deleted_at IS NULL;

USING JOIN:

SELECT dings.id
FROM doorbots
JOIN dings
ON doorbots.id = dings.doorbot_id
WHERE doorbots.device_id = '38d269cf5d1f'
AND dings.deleted_at IS NULL;

Upvotes: 0

Jigar Prajapati
Jigar Prajapati

Reputation: 112

You can combine it by using left join or join if both tables have relationship like 

SELECT d.id 
FROM dings d 
LEFT JOIN doorbots ON dbs dbs.id = d.doorbot_id 
WHERE d.deleted_at IS NULL AND dbs.device_id = '38d269cf5d1f';

SELECT d.id 
    FROM dings d 
    JOIN doorbots ON dbs dbs.id = d.doorbot_id 
    WHERE d.deleted_at IS NULL AND dbs.device_id = '38d269cf5d1f';

or like this 

SELECT id 
FROM dings 
WHERE id IN (
    SELECT id 
    FROM doorbots 
    WHERE device_id = '38d269cf5d1f'
) AND d.deleted_at IS NULL;

Upvotes: 1

Kaiserbogey
Kaiserbogey

Reputation: 191

You just have to replace the "2576673" in your second query by your first query, surrounded by parentheses.

Upvotes: 0

India.Rocket
India.Rocket

Reputation: 1245

Using in statement you can ask for that specific id which you want.

Try this:-

select id 
from dings 
where doorbot_id in (select id 
from doorbots 
where device_id = '38d269cf5d1f')and deleted_at is NULL;

Upvotes: 1

Related Questions