Display fields from different tables

please allow me to make some suggestions

  1. always use explicit JOIN syntax, not the old style comma-list join with the join conditions in the WHERE clause

  2. always properly qualify all columns with their table names when there is more than one table in the query

  3. use column aliases in the SELECT clause, so that when the result set comes into php, you can distinguish columns which have identical column names from each other using their aliases

  4. if you write the query in a way which guarantees that two columns are always going to be equal, you do not really need to return both columns in the SELECT clause

SELECT products.id           AS products_id
     , products.category_id 
     , categories.name       AS category_name
     , products.product_name
     , products.price
     , products.subcategory
     , products.location
     , products.date_added 
  FROM products
INNER
  JOIN categories 
    ON categories.id = products.category_id

if you take a close look, you will see that his query is identical to the one i gave you in post #4

please don’t change anything back again

The first point I don’t understand you mean that to use
this approach with JOIN

FROM products
INNER
JOIN categories
ON categories.id = products.category_id

Instead of joining as below.

FROM products, categories
WHERE category_id = categories.id AND category_id = “.$idc.”

I don’t know if it is my version of Mysql server or what but The INNER JOIN on this particular situation didn’t work.

Don’t know why.

sorry, “didn’t work” is not a mysql error message that i’m familiar with

:frowning:

WEll, I know you were giving me a suggestion,

Really don’t know what you mean, but we have discuss why it didn’t work because INNER JOIN would return all the rows as well. When WHERE is more specific. That’s what I understood.

Sorry if I miss understood you,

I do think you are very knowledgable,

Thank r937.

this –

FROM products INNER JOIN categories
ON categories.id = products.category_id

is equivalent to this –

FROM products, categories
WHERE category_id = categories.id

if you add this to the comma-style join query –

AND category_id = “.$idc.”

then you must obviously also add it to the JOIN syntax query !!

so this –

FROM products, categories
WHERE category_id = categories.id
AND category_id = “.$idc.”

is equivalent to this –

FROM products INNER JOIN categories
ON categories.id = products.category_id
WHERE category_id = “.$idc.”

doesn’t that make more sense now?

Man, I am a shame that I couldn’t understand the first tiem :slight_smile: it is clear Now I will make an extra effort to avoid you coming to simple language. I understood it I just need to add the other condition to the JOIN.

WHERE category_id = “.$idc.”

As you were going to the steps in the last post, I wonder why not AND instead of WHERE in the INNER JOIN after ON. It seems that the language has change.

FROM products INNER JOIN categories
ON categories.id = products.category_id
WHERE category_id = “.$idc.”

Why not

FROM products INNER JOIN categories
ON categories.id = products.category_id
AND category_id = “.$idc.”

it just a question Mr.

From what I understand AND means that both of the conditions need to be true categories.id = products.category_id AND category_id = “.$idc.”.

Now I can see you have put WHERE what’s is the underline of changing?

The only thing I can figure out is using where to make the sentence Longer in case there is more condition and use AND after where or even have other possibilities and other options to a different query.

you ask a very good question

yes, in the INNER JOIN query, the category_id = “.$idc.” condition can be either in the ON clause or the WHERE clause – they will produce the same results *

however, i prefer to put it into the WHERE clause because when you think about it, it doesn’t really define a join condition, but rather a filter condition

  • except that it makes a very big difference where such a condition is located if it’s an OUTER JOIN

i hope that as you gain more experience with different types of joins, these questions will become easier

:slight_smile: