INNER JOIN grief

Report · Nov 20, 2007

ASP VB and Access. I am struggling with inner joins! Currently, I am trying to replace a number in my main table with the number that it relates to in another table... as you would expect an inner joint to do!

I have two tables.

The first table is 'Manufacturers' with the columns of ManufacturerID and ManufacturerName

The second table is 'Products' with a column of ProductID, ManufacturerID, ProductName and many more not so relevant to this requirement.

I need to get the number in Products.ManufacturerID to be replaced by Manufacturers.ManufacturerName when it is read into a browser.

I am sorry to be so blatant, but what is the sql for this? I just can't figure it out! It's gone 3 a.m. and I am at the end of my tether!

Here's one example that I have attempted, but nothing is working:

SELECT Manufacturers.Manufacturer, Products.ManufacturerID FROM Manufacturers INNER JOIN Products ON Manufacturers.Manufacturer = Products.ManufacturerID"

I hope somebody can help me. I am sooo stuck...

Many thanks.

Mat

Report · Nov 21, 2007

All your wanting to do is show the Manufacturers.ManufacturerName & Products.ManufacturerID?

"SELECT Manufacturers.Manufacturer, Products.ManufacturerID FROM Manufacturers, Products WHERE Manufacturers.ManufacturerID = Products.ManufacturerID"

Wouldnt that do the job?

Report · Nov 21, 2007

Yes! Excellent! That did the job, but it has left me in complete confusion now.

In order to take a reference from one table that was simply a number and replace it with text from another table that is related to that column, I thought you had to use a JOIN.

My first forey into a JOIN statement and I have fallen at the first hurdle - not understanding when to use JOIN! Is there any info on JOIN statements that I can learn from. I have looked on the web and I have found examples of how to do a join, but not how or when to use them. Would it be easy to explain here, or would that be a long job?

Thanks for your reply anyway.

Mat

Report · Nov 21, 2007

"matthew stuart" <webforumsuser@macromedia.com> wrote in message
news:fi178h$fia$1@forums.macromedia.com...
> Yes! Excellent! That did the job, but it has left me in complete confusion
> now.
>
> In order to take a reference from one table that was simply a number and
> replace it with text from another table that is related to that column, I
> thought you had to use a JOIN.

No you aren't replacing anything,
you select the text value from the other table where that other table
contains a numeric column with the same value as the numeric column in first
table.

> My first forey into a JOIN statement and I have fallen at the first
> hurdle -
> not understanding when to use JOIN! Is there any info on JOIN statements
> that I
> can learn from. I have looked on the web and I have found examples of how
> to do
> a join, but not how or when to use them. Would it be easy to explain here,
> or
> would that be a long job?

You use a join when you want to return data from multiple tables that have
values that can be correlated in some way expressible in SQL.

> Thanks for your reply anyway.
>
> Mat

Report · Nov 21, 2007

This can all be done in Access using their drag'n'drop tools. Create a new
query and away you go.

--
Jules
http://www.charon.co.uk/products.aspx
Charon Cart
Ecommerce for ASP/ASP.NET

Report · Nov 22, 2007

I thought it worked, but it didn't, well, not quite! It bought in a manufacturers name, but only the one that was first in the manufacturers table.

I also had a go at Jules' drag n drop suggestion, but that had pretty much the same effect - it only bought in the first manufacturer name in the table!

I don't know why it is so difficult, but all I need to do is replace the number in the product table that corresponds to the name of the manufacturer in the manufacturers table!

Has anyone got any other ideas?

Thanks.

Mat