Posts by ferensick

Sorry, 1 more suggestion:
You can also use the multiple queries you have and put it in 1 SQL statement, like this:
SELECT
(SQL Query 1) a,
(SQL Query 2) b,
(SQL Query 3) c,
(SQL Query 4) d
FROM dual --this line is optional... will work without 'FROM dual'

The advantage of using subqueries this way is that you can turn this into a VIEW. You can not use a SUBQUERY in the FROM clause in a view. The first solution I posted above can not be turned into a view with MySQL 5x. (Not sure about MySQL 6x). However when using subqueries in this way... you can use a VIEW:

CREATE OR REPLACE VIEW v_products_images AS
SELECT
(SQL Query 1) a,
(SQL Query 2) b,
(SQL Query 3) c,
(SQL Query 4) d

Hi lifeworks. There could be an easy way to do this, depending on the values in your table. I'll explain first then write a sample query. All you need to do is select your records from the IMAGES table and ORDER them in such a way that the product ID is ordered in a non-repetitive way. Example; Product_ID in IMAGES table should be ordered like this: 1, 2, 3, 4, 5, 6, 7, 8, 9, 1, 2, 3, 4, 5, 6, 7, 8, 9... (assuming there are only 9 product_id's in the IMAGES table).
This way you can do this:

SELECT * FROM products p JOIN
(SELECT * FROM images ORDER BY _______) i ON p.id = i.product_id LIMIT 0, 9

Let me know if this is clear enough or if it's possible. Also give some more information if you need to. =) I hope this can help.
Do a LEFT JOIN if you want all the records in the products table regardless if it has an image associated with it or not.

Hi lifeworks, this could work, but does it matter which image is pulled for each product?

SELECT * FROM products, images WHERE products.id = images.product_id ORDER BY ____ LIMIT 0, 1

Depending what your ORDER BY column(s) are, will determine which image is selected first... this could help if you need a specific image for a given product id.
I just realized this of course will work for 1 product but obviously not for multiple products. I'm think a subquery may work. Let me sit on it for a couple minutes. I'll post back.

re: vnanaji - mysql installation
I've seen this problem before. I recommend using a more current version. However your problem is that you have to run the script from the root dir where MySql is installed.
./scripts/mysql_install_db --user=mysql

Pretty much as mentioned the constraints are the way to go. You can use a lookup table to easily do this as well. So you have your lkp_Status table, just with those 3 values. And you can create a primary foreign key constraint as well. This way in the future if a value is added and there needs to be 4 values you can just add the value in the lookup table and the primary foreign key relationship remains the same. It doesn't need to change.

Hi,
It seems that your .xls file is being shared. Turn the sharing off and you should not have this issue anymore. I know it Excel 2003, when you open the .xls file you can change the sharing option under TOOLS in the menu bar. I am not sure where it is in 2007. Take a look at the EXCEL help -> Sharing Workbook.
I hope this helps.