Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

What's wrong with this SQL query?


Guest

Recommended Posts

I am trying to query the database to bring back results from two separate tables, similar to the way the all products page works, but within the db itself so I can save it as a excel file.

 

I have put this in:

 

SELECT p.products_id, p.products_quantity, p.products_model, pd.products_name, p.products_price,

FROM products, products_description

WHERE p.products_quantity > 0 AND p.products_quantity < 5000

 

I also tried:

 

SELECT p.products_id, p.products_quantity, p.products_model, pd.products_name, p.products_price,

FROM products AND products_description

WHERE p.products_quantity > 0 AND p.products_quantity < 5000

 

But both time it sent back an SQL error.

 

What is wrong with it and does anyone know what it should be?

Link to comment
Share on other sites

Hmm - now I get result but it's not showing the products name and it's telling me I have 1,870,290 results, which is clearly wrong . . .

 

I am not very good at SQL, as you can probably tell.

 

It is surely possible to query more than one table, otherwise the system would not work.

Link to comment
Share on other sites

Ha!! Got it! It's:

 

SELECT p.products_id, p.products_quantity, p.products_model, p.products_price, pd.products_name

FROM products p, products_description pd

WHERE p.products_id = pd.products_id and p.products_quantity > 0 AND p.products_quantity < 5000 AND pd.language_id=1

 

If anyone wanted to know. I forgot the:

 

WHERE p.products_id = pd.products_id

 

to match up the products IDs in the two tables.

Link to comment
Share on other sites

you should say from products p, products_description pd

 

You needed that bit since there's an invisible 'AS' there. In the bit where you're telling it what info you want, you're saying e.g. 'select p.products_id', but what's 'p'?

 

'p' is an alias for the products table, another name to call it. You needed a 'from products AS p', and 'products p' is a shorthand of that.

 

Just thought I'd try and help your SQL out :)

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...