MySQL - SELECT a single value from two optional records based on a query param value
mysql group by
select query in mysql w3schools
mysql> select from multiple tables
Here is my simplified scenario. I have DB records in 'store_config' table:
ID store_id value 1 0 val1 2 10 val2 3 7 val3 4 99 val4
All records are optional - may or may not exist.
store_id column is unique.
I want to run a query:
So that: The query should return a value matching the store_id from the query param if it exists, otherwise return value matching store_id 0 if it exists.
storeId=0 record is considered to be a default value and returned only if there is no existing record for the supplied storeId query param. That's the logic behind it.
You can use
order by and
select t.* from t where store_id in (@store_id, 0) order by store_id desc limit 1;
This assumes (as implied by your question) that there is only one row in the table for each store id. If that is not the case, you might want a more complicated version:
select t.* from t where store_id = @store_id union all select t.* from t where store_id = 0 and not exists (select 1 from t t2 where t2.store_id = @store_id);
MySQL 8.0 Reference Manual :: 13.2.10 SELECT Statement, SELECT is used to retrieve rows selected from one or more tables, and can the AS is optional, a subtle problem can occur if you forget the comma between two in a WHERE clause, because the column value might not yet be determined when If ORDER BY occurs within a parenthesized query expression and also is� SELECT Model, ProdName, ListPrice FROM PRODUCT WHERE ListPrice < (SELECT AVG(ListPrice) FROM PRODUCT) ; The subquery returns a single value: the average list price in the PRODUCT table. The outer query retrieves all rows from the PRODUCT table that have a lower list price than the average list price.
Here is one approach using a
SELECT ID, store_id, `value` FROM store_config WHERE store_id IN (0, 10) ORDER BY store_id DESC LIMIT 1;
The trick here is that if
ID=10 is present, then its record would be the one retained. If
ID=10 is not present, but
ID=0 is present, then this record would be retained. Otherwise, the result set would be empty.
Retrieving Records from Multiple Tables, Everything in this syntax is optional except the word SELECT and the all and use the term only for SELECT statements that retrieve records from two or more tables. The following query joins a table to itself, but assigns an alias to one rows, not based on the particular values that the rows may contain. MySQL - Select Query - The SQL SELECT command is used to fetch data from the MySQL database. You can use this command at mysql> prompt as well as in any script like PHP.
Please try this.
SELECT (IFNULL((SELECT store_id FROM @tbl Where id = @store_id LIMIT 1),0))
MySQL SELECT Statement with Examples, SELECT QUERY is used to fetch the data from the MySQL database. fields such as adding numbers or putting together two string fields into one. WHERE condition is optional, it can be used to specify criteria in the result set GROUP BY is used to put together records that have the same field values. MySQL select from where multiple conditions Written by Guillermo Garron Date: 2015-01-11 10:25:20 00:00 SELECT FROM WHERE multiple conditions. Maybe one of the most used MySQL commands is SELECT, that is the way to stract the information from the database, but of course one does not need all the info inside a database, therefore one should limit the info coming out from the table, there is
Using multi-value parameters in SSRS, This article mentions usage of the multi-value parameter in the SSRS The multi -value parameter allows us to pass either one or more than the input value to the report. We will click the Allow multiple values option in the General tab so At first, we will select the Get values from a query so that we can� This example uses the column heading of the first query for the output. It uses the CONCAT() function to concatenate first name, space, and last name into a full name. MySQL UNION and ORDER BY. If you want to sort the result set of a union, you use an ORDER BY clause in the last SELECT statement as shown in the following example:
Conditional WHERE clauses in SQL, One of the key features of SQL databases is their support for ad-hoc queries: new SELECT first_name, last_name, subsidiary_id, employee_id FROM If we do not use bind parameters but write the actual values in the SQL This feature allows the database to cache multiple execution plans for the same SQL statement. To update values in multiple columns, you use a list of comma-separated assignments by supplying a value in each column’s assignment in the form of a literal value, an expression, or a subquery. Third, specify which rows to be updated using a condition in the WHERE clause. The WHERE clause is optional.
MySQL Tutorial - MySQL By Examples for Beginners, Insert on all Columns INSERT INTO tableName VALUES (column1Value, column2Value,), . A table is made up of columns (or fields) and rows (records ). 'Pen Red', 5000, 1.23); Query OK, 1 row affected (0.04 sec) -- Insert multiple rows in one 'productCode' cannot be null -- Query the table mysql> SELECT * FROM� MySQL Database MySQL Database MySQL Connect MySQL Create DB MySQL Create Table MySQL Insert Data MySQL Get Last ID MySQL Insert Multiple MySQL Prepared MySQL Select Data MySQL Where MySQL Order By MySQL Delete Data MySQL Update Data MySQL Limit Data PHP XML PHP XML Parsers PHP SimpleXML Parser PHP SimpleXML - Get PHP XML Expat PHP XML DOM PHP
- have you tried with limit? SELECT store_id from table where store_id=paramname LIMIT 1 ?
there may be other storeId records in between... there is no internal order to a MySQL table. I have to agree with the above comments; it is not entirely clear what you want to do here.
- @Tim I tried to rewrite the description, is it clear enough now?
- yes, there is only one record per store_id but thanks for extending your answer.
- I just realised it doesn't solve my problem. So if both records are present and the param is store_id = 10, I want to get the value for that store - not storeId=0
- please see description of the issue again, I clarified it a bit more
- @mwdev This correctly answers your question, as does my answer (more or less).
- @mwdev . . . That is what both these queries do.
- @mwdev . . . I find it amusing that this is accepted and has upvotes given that it uses the wrong columns for the comparisons.
- @GordonLinoff For what it's worth, I gave this answer on a cell phone, while on a crowded metro train.
- . . The OP accepted an incorrect answer posted after a correct answer. The OP can do whatever s/he wants. I'm just amused by it.
- @Gordon you're right, wrong colums is one thing but I just realised it doesn't solve my problem. So if both records are present and the param is store_id = 10, I want to get the value for that store
- @mwdev My answer in fact should cover this situation. If both
store_idof 1 and 10 are present, the single latter record would be retained.
- I think I got the idea here but other answers are more elegant and subqueries is what I wanted to avoid. Thanks.