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

illzz

You can use an INNER JOIN to only return results with matching IDs.

SELECT
  {fields}
FROM products p
  INNER JOIN items i ON p.id=i.product_id

woocha

Thank you for the quick reply 🙂

I tried this code below:

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

can you tell me if mine accomplishes the same thing...at least in theory. Because I coouldn't get mine to work????

illzz

You will still need to join the tables. Try this instead:

$query = " SELECT inventory.instock FROM inventory "
       . " INNER JOIN products ON products.item_number = inventory.id "
       . " WHERE inventory.instock < 1";

It's a good practice to prepend the table names (or aliases) to the column names when you have multiple tables in your query. This is to avoid running into ambiguous column name errors if you have tables with columns that have the same name. And also it makes it easier to tell which fields belong to which tables.

woocha

Thank you...I appreciate the help 🙂

illzz

No problem. Don't forget to mark this thread as Resolved using the "Thread Tools".