Select from multiple tables

[Jim Weinberg]

Hi all.

I have two tables: products and products_description. They have different number of columns. I want to select information from both tables with a single query. When I try the query below, I get a 1064 error. Can you help?

SELECT `products.products_id`, `products.products_image`, `products.products_price`, 'products_description.products_name', 'products_description.products_desc'
FROM `products`, 'products_description' 
where`products.products_id` = `products_description.products_id`;

[guelphdad]

1 major problem, you have single quotes around some of your column names and one of your table names. That makes them strings instead of references to the column or table.

The others you have backticks.

In fact backticks aren't needed at all, they are needed around reserved words should you use them for a column or table name, but the best practice is not to use reserved words at all.

Second issue, and it is a habit rather than incorrect, but the comma join syntax is best avoided, you will run into issues with it when your queries get more complicated.

Use an INNER JOIN instead:

SELECT 
  products.products_id, 
  products.products_image, 
  products.products_price, 
  products_description.products_name, 
  products_description.products_desc
FROM 
  products
INNER JOIN
  products_description 
ON
products.products_id = products_description.products_id

[Jim Weinberg]

guelphdad:

Thank you for your reply. When I didn't hear from anyone, I decided to just use 2 separate select statements. It was a bit more coding, but worked.

That said, I will now go back and try your solution.

Appreciate the input.

Regards.