I have a series of bird species names that are sorted taxonomically (by ID). I would like to cluster sub-species (which currently have a clust value of 0) with the species (clust <> 0) that falls above them in the…
I have a database design with multiple many-to-many relationships, and am trying to write a SELECT statement that retrieves a result set with ONE ROW PER USER. To do this, I'd like to aggregate the permissions into a single column.…
I need to get list of transactions with list of logs and tags inside each transaction and the most important make search by field in logs and tags and unite it all. Example below is search by field1 and tag1.…
I wrote a SQL query to fetch all movies and, if present, include the name 'Brad Pitt' for movies where he's a cast member. The query is as follows: select m.title, p_cast.person_name From movie m left join ( movie_cast m_cast…
I have stumbled upon a peculiar phenomena in an SQL select: select 1 , ui as row_value , ui is null as "ui is null" , ui is not null as "ui is not null" from user_info ui where ui.user_id…
I am a beginner trying to build very simple inventory system using PHP + SQL I have 2 warehouse. I added 2 tables, 1 for each warehouse contains (item id)-(in)- (out) like shown below table1 item id in out item1…
Please suppose these two tables: users table: id account_id 1 1 2 1 3 1 4 1 user_labels table: id user_id title 1 2 label1 2 2 label2 3 1 label1 4 1 label2 now I want to select users…
The goal is to return an array of available users that are assigned to the site we are working on and are available to do the task we want to assign. For this question, we can assume that we are…
When I perform EXPLAIN on the following query to fetch users with bookings SELECT user.id FROM user LEFT JOIN booking ON booking.user_id = user.id AND booking.end_timestamp > 1706878800 AND booking.end_timestamp <= 1706882400 AND booking.status IN ('pending', 'progress', 'done'); I get…
We have this query that is running: SELECT concat_ws(' ', z.ticker, CASE WHEN a.date_approved IS NOT NULL THEN TO_CHAR(a.date_approved,'YYMMDD') WHEN z.date_recommended IS NULL THEN '000000' ELSE TO_CHAR(z.date_recommended,'YYMMDD') END, CASE WHEN s.ticker IS NOT NULL THEN 6 WHEN a.approved_recommendation IS NOT…