Performing a nested select query in MYSQL with the wildcard character (*)

Question

Performing a nested select query in MYSQL with the wildcard character (*)

Within my incidents table, there are various fields including index, timestamp, refNum, priority, status, type, email, telephone, title, description, system, category, time, requiredBy, dateReason, oneOff, url, browserDevice, jsessionid, customersProducts, investigationsUndertaken, whatquestions, supportingInformation, and open.

I am a beginner in nesting queries and I am attempting to retrieve all records from the incidents table where either the title or status matches a keyword within a specified timestamp range. Individually, the queries work fine, but when nested together, I receive an error stating #1241 - Operand should contain 1 column(s). Can someone help me identify the mistake in my query?

SELECT
    *
FROM
    `incidents`
WHERE
     `timestamp` BETWEEN '2014-12-01 00:00:00' AND '2015-11-23 23:59:59'  
IN
(SELECT * FROM `incidents` WHERE `title` LIKE '%test%' OR `status` LIKE '%test%')

My desired outcome is to display all fields using *, filter them based on the timestamp range, and search for specific keywords within the title or status fields. In this case, I have used the keyword 'test' as an example.

php mysql select join nested

Answer 1

Answer №1

It is essential to include an index column when writing a subquery to ensure the main query can retrieve specific data. Using SELECT * is not valid as there is no primary key specified. In this case, I have selected the column index (assuming it is an index).

SELECT
    *
FROM
    `incidents`
WHERE
     `timestamp` BETWEEN '2014-12-01 00:00:00' AND '2015-11-23 23:59:59'  
AND index IN
(SELECT index FROM `incidents` WHERE `title` LIKE '%test%' OR `status` LIKE '%test%')

Answer 2

It is essential to include an index column when writing a subquery to ensure the main query can retrieve specific data. Using SELECT * is not valid as there is no primary key specified. In this case, I have selected the column index (assuming it is an index).

SELECT
    *
FROM
    `incidents`
WHERE
     `timestamp` BETWEEN '2014-12-01 00:00:00' AND '2015-11-23 23:59:59'  
AND index IN
(SELECT index FROM `incidents` WHERE `title` LIKE '%test%' OR `status` LIKE '%test%')

Performing a nested select query in MYSQL with the wildcard character (*)

Answer №1

Similar questions

Is there a way to divide a string that has two characters and store each of them in different variables?

Folder with a Unicode title containing Apache htdocs

Failure to trigger AJAX Success or Error Events

JSON object that is nested within another JSON object

Failed to select mySql database using easyphp with error message "Database selection failed."

When choosing from the drop-down menu, the selected box displays the ID instead of

Issue with flash installation

What are your preferred HTMLPurifier configurations for sanitizing CSS styles?

Explore the concealed features of Laravel like the hidden attributes such as password for enhanced security

Issues with assigning values to a variable in a PHP/MySQL prepared statement?

Using PHP to remove a single Gmail contact from your list is a simple

Retrieve the names from one table by querying another table

Transform the character encoding from a non-standard format to UTF-8

PHP error: Trying to access an undeclared variable after using the require function

This route does not allow the use of the POST method. Only the GET and HEAD methods are supported. This limitation is specific to Laravel

Iterating through an instance of stdClass named "object1"

Sending extensive information using PHP

Show individual row information within a bootstrap modal upon clicking the edit button

The SSL certificate is functioning properly on my localhost through XAMPP, however, it is not working with localhost:9001

Creating an SQL select statement with a three-level expression is a complex task that requires careful planning and