Can't get my head around this!

[ChrisCwmbran]

Hey all,

 

Here is a brief(ish) explanation of my problem. I've omitted all fields that aren't relevant to the query. I have three tables.

 

1. manufacturer which contains an fields called manufacturerID (type int) and manufacturerName (type varchar).

 

2. productType which contains fields called productTypeID (type int) and productDescription (type varchar)

 

3. product which contains fields productID (type int), productName (type varchar), manufacturer (type int) and productType (type int)

 

 

Now for example, a kind of product type is "Monitor" and that has a productTypeID of 2.

 

I want to list all manufactrurerName where one or more products made by that manufacturer are monitors i.e. all manufacturers who made monitors.

 

I can do this using more than one query but I suspect there is a way of doing this in a single query. 

 

Can anyone tell me how this is done please?

 

Thanks in advance! I've been trying to get my head around this for several hours!

 

 

Chris.

[Jessica]

It's called a JOIN

[ChrisCwmbran]

Thanks. Before I read your post I was thinking along the lines of:

SELECT * FROM manufacturer m WHERE m.manufacturer IN (SELECT p.manufacturer FROM product p WHERE p.productType=2);

But I don't think that's a join - and its close to midnight here so my brain is starting to melt.

 

I'll read up on joins. I did lots of SQL training with Oracle but that was 22 years ago and I haven't used it at all since!

 

Thanks for your response!

[Barand]

SELECT DISTINCT m.manufacturerName

FROM manufacturer m

INNER JOIN product p

    ON p.manufacturer = m.manufacturerID

WHERE p.productType = 2