always use explicit JOIN syntax, not the old style comma-list join with the join conditions in the WHERE clause
always properly qualify all columns with their table names when there is more than one table in the query
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
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
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.
Man, I am a shame that I couldn’t understand the first tiem 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.
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