[RESOLVED] INNER JOINS - is it the way to go

[cheese62]

Good morning all,

Thanks for taking the time to look at my post.

I have two table products and supplier.

I need to get the telephone number out of the supplier table to display in a table.

There is a common column in both tables called ACCOUNTNO, so I wrote the following query;

SELECT products.*, supplier.TEL FROM products INNER JOIN supplier ON products.ACCOUNTNO=supplier.ACCOUNTNO WHERE products.STOCKCOD = '010030' ORDER BY products.STOCK10 ASC LIMIT 100

There are no syntax errors, but it doesn't return any data. I thought it would return every record from the PRODUCTS table and add a column called TEL

Help would be appreciated on this.

Thanks,

Neil.

[nemonoman]

Your SQL is right and should return values you expect. So your problem is that the data is not set up like you think.'

To debug:

do you ACTUALLY have product WHERE products.STOCKCOD = '010030' ?

Check: SELECT * FROM products WHERE products.STOCKCOD = '010030'

If you get no values returned, you've found you're problem. Fix the data or Query.

IF you do find a record, CAREFULLY review the value returned by products.ACCOUNTNO

then
SELECT * FROM supplier WHERE ACCOUNTNO= <<RETURNED VALUE HERE
You will probably NOT return any records

You will need to figure out how the data in products.ACCOUNTNO differs from supplier.ACCOUNTNO

Some likely problems:
one table's data has different type: integer in table 1, varchar in table 2

ACCOUNTNO has leading or trailing spaces

ACCOUNTNO in one table contains quotes or single quotes.

[cheese62]

Yep that was it.... rubbish in, rubbish out.

What do I learn from this. Don't assume your code doesn't work. Check you data.

Thanks again,

Neil.

:o