CODEWITHSUNDEEP
c#sql
mitomed
mitomed

Reputation: 2066

Merge two SQL queries into one

I need to do raw SQL for a particular method in C# (using otherwise NHibernate) and I'm struggling to merge queries to different tables as I hardly use other than ORM. In this case I'm querying three of them and I would like to use the offerId's from the results from the first simple select in the last line of the second query (instead of using the harcoded 1003)

SELECT * FROM [OFFER]
WHERE CampaignId = 1 AND Processed Is NULL

SELECT [BANK].* FROM [BANK]
INNER JOIN [REMINDER]
ON [BANK].personId = [REMINDER].personId AND [BANK].isCurrent = 1
WHERE [REMINDER].offerId = 1003

I have tried to do the first query and then use a foreach but then I will end up with as many queries to the database as results so it will kill performance, right?

Thanks

Upvotes: 2

Views: 971

Answers (4)

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

The data is somehow linked. That means there must be a foreign key there. You should go for an inner join then.

SELECT [BANK].* [OFFER].* FROM [BANK]
INNER JOIN [REMINDER]
  ON [BANK].personId = [REMINDER].personId AND [BANK].isCurrent = 1
INNER JOIN [OFFER]
  ON [REMINDER].offerId = [OFFER].id
WHERE [OFFER].CampaignId = 1 AND [OFFER].Processed Is NULL

This is assuming the primary key in [OFFER] is id.

Upvotes: 1

Yuck
Yuck

Reputation: 50835

This is the most literal translation and probably easiest to follow given the original two queries:

SELECT [BANK].* FROM [BANK]
INNER JOIN [REMINDER]
ON [BANK].personId = [REMINDER].personId AND [BANK].isCurrent = 1
WHERE [REMINDER].offerId IN (

  SELECT OfferId FROM [OFFER]
  WHERE CampaignId = 1 AND Processed Is NULL

)

You could also use JOIN syntax:

SELECT [BANK].*
FROM [BANK]
     JOIN [REMINDER] ON [BANK].personId = [REMINDER].personId
                          AND [BANK].isCurrent = 1
     JOIN [OFFER] ON [REMINDER].OfferId = [OFFER].OfferId
WHERE [OFFER].CampaignId = 1 AND [OFFER].Processed Is NULL

Note that INNER JOIN and JOIN are equivalent syntax.

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

Use the IN predicate:

SELECT [BANK].* 
FROM [BANK]
INNER JOIN [REMINDER] ON [BANK].personId = [REMINDER].personId 
WHERE [BANK].isCurrent = 1
  AND [REMINDER].offerId IN (SELECT offerId FROM [OFFER]
                             WHERE CampaignId = 1 AND Processed Is NULL)

Upvotes: 1

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

SELECT [BANK].* FROM [BANK]
INNER JOIN [REMINDER]
ON [BANK].personId = [REMINDER].personId AND [BANK].isCurrent = 1
WHERE [REMINDER].offerId in
(
SELECT offerId  FROM [OFFER]
WHERE CampaignId = 1 AND Processed Is NULL
)

Upvotes: 1

Related Questions