I want to show the closest related item for a product. So say I am showing a product and the style number is SG-sfs35s. Is there a way to select whatever product’s style number is closest to that? …
Tag: mysql
How to order by maximum of two column which can be null in MySQL?
I want to do something like : maxof(Null,1000) should be 1000, How to implement the maxof? Answer If you know that salaryplus will always be greater than salaryminus, then you can do coalesce will return the first value which is not null, or (in this example) 0, if both values are null. Otherwise, do somethin…
GROUP BY behavior when no aggregate functions are present in the SELECT clause
I have a table emp with following structure and data: When I execute the following SQL: I get the following result: On what basis did the server decide return Jill and Fred and exclude Jack and Tom? I am running this query in MySQL. Note 1: I know the query doesn’t make sense on its own. I am trying to
PHP MySQL INSERT return value with one query execution
Is there anything returned from MySQL/PHP on a INSERT query being executed? Here is my function which I have in a CLASS. This is how I call the function But executing a INSERT query the $rs returns nothing. Does my function need help or is this the default behavior? Any tips would be helpful as well. Answer I…
Combining OUTER JOIN and WHERE
I’m trying to fetch some data from a database. I want to select an employee, and if available, all appointments and other data related to that employee. This is the query: SELECT TA.id, TEI….
Combining UNION and LIMIT operations in MySQL query
I have a Jobs and a Companies table, and I want to extract 20 jobs that meet the following criteria: Jobs only from two (2) named companies There can at most be 10 jobs per company I have tried the following SELECT with UNION DISTINCT, but the problem is that the LIMIT 0,10 applies to the whole result set. I
Selecting a single row in MySQL
I am using MySQL, I have a table that has 9 columns. One of them is the primary key. How can I select a single row, by the primary key or column 8 or 4? Answer If I understand your question correctly: The ‘LIMIT’ keyword makes sure there is only one row returned.
Unknown column in ‘field list’ error on MySQL Update query
I keep getting MySQL error #1054, when trying to perform this update query: It’s probably some syntax error, but I’ve tried using an inner join instead and other alterations, but I keep getting the same message: Answer Try using different quotes for “y” as the identifier quote characte…
MySQL “Group By” and “Order By”
I want to be able to select a bunch of rows from a table of e-mails and group them by the from sender. My query looks like this: The query almost works as I want it — it selects records grouped by e-mail. The problem is that the subject and timestamp don’t correspond to the most recent record for a
Running a .sql script using MySQL with JDBC
I am starting to use MySQL with JDBC. I have 3-4 tables to create and this doesn’t look good. Is there a way to run a .sql script from MySQL JDBC? Answer Ok. You can use this class here (posted on pastebin because of file length) in your project. But remember to keep the apache license info. JDBC Script…