[SOLVED] MySQL Query from 2 tables id = product_id

[woocha]

Hey Guys...

 

I am trying to do a simple SQL query on 1 Database but from 2 seperate tables.

 

I have one table 'products' with an int id auto increment and I have another table 'items' with an int product_id .  What I want to do is, make a one line SQL query that will grab any results where product_id {from table 1} = id{from table 2}

 

Anyone have any experience with this?

 

Thanks guys

[unkwntech]

SELECT * FROM table1, table2 WHERE table1.product_id = table2.id AND OTHER CONDITIONS HERE

[woocha]

Hey Thank you for the quick reply...

The code I was trying is:

$query = "SELECT instock FROM inventory where instock < 1 and products.item_number = inventory.id";

 

That is very similar to what you suggested, but mine did not work.  Can you see anything I might try differently?

[unkwntech]

You did not seelct and data from the second table, look at how my query is written.

SELECT * FROM table1, table2 WHERE table1.product_id = table2.id AND table1.instock < 1

from BOTH tables --^----^                                                  ^

then you have to reference each column with the table name --^

and specify the table1.product_id = table2.id before your other conditions

[adam84]

SELECT i.instock 
FROM inventory i, products p 
WHERE 
i.instock < 1 AND 
p.item_number = i.id

[woocha]

You did not seelct and data from the second table, look at how my query is written.

SELECT * FROM table1, table2 WHERE table1.product_id = table2.id AND table1.instock < 1

from BOTH tables --^----^                                                  ^

then you have to reference each column with the table name --^

and specify the table1.product_id = table2.id before your other conditions

SWEET !!

 

That worked like a charm...I thank you for the tip.  Your post squared it away for me.

Thank you