How do you join + output from two table?

kamy99

Hi all:

I have two tables;
Products, Categories.

The CatID field in Categories is linked to Category field in Products.

All my form insertions work fine.(just in case.....)

I need to extract info from these two tables and display them order by Category.

Can you help?
This is what I have so far, but it's giving me error:

db_connect();
$getInfo=mysql_query("SELECT C.*, P.* FROM Products as P, Categories as C LEFT JOIN CatID AS Cat ON P.Category=C.CatID order by Productname ");
while ($i=mysql_fetch_array($getInfo))
{
echo $i[2];}

Thanx in advance...
Kamy

tomhath

SELECT C., P.
FROM Products as P, Categories as C
WHERE P.Category=C.CatID order by Category

kamy99

That helped a lot, but didn't solve it!

Perhaps I should clarify;

I need to do an INNER JOIN. Testing a similar DB on Access gave me this query:

SELECT Products.ProdName, Categories.Name
FROM Categories INNER JOIN Products ON Categories.CatID = Products.Category;

In my out put, I need to display the Category Name.

jdh

I'm not quite sure what are trying to do.... so I will draw a picture...

Example:

Tbl1

+------+---------------+
 |   id  |     name      |
+-----------------------+
 |  1    |      Jake       |
 |  2    |      Sam       |
 |  3    |     Ralph      |
 -------------------------

And so on...

Tbl2
+-------------------------+
 |   nextel    |  holder  |
+-------------------------+
 |  662-7732 |    3      |
 |  989-2234 |    1      |
 |  888-5555 |    2      |
 ---------------------------

So, when you go through tbl2, and see that the holder of the phone is id # 3, it also returns the name from tbl1, Ralph?

This is something I have been trying to do too. I haven't tried anything yet, mine is much more complicated that the example here, just need an idea how to tackle it.

Right now, I just have it put in name, like instead of 3, 1, 2, it inserts: Ralph, Jake, Sam. If there is a better way, please let me know!

Mahamoti

tomhath's post is correct.

The syntax used there is equivalent to an inner join in MySQL, and should give you the output you're looking for.

Mahamoti