I have googled a lot and couldn’t find a clear answer to my question assume we have this query SELECT * WHERE user_id = x ORDER BY date_created If we have a single column index on user_id and another …
Tag: where-clause
Limit MYSQL search results to dates greater than variable
I have a MYSQL table posts which contains a VARCHAR(25) column created that is filled with datetimes in the format XXXX-XX XX:XX:XX. I’m attempting to search by dates > now – 24 hours. When searching with just one posts.uuid I get the desired result, which lists all posts from that uuid that ar…
Join tables to display null if right side table doesnt have data
I have a feedback_ques, feedback_ans table as follows: I want to join the tables as follows: This gives me all the fields. I want the query to return null for answers if they arent found in the answers table. For example member_id 20 doesnt have answers, so i would want the table to display null values as bel…
PostgreSQL. constraint allowing only one of many possible values
I have statuses like this: started,calculated,finished I need a constraint allowing only one NOT finished status in a table. This is allowed: this is forbidden due to two not finished statuses: Answer You can use a filtering unique index: The trick is to pass a fixed value instead of a column name to the on c…
MySQL select all column names where condition is met for specific row
I have a table which links users to groups which is set up as below: I would like to know if there is a method to select all groupIDs (column names) where a certain condition is met. E.g. if I want to find all groups where user 2 has a level greater than 0 it would return (1,2,4) It is
Select from cross tables in postgres problem
Lets imagine i have a table A that contains rows X,Y and Z, and i have another table that relate elements from A to another table B. A_B : element_A, element_B, and i want a query that for element b in B returns for all elements a in A return True if {a, b} exist in the table A_B and
Date conversion in SQL from 10-09-2020 to 2020-09-10
I want to convert it for comparing data & here I am stuck. I have data in date column like 10-09-2020 where convert(varchar, date, 111) >= ‘2020-09-10’ AND convert(varchar, date, 111)
getting different results from the same query according to the value of a variable
suppose we have the following masterdata: and we have the order tables: what is requested is to have one single query that could return 2 different sets of data, according to the value of a given variable v_order_status: v_order_status = ‘del’ then only transport orders is retrieved: v_order_statu…
how to check a date is in current financial year
I am trying to check a condition if given date is in current financial year i.e april to march. but not getting any idea how to do code select nvl(Count(1), 0)+1 from ASET where IPE=’O’ and irt in (…
PostgreSQL(function) – Implementation of where clause to get rows by a value(concatenate) in the JSONB column
I am trying to get the rows from a table where I want the condition to check for a value from a jsonb column. The column stores the data as: In the function, I check for the value using: but I want the 420 to be replaced with “ID” which I pass through the function. The only way I came