CODEWITHSUNDEEP
sqljoinsubquery
SgtOVERKILL
SgtOVERKILL

Reputation: 319

How to use a join rather than two sub queries

I have these two queries that were originally a subquery that I would like to do on a join but I am not sure how to do this properly.

My smaller example table looks like this

SupplierId|CompanyName|Country

These fields are mandatory so they can all be joined on. My original subquery looks like this

select *
from Suppliers
where CompanyName != 'Nord-Ost-Fisch Handelsgesellschaft mbH'and Country = 
(Select country 
from Suppliers 
where CompanyName = 'Nord-Ost-Fisch Handelsgesellschaft mbH');

The first part of my query is finding a country that belongs to a company name and returns Germay

Select Country
From Suppliers 
Where CompanyName = 'Nord-Ost-Fisch Handelsgesellschaft mbH'

The second part of my query finds all suppliers that correlate to the above query. Where Country = 'Germany' should not be hardcoded in.

Select *
From Suppliers
Where Country = 'Germany' -- this based on above statement
 and CompanyName != 'Nord-Ost-Fisch Handelsgesellschaft mbH'

The query I am trying looks like this

 Select *
 From Suppliers s join Suppliers ss
 on s.SupplierID = ss.SupplierID
 Where ...

I am just failing to see how if I joined these two tables, how I can still get the country of Germany and then query against that result.

Upvotes: 3

Views: 60

Answers (1)

BradC
BradC

Reputation: 39986

First of all, there's no inherent reason why you can't keep it a subquery in this case; I actually think it makes the intent slightly more obvious.

But if you want a self-join instead, here is how it would look (I used s for the table that will return the actual supplier info, and c for the table that is simply filtering by country):

select s.*
from Suppliers s
inner join Suppliers c
    ON s.Country = c.Country
WHERE c.CompanyName = 'Nord-Ost-Fisch Handelsgesellschaft mbH'
AND s.CompanyName <> 'Nord-Ost-Fisch Handelsgesellschaft mbH'

Just be specific about which table field you are filtering on.

Or, if you like, you could put the filter on c as part of the join:

select s.*
from Suppliers s
inner join Suppliers c
    ON s.Country = c.Country
    AND c.CompanyName = 'Nord-Ost-Fisch Handelsgesellschaft mbH'
WHERE s.CompanyName <> 'Nord-Ost-Fisch Handelsgesellschaft mbH'

Upvotes: 2

Related Questions