Posts by ferensick

Solution 1:

SELECT cd_e_calls.bedrijfs_id AS gegevens_id, gebeld , status
FROM cd_e_calls , cd_e_gegevens , cd_e_negatief
WHERE cd_e_gegevens.RvV != '1'
AND bedrijfs_id = 
(
SELECT 
cd_e_gegevens.id AS gegevens_id,
COUNT(cd_e_calls.bedrijfs_id), MAX(cd_e_calls.gebeld)
FROM cd_e_gegevens
JOIN cd_e_calls ON cd_e_gegevens.id = cd_e_calls.bedrijfs_id
JOIN cd_e_negatief ON cd_e_negatief.gegevens_id != cd_e_gegevens.id
AND cd_e_calls.bedrijfs_id = cd_e_gegevens.id 
AND cd_e_negatief.gegevens_id != cd_e_gegevens.id
HAVING COUNT(cd_e_calls.bedrijfs_id) = 2
HAVING MAX(cd_e_calls.gebeld) NOT LIKE '2009-08-25' 
GROUP BY cd_e_gegevens.id
ORDER BY id DESC
LIMIT 1
)

I receive te following error:
MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'HAVING MAX( cd_e_calls . gebeld ) NOT LIKE '2009-08-25' GROUP BY cd_e_gegevens ' at line 1

After changing the second HAVING with AND, I receive the following:
MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY cd_e_gegevens . id ORDER BY id DESC LIMIT 1 )
LIMIT 0, 30' at line 1

I can't figure that out..

Solution2:
MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'HAVING MAX( cd_e_calls.gebeld ) NOT LIKE '2009-08-25'
)
ORDER BY id DESC
LIMI' at line 14

After changing the second HAVING to AND:
MySQL said:

#1241 - Operand should contain …

/* Solution 2 --again I can not test... please do so and tell me results =) */
--2

[B]SELECT[/B] cd_e_calls.bedrijfs_id AS gegevens_id, gebeld , status
[B]FROM[/B] cd_e_calls , cd_e_gegevens , cd_e_negatief
[B]WHERE[/B] cd_e_gegevens.RvV != '1'
[B]AND[/B] bedrijfs_id = 
(
	[B]SELECT[/B] cd_e_gegevens.id AS gegevens_id
	[B]FROM[/B] cd_e_gegevens
	[B]WHERE [/B]
		(
			[B]SELECT COUNT[/B](cd_e_calls.bedrijfs_id), [B]MAX[/B]( cd_e_calls.gebeld )
			[B]FROM[/B] cd_e_calls
			[B]WHERE[/B] cd_e_calls.bedrijfs_id = cd_e_gegevens.id 
			[B]HAVING COUNT[/B](cd_e_calls.bedrijfs_id) =2
			[B]HAVING MAX[/B]( cd_e_calls.gebeld ) NOT LIKE '2009-08-25'
		)
		[B]ORDER BY[/B] id [B]DESC[/B]
		[B]LIMIT[/B] 1
)
[B]AND[/B] cd_e_negatief.gegevens_id != cd_e_gegevens.id
[B]LIMIT[/B] 1

/* Solution 1: I am sorry I can not test this or play with it... please test and let me know the results and if it's the desired results */
------

[B]SELECT[/B] cd_e_calls.bedrijfs_id AS gegevens_id, gebeld , status
[B]FROM[/B] cd_e_calls , cd_e_gegevens , cd_e_negatief
[B]WHERE[/B] cd_e_gegevens.RvV != '1'
[B]AND[/B] bedrijfs_id = 
	(
			[B]SELECT [/B]
			cd_e_gegevens.id AS gegevens_id,
			[B]COUNT[/B](cd_e_calls.bedrijfs_id), [B]MAX[/B](cd_e_calls.gebeld)
			[B]FROM[/B] cd_e_gegevens
			[B]JOIN[/B] cd_e_calls ON cd_e_gegevens.? = cd_e_calls.?
			[B]JOIN[/B] cd_e_negatief ON cd_e_negatief.gegevens_id != cd_e_gegevens.id
			[B]AND[/B] cd_e_calls.bedrijfs_id = cd_e_gegevens.id 
			[B]AND[/B] cd_e_negatief.gegevens_id != cd_e_gegevens.id
			[B]HAVING COUNT[/B](cd_e_calls.bedrijfs_id) = 2
			[B]HAVING MAX[/B](cd_e_calls.gebeld) NOT LIKE '2009-08-25'	
			[B]GROUP BY[/B] cd_e_gegevens.id
			[B]ORDER BY[/B] id [B]DESC[/B]
			LIMIT 1
	)

Yes you can do a join. I am working on it now. Also even if MyISAM is the default storage engine you can still change the table's storage engine; ALTER TABLE (table name) ENGINE=InnoDB;
You can try this with your existing query out of curiosity. In the meantime I am working on an altered query for the above. =)

*thanks I can see how many records there is now. =) Indexing could be useful.

-- original query

[B]SELECT [/B]
cd_e_calls.bedrijfs_id as gegevens_id, gebeld, status
[B]FROM[/B] cd_e_calls, cd_e_gegevens, cd_e_negatief
[B]WHERE[/B] cd_e_gegevens.RvV != '1'
[B]AND[/B] cd_e_calls.bedrijfs_id = 
(
	[B]SELECT[/B] cd_e_gegevens.id as gegevens_id
	[B]FROM[/B] cd_e_gegevens
	[B]WHERE[/B] 
		(
			[B]SELECT COUNT[/B](cd_e_calls.bedrijfs_id)
			[B]FROM[/B] cd_e_calls
			[B]WHERE[/B] cd_e_calls.bedrijfs_id = cd_e_gegevens.id 
		) =2
			[B]AND[/B] 
		(
			[B]SELECT MAX[/B](cd_e_calls.gebeld)
			[B]FROM[/B] cd_e_calls
			[B]WHERE[/B] cd_e_calls.bedrijfs_id = cd_e_gegevens.id
		) [B]NOT LIKE[/B] '2009-08-25'
		[B]ORDER BY[/B] id [B]DESC[/B]
		[B]LIMIT[/B] 1 -- is this necessary? I would take it out and try it again, since you have LIMIT 1 already at the end
)
[B]AND[/B] cd_e_negatief.gegevens_id != cd_e_gegevens.id
[B]LIMIT[/B] 1

-------------------------
Other option, turn your subquery into a view:

[B]CREATE OR REPLACE ALGORITHM[/B] = TEMPTABLE [B]VIEW [/B][U] v_cd_e_GCB[/U] [B]AS[/B]
[B]SELECT[/B] cd_e_gegevens.id as gegevens_id
[B]FROM[/B] cd_e_gegevens
[B]WHERE[/B] 
	(
		[B]SELECT COUNT[/B](cd_e_calls.bedrijfs_id)
		[B]FROM[/B] cd_e_calls
		[B]WHERE[/B] cd_e_calls.bedrijfs_id = cd_e_gegevens.id 
	) =2
		[B]AND[/B] 
	(
		[B]SELECT MAX[/B](cd_e_calls.gebeld)
		[B]FROM[/B] cd_e_calls
		[B]WHERE[/B] cd_e_calls.bedrijfs_id = cd_e_gegevens.id
	) [B]NOT LIKE[/B] '2009-08-25'
[B]ORDER BY[/B] id DESC
[B]LIMIT[/B] 1 -- if you want the view to be re-usable, take this limitation out, it will be more scalable

-------------------------
New Query with view:

[B]SELECT [/B]
cd_e_calls.bedrijfs_id as gegevens_id, gebeld, status
[B]FROM[/B] cd_e_calls, cd_e_gegevens, cd_e_negatief
[B]WHERE[/B] cd_e_gegevens.RvV != '1'
[B]AND[/B] cd_e_calls.bedrijfs_id = 
(
SELECT * FROM [U]v_cd_e_GCB[/U] LIMIT 1
)
[B]AND[/B] cd_e_negatief.gegevens_id != cd_e_gegevens.id
[B]LIMIT[/B] 1

-------------------------
What I think the real problem is, the database set up. MySQL is great at optimizing queries as they are. I have done a few tests of my own with different queries (same results) with execution time barely changing. I noticed that your selecting …

Hi kishan112,
Which storage engine are you using (MyISAM, InnoDB)? And how many records are expected to be returned?
Out of curiosity how are you executing the query? (via phpMyAdmin, MySQL Administrator)?

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

To Generate HTML based on your MySQL database you can use QCODO: http://www.qcodo.com/
I've used this before and it's pretty handy.
Also what is the overall objective? Are you trying to manage your DB via your web browser? Or are you trying to make a simple report? For managing PHPMyAdmin is very useful.
And to simply have a table displayed on a web page in a chart form, use PHP to get the table values, and HTML to format the table.
QCODO may be too much if you just want to display one table in HTML.
http://www.freewebmasterhelp.com/tutorials/phpmysql/4
Example:

<?
$username="username";
$password="password";
$database="your_database";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM contacts";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

echo "<b><center>Database Output</center></b><br><br>";

$i=0;
while ($i < $num) {

$first=mysql_result($result,$i,"first");
$last=mysql_result($result,$i,"last");
$phone=mysql_result($result,$i,"phone");
$mobile=mysql_result($result,$i,"mobile");
$fax=mysql_result($result,$i,"fax");
$email=mysql_result($result,$i,"email");
$web=mysql_result($result,$i,"web");

echo "<b>$first $last</b><br>Phone: $phone<br>Mobile: $mobile<br>Fax: $fax<br>E-mail: $email<br>Web: $web<br><hr><br>";

$i++;
}

?>

n/p I'm glad it's working. :-)

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.

This seems like a pretty big project. But there are some useful tools or apps that have been developed already doing these types of things... such as: http://www.phpguru.org/static/TableEditor.html Possibly this could be used and modified to fit your needs. Have you started anything at all? Are you using a framework (like Zend... which has a lot of built in classes already done for you)?
Some more info may help you get some more answers. I hope you can figure this out. :-)

Possibly this is solved already. (I hope) =)
Nonetheless, what I noticed that wasn't mentioned are the file formats.
Yes they are both .CSV however Lowes2.csv is in a MAC ANSI file format and the other is in a Windows ANSI file format. This makes a difference with the line breaks, which could affect the upload as well. Download a smart editor like Notepad++ (for windows) and something like TextMate for MAC... pretty much any editor where you can change the file format (not just the extension). If MySQL is on windows, then the MAC line breaks could be affecting the upload.

I attached the updated Lowes2.csv in a Windows ANSI format. If you edit it on your MAC, this will change. So try uploading it first without editing it.

*Sorry I just noticed that the line feeds were mentioned already. I noticed the comma thing as well. Anyway I hope these files are all sorted out now.
Cheers

Take a look at this if you have not already:
http://dev.mysql.com/doc/refman/5.1/en/out-of-memory.html

"To remedy the problem, first check whether your query is correct. Is it reasonable that it should return so many rows? If not, correct the query and try again. Otherwise, you can invoke mysql with the --quick option. This causes it to use the mysql_use_result() C API function to retrieve the result set, which places less of a load on the client (but more on the server). "

To easily manage MySQL over the web, try to use PHPMyAdmin. It is very useful. With this you will also be able to easily export or backup your database(s) without having to write the queries manually.

You may not get this error in the 'Production' environment even though you have the same set up. If your internal server has more memory / free space than your testing server then you may not get this error.

I am also curious. What engine are you using? For larger databases you should be using InnoDB. Usually by default MyISAM is used. You can easily change the tables from MyISAM to InnoDB without affecting the records or anything. Please post back if you figured it out and if any of this helped. I hope it does. =)

cgyrob is correct. In general for simple joins there is no performance difference. If you have larger complicated queries with several filters and joins or subqueries, strictly for readability it would be better to use the proper JOIN syntax so the filters and join conditions can easily be separated.
Keep in mind you can not do FULL OUTER JOINS in MySQL (I'm not sure if you can in version 6. I haven't upgraded yet). But there are work arounds. =)

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.

Not enough info is given here... such as, db used, is this front-end, back-end, etc?
One way to do this is to simply use a SELECT statement.

SELECT * FROM <table1> WHERE <field1> LIKE '%value%'

The best bet is to have the statements automatically generated, having the <tables> and <fields> as variables.