i'm pretty decent with SQL but it's not my main experience of expertise. A colleague asked me a stumper of a question the other day. I was wondering if you guys could provide assistance. Basically we're doing an over all count and grouping these counts by days... but we'd also like to kind of subquery those counts to get a specific type of count. This is what I have so far that's not quite right. select d.start_date, count(a.status), ( select count(checked_ins) as checked_ins from ( select distinct(a.id) as checked_ins from schema1.employee_se_assignment a, schema2.se d where a.se_id= d.id and a.status not in ('unassigned') and d.customer_name in (‘Test Client’) and a.status = 'checked_in' ) src ) from schema1.employee_se_assignment a, schema2.se d where a.se_id= d.id and a.status not in ('unassigned') and d.customer_name in (‘Test Client’) and a.status = 'checked_in' group by d.start_date Order by d.start_date that yields the following results. which is almost what he wants. but it's clearly yielding the total of all of a certain type of status when we want it broken down by day. EG (see below):

I have a question/tagging system like StackExchange. I want to display the tags associated with each question and also show the total number of times the tag is used, unless the question has been taken offline or is suspended, then it is not included in the total number count. Tables are: QUESTIONS - includes fields: suspended and offline TAGS - includes fields: tag_id and tag_name TAGS_X - includes fields: tag_id, question_id The query below almost works, but the subquery seems to return the total count of times the tag is used and the filtering on the suspended and offline fields is not functioning as I intended (does not seem to filter on those conditions). SELECT tags_x.tag_id, tags.tag_name, tags_x.question_id, questions.suspended, questions.offline, (select count(tags_x.tag_id) from tags_x WHERE tags_x.tag_id=tags.tag_id AND questions.suspended = 0 AND questions.offline = 0) num from tags_x LEFT JOIN tags ON tags.tag_id = tags_x.tag_id LEFT JOIN questions ON questions.question_id = tags_x.question_id WHERE questions.suspended = 0 AND questions.offline = 0 Below shows a typical result. Tag 'a' actually shows up in 21 rows. One row is filtered where the offline value is 1. The subquery count is returning 22, but I really want it to show the filtered result of 21. Seems like the WHERE/(offline, suspended) filters in the subquery are incorrectly applied. Can you help me out to determine the correct way to do this?

Oracle 11g R2 Unfortunately our application has per row security "*features*". We have a query that looks about like this: **Bad, slow:** SELECT someRow, someOtherRow FROM bigTableA a WHERE EXISTS ( SELECT 0 FROM bigTableA_securitymapping b WHERE b.PrimaryKeyTableA = a.PrimaryKeyTableA AND b.accesscode in (SELECT accesscode FROM accesscodeView WHERE user = :someUserID) ) There a unique index on bigTableA_securitymapping(PrimaryKeyTableA,accesscode). The accesscodeView could potentially return more than one accesscode for a given user, so it must be IN() and not =. The issue is that this query ignores the unique index for bigTableA_securitymapping and chooses to do a full table scan. If I change the IN() to an = then it does a UNIQUE SCAN on the unique index on bigTableA_securitymapping and is about 50 times faster. **Good, fast but not possible:** SELECT someRow, someOtherRow FROM bigTableA a WHERE EXISTS ( SELECT 0 FROM bigTableA_securitymapping b WHERE b.PrimaryKeyTableA = a.PrimaryKeyTableA AND b.accesscode =(SELECT distinct accesscode FROM accesscodeView WHERE user = :someUserID) ) But, I cannot do that because the accesscodeView may return more than one row. (There's a distinct in there because the accesscodeView needs it given the =, putting the DISTINCT on the original query makes no difference.) If I hardcode the accesscodes, it also does a UNIQUE SCAN on the unique index for bigTableA_securitymapping. **Good, fast but requires large application change:** SELECT someRow, someOtherRow FROM bigTableA a WHERE EXISTS ( SELECT 0 FROM bigTableA_securitymapping b WHERE b.PrimaryKeyTableA = a.PrimaryKeyTableA AND b.accesscode in (1,2,3,4) ) Changing to a join inside doesn't really help either. It still does a full table scan. **Bad, slow:** SELECT someRow, someOtherRow FROM bigTableA a WHERE EXISTS ( SELECT 0 FROM accesscode ac INNER JOIN bigTableA_securitymapping b ON ac.accesscode = b.accesscode WHERE b.PrimaryKeyTableA = a.PrimaryKeyTableA AND user = :someUserID ) So why the difference between = and IN() in. And why does a non-correlated subquery (the accesscodeview subquery) cause such a plan difference? Is there any way to rewrite it to do what I want? The difference in 'good plan' costs vs 'bad plan' costs here are 87 vs 37,000 and a large amount of time in real runtime for the same results.

I need to delete rows from a table based on what is present in a temporary table. For me, both of these statements work : DELETE from main_table where id in (select deletable_id from temporary_table); and DELETE main_table from main_table join temporary_table on main_table.id = temporary_table.deletable_id; Which of the two is advisable to use given the fact that main_table will be having a billion of rows and the temporary will have a few thousands.

I have a query with subqueries that use only elements from the GROUP BY clause:

SELECT 
		DATE(cm.created_at) as "date", 
		COUNT(1) as total_message,
		room_id,
		(SELECT seller_id FROM chat_room cr WHERE cr.id = cm.room_id LIMIT 1) seller_id,
		(SELECT count(1) FROM "order" o WHERE o.room_id = cm.room_id) total_order,
		(SELECT sum(total_amount) FROM "order" o WHERE o.room_id = cm.room_id and DATE(o.created_at) = date(cm.created_at) )  total_order_amount
	FROM chat_message cm
	GROUP BY DATE(cm.created_at), cm.room_id
	ORDER BY DATE(cm.created_at)

Error: ERROR: subquery uses ungrouped column "cm.created_at" from outer query This part of the query causes the error: (SELECT sum(total_amount) FROM "order" o WHERE o.room_id = cm.room_id and DATE(o.created_at) = date(cm.created_at) ) total_order_amount How to solve this?

I have two MySQL queries. The first one returns a list of codes, which comprise either one or two letters (e.g. "AB", "WO", "G"). This is the query: SELECT d.field_id_105 AS code, title FROM exp_channel_titles AS t INNER JOIN exp_channel_data AS d ON (d.entry_id = t.entry_id) WHERE t.channel_id = 18 AND t.status = 'open' AND t.author_id = 123 ORDER BY t.entry_id ASC Then, using a PHP foreach loop, I'm looping through each of those codes and performing a second query on each one, which is obviously incredibly inefficient. The second query varies slightly depending on whether the code comprises one or two letters. If it comprises 2 letters, I simply want to find all records where a specific field starts with those two letters. If it comprises one letter, I want to find all records where a specific field starts with that letter followed by any number from 1 to 9 (e.g. G1 or W8). Here are the two different versions of the second query: If the code comprises two letters SELECT t.title AS title, t.entry_date AS entry_date, t.entry_id AS entry_id, i.title AS installer, i.entry_id AS installer_id, d.entry_id FROM exp_channel_data AS d INNER JOIN exp_relationships AS r ON (r.parent_id = d.entry_id AND r.field_id = 48) INNER JOIN exp_channel_titles AS t ON (d.entry_id = t.entry_id AND t.status = 'open') INNER JOIN exp_channel_titles AS i ON (r.child_id = i.entry_id AND i.status = 'open') WHERE LEFT(field_id_56, 2) = 'AB' AND d.channel_id = 12 If the code comprises one letter: SELECT t.title AS title, t.entry_date AS entry_date, t.entry_id AS entry_id, i.title AS installer, i.entry_id AS installer_id, d.entry_id FROM exp_channel_data AS d INNER JOIN exp_relationships AS r ON (r.parent_id = d.entry_id AND r.field_id = 48) INNER JOIN exp_channel_titles AS t ON (d.entry_id = t.entry_id AND t.status = 'open') INNER JOIN exp_channel_titles AS i ON (r.child_id = i.entry_id AND i.status = 'open') WHERE LEFT(field_id_56, 2) IN ('G1','G2','G3','G4','G5','G6','G7','G8','G9') AND d.channel_id = 12 I've 'hard-coded' the values in the WHERE clauses for demo purposes, but they would actually be populated dynamically by PHP. So essentially what I'd like to do is combine all of these queries into one query. I'm not sure if that's even possible. I got as far as using the first query as a subquery inside the LEFT() function, but I'm getting a little out of my depth at that point and can't work out how to handle appending the numbers to the single-letter codes. Maybe some REGEXP, but I'm not sure. Any help appreciated, thanks.

I wrote a complicated UPDATE query, and it works, but it looks menacing. Here's what I'm trying to do: In each topic user 'Bob123' posted anonymously. When you post anonymously in a topic, you get a unique anonymous index for that topic. Say I want to merge two topics together. Bob123 has a different anon index in both topics, so his unique anon index wouldn't be unique. I only have two pieces of data to work with: $topic_id, the topic id you are merging into, and $post_id_list, all the post ids that got merged over. I want to update all anonymous_index entries per each distinct poster_id's post in that topic. This anonymous_index needs to be the original index they had in the topic before the other topic was merged into it. The first SELECT query first selects the anon indices of the moved posts. The outer SELECT query gets the first non-merged post's anon index (if it is > 0) of those merged posters in the topic and selects a merged anon index from the first query. Then, I update it. Wherever the anon index of those posters in that topic doesn't equal the old index, I update it. Is there something simple that I'm missing here? I don't like the fact that I have a subquery in a subquery. At first I was using HAVING MIN(anonymous_index) MAX(anonymous_index) along with AND post_id NOT IN ($merged_post_list)to select the poster id list that needed to be updated and an unmerged anon index, but it returned 0 rows with this. If the merged post is BEFORE all original posts (and has a larger anon index), then the minimum anon index will match the maximum index for that poster. So making another subquery fixed this...

$merged_post_list = implode(',', $post_id_list);

...

UPDATE " . POSTS_TABLE . " AS p
INNER JOIN (    SELECT p.post_id, p.anonymous_index AS old_index,
                       merged.poster_id, merged.anonymous_index AS new_index
                FROM " . POSTS_TABLE . " AS p,
                (       SELECT poster_id, anonymous_index
                        FROM " . POSTS_TABLE . "
                        WHERE post_id IN ($merged_post_list)
                        AND topic_id = $topic_id
                        AND anonymous_index > 0
                ) AS merged
                WHERE p.post_id NOT IN ($merged_post_list)
                AND p.topic_id = $topic_id
                AND p.anonymous_index > 0
                AND p.poster_id = merged.poster_id
                GROUP BY merged.poster_id
) AS postdata
SET p.anonymous_index = postdata.old_index
WHERE p.topic_id = $topic_id
AND anonymous_index > 0
AND anonymous_index  postdata.old_index
AND p.poster_id = postdata.poster_id

post_id is the primary index, poster_id and topic_id are also indices. Here's some sample behavior: Before merge:

|post_id_____poster_id_____anonymous_index|
| 11         | 3           | 2            |
| 12         | 22          | 1            |
| 14         | 22          | 1            |
| 15         | 3           | 2            |

After merge:

|post_id_____poster_id_____anonymous_index|
| 10         | 22          | 4            |
| 11         | 3           | 2            |
| 12         | 22          | 1            |
| 13         | 3           | 4            |
| 14         | 22          | 1            |
| 15         | 3           | 2            |
| 16         | 22          | 4            |

After UPDATE (the above query):

|post_id_____poster_id_____anonymous_index|
| 10         | 22          | 1            |
| 11         | 3           | 2            |
| 12         | 22          | 1            |
| 13         | 3           | 2            |
| 14         | 22          | 1            |
| 15         | 3           | 2            |
| 16         | 22          | 1            |

EDIT: I've made the following index and an alternative SELECT query to avoid having two subqueries, how would these fare?: (topic_id, poster_id, anonymous_index, post_id)

SELECT merged.poster_id, merged.anonymous_index AS new_index,
	   old.post_id, old.anonymous_index AS old_index
FROM " . POSTS_TABLE . " AS merged,
	 " . POSTS_TABLE . " AS old
WHERE merged.post_id IN ($post_list)
AND merged.anonymous_index > 0
AND merged.anonymous_index  old.anonymous_index
AND old.topic_id = $topic_id
AND old.post_id NOT IN ($post_list)
AND old.anonymous_index > 0
AND old.poster_id = merged.poster_id
GROUP BY merged.poster_id
ORDER BY NULL

EDIT AGIAN: Here is my schema:

Table structure for table phpbb_posts
--

CREATE TABLE phpbb_posts (
  post_id int(10) UNSIGNED NOT NULL,
  topic_id int(10) UNSIGNED NOT NULL DEFAULT '0',
  forum_id mediumint(8) UNSIGNED NOT NULL DEFAULT '0',
  poster_id int(10) UNSIGNED NOT NULL DEFAULT '0',
  icon_id mediumint(8) UNSIGNED NOT NULL DEFAULT '0',
  poster_ip varchar(40) COLLATE utf8_bin NOT NULL DEFAULT '',
  post_time int(11) UNSIGNED NOT NULL DEFAULT '0',
  post_reported tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
  enable_bbcode tinyint(1) UNSIGNED NOT NULL DEFAULT '1',
  enable_smilies tinyint(1) UNSIGNED NOT NULL DEFAULT '1',
  enable_magic_url tinyint(1) UNSIGNED NOT NULL DEFAULT '1',
  enable_sig tinyint(1) UNSIGNED NOT NULL DEFAULT '1',
  post_username varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  post_subject varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  post_text mediumtext COLLATE utf8_bin NOT NULL,
  post_checksum varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  post_attachment tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
  bbcode_bitfield varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  bbcode_uid varchar(8) COLLATE utf8_bin NOT NULL DEFAULT '',
  post_postcount tinyint(1) UNSIGNED NOT NULL DEFAULT '1',
  post_edit_time int(11) UNSIGNED NOT NULL DEFAULT '0',
  post_edit_reason varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  post_edit_user int(10) UNSIGNED NOT NULL DEFAULT '0',
  post_edit_count smallint(4) UNSIGNED NOT NULL DEFAULT '0',
  post_edit_locked tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
  post_visibility tinyint(3) NOT NULL DEFAULT '0',
  post_delete_time int(11) UNSIGNED NOT NULL DEFAULT '0',
  post_delete_reason varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  post_delete_user int(10) UNSIGNED NOT NULL DEFAULT '0',
  sfs_reported tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
  parent_id int(10) UNSIGNED DEFAULT '0',
  post_depth int(3) UNSIGNED NOT NULL DEFAULT '0',
  is_anonymous tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
  anonymous_index mediumint(8) UNSIGNED NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

--
-- Indexes for dumped tables
--

--
-- Indexes for table phpbb_posts
--
ALTER TABLE phpbb_posts
  ADD PRIMARY KEY (post_id),
  ADD KEY forum_id (forum_id),
  ADD KEY topic_id (topic_id),
  ADD KEY poster_ip (poster_ip),
  ADD KEY poster_id (poster_id),
  ADD KEY post_username (post_username),
  ADD KEY tid_post_time (topic_id,post_time),
  ADD KEY post_visibility (post_visibility),
  ADD KEY parent_id (parent_id);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table phpbb_posts
--
ALTER TABLE phpbb_posts
  MODIFY post_id int(10) UNSIGNED NOT NULL AUTO_INCREMENT;COMMIT;

So I have a table of values associated with different sessions using a field called sessionid, lets say the table has 3 other fields: itemcode, itemcost, & itemeffect. What I end up doing, is getting a standard set of items (rows) from another table that contains the defaults for each country, and then store it in this sessiontable with a guid in the sessionid field. The user can then modify the items for their session only. What I want to do instead is to search for an existing session match that has the same items and respective values, to avoid session repetition as the processing later in the application is expensive (reports etc). A match would return the sessionid, otherwise I will stash the new session and items for repeated use. I'm struggling to search for a solution on stackExchange (im working off [this q/a](https://dba.stackexchange.com/questions/72641/checking-whether-two-tables-have-identical-content-in-postgresql) , I've realised EXIST and UNION won't work because I dont know the sessionid and my users session guid wont match (hence the question to begin with). I'm thinking something to do with group by sessionid, and check with the worlds longest sql query (or LINQ madness) with a check for each item and item-field, and addtionally a check that no other (unmatched) items exist in matching sessions, and in theory there will only ever be one match or none! Here's my pseudo-code: useritems = getCountryDefaultItems(); var existingid =( select grpresult from (select item from db.sessionints group by item.sessionid where item in useritems (ignore sessionid) ) g where g.count == useritems.count ) .first().sessionid; I've had a go, and I believe it should be FULL OUTER JOIN so I hear, but sqlfiddle says no so I played with left + right + inner joins :/ sqlfiddle SELECT COUNT(1),a.SessionID FROM sessionitems a RIGHT JOIN useritems b -- should be FULL OUTER JOIN ? USING (SessionID,InterventionCode, Included, IntLvl, Uptake Baseline Smokers, RelativeEffect, Cost, IntType, Baseline, Current, Alternative, UpPopulation, EffPopulation, Name) WHERE (a.InterventionCode IS NULL OR b.InterventionCode IS NULL) AND (a.Included IS NULL OR b.Included IS NULL) AND (a.IntLvl IS NULL OR b.IntLvl IS NULL) AND (a.Uptake Baseline Smokers IS NULL OR b.Uptake Baseline Smokers IS NULL) AND (a.RelativeEffect IS NULL OR b.RelativeEffect IS NULL) AND (a.Cost IS NULL OR b.Cost IS NULL) AND (a.IntType IS NULL OR b.IntType IS NULL) AND (a.Baseline IS NULL OR b.Baseline IS NULL) AND (a.Current IS NULL OR b.Current IS NULL) AND (a.Alternative IS NULL OR b.Alternative IS NULL) AND (a.UpPopulation IS NULL OR b.UpPopulation IS NULL) AND (a.EffPopulation IS NULL OR b.EffPopulation IS NULL) AND (a.Name IS NULL OR b.Name IS NULL) GROUP BY a.SessionID; , but sqlfiddle is having errors doing MSSQL schema today, so I've used mysql for now which doesnt support FULL OUTER JOIN on sqlfiddle: I can get a count of exactly matching rows when there's an exact match, but the id comes back null, and if I remove the COUNT(1) and return * it returns only one row (is this a restriction on results in nested select queries) which might be okay as the session is a match, but I'm worried I have overlooked something and don't appreciate where the query will fall down...

I have two tables. Member information and login entries. I am simply trying to display the member information table sorted by the last login access date. So i created this query, but it is extremely slow. Is there a better way to write it? SELECT menmembers.memb_id, menmembers.firstname, menmembers.lastname, (SELECT Max(trank.datetr) AS MaxOfdatetr FROM trank where trank.cod_usr=menmembers.memb_id;) AS LastLogin FROM menmembers;

As shown on below image, I have two tables; - **Customer** : with about 4 million records; and 3 columns [Customer] : Customer ID [Date] : Creation date [Num_Days] : Number of working days to calculate the [next_wkday] in the query result. - **Calendar** : with all calendar days; and 3 columns [ref_date] : dates [civil_util] : when (1) -> workday; when (0) -> non working day [year] I need to get the **query_result** with the three columns of Customer Table and a calculated date [next_wkday], representing the number of working days [Num_Days] after each customer Creation date, jumping the zeros (*non working days*) in the [civil_util] column. I've created the query below to calculate the [next_wkday] using the Lead() function. But is not a solution because the **offset parameter** must be a constant, and we need to use the [Num_Days] value for each Creation Date: select *, Lead (to_date(ref_date),5) OVER (ORDER BY to_date(ref_date)) AS next_wkday, datediff(Lead (to_date(ref_date),5) OVER (ORDER BY to_date(ref_date)),ref_date) as days_diff from calendar where ref_date >= to_date(now()) and civil_util = 1 limit 1 Basicaly I need to solve two problems: 1 - Must be a query to perform the calculation Because I don't have the profile to create functions in the database. 2 - I need to design a query that achive the ***Query result***, shown in the image, that will able to join both, the Customer table and the calculation query. So, I need to find another solution. And that solution must work in two engines: 1. Impala version : impalad version 2.12.0-cdh5.16.2 2. Oracle 11g I need to reinforce, for performance puposes, that the Customer table has 4 billion records approximatly. Can anyone help please? My best regards

I have just recently learned about user variables (ie, @myvar) and am trying to create a complex sorting query that takes 2 tables where the second is missing a column which then gets filled in by a subquery. In this subquery I need to generate a number by doing a lookup on the first table and modifying the result. More specifically, I have a table A (id, date), and table B (id, weekday) and I need to return a date for each row of B where it is the *next date occurring on the same weekday where such a date does not occur in A*, all starting from a specific minimum date, where entries in table A are all higher (ie, later) than that date. Table B is sorted by staggered weekday (First Monday, First Tuesday... Second Monday, Second Thursday..) and then these get "slotted in" (by assigning "date") where that date in A does not occur. So if the two tables look something like: A id | date a-a 2014-11-11 a-b 2014-11-13 a-c 2014-11-18 B id | weekday b-a Tuesday b-b Tuesday b-c Wednesday Then the resulting sort (if start date is 2014-11-10, a Monday) would be: sorted id | date a-a 2014-11-11 b-c 2014-11-12 (Wednesday) a-b 2014-11-13 a-c 2014-11-18 b-a 2014-11-25 (Tuesday) b-b 2014-12-02 (Tuesday) I have been working on this query for a while now, and almost had it done, but then I couldn't find a way to pass the "start date" to the subquery that allocates dates to table B. The docs say that > Subqueries in the FROM clause cannot be correlated subqueries. They are materialized in whole (evaluated to produce a result set) before evaluating the outer query, so they cannot be evaluated per row of the outer query. So I reimplemented the subquery to move the variable to a WHERE clause, using JOINs, but this still didn't seem to help, since I couldn't figure out how to return just the single row needed without an outer query to do that. So here is what I have, in the hopes someone can help me figure this out: SELECT @next_date as next_date, @tmp_i := CASE w.weekday + 0 WHEN 0 THEN @idxw_U:=@idxw_U+1 WHEN 1 THEN @idxw_M:=@idxw_M+1 WHEN 2 THEN @idxw_T:=@idxw_T+1 WHEN 3 THEN @idxw_W:=@idxw_W+1 WHEN 4 THEN @idxw_R:=@idxw_R+1 WHEN 5 THEN @idxw_F:=@idxw_F+1 WHEN 6 THEN @idxw_S:=@idxw_S+1 END as idxW, @idxw_offset := (7 + w.weekday - dayofweek(@next_date)) mod 7 as idxw_offset, @idxw_corr := (@tmp_i *7)-7 + (7 + w.weekday - dayofweek(@next_date)) mod 7 as idxW_corr, w.weekday as weekday, @dayofweek:= w.weekday+0 as dweekday, @TMP_date:= DATE_ADD(@next_date, INTERVAL (@idxw_corr) DAY) as tmp_date, @clash_offset:= IF((SELECT 1 FROM t_date WHERE date = @TMP_date), IFNULL( ( SELECT next_slot FROM ( SELECT -- DAYOFWEEK(d.date) as weekdaynum, -- dayname(d.date) as weekday, -- d.date, -- MIN(d2.date) as min_d2, DATE_ADD(MIN(d2.date), INTERVAL 1 WEEK) as next_slot, -- DATEDIFF(d2.date, d.date) as datediff, -- DATEDIFF(d2.date, d.date) DIV 7 as weeksdiff, -- DATEDIFF(MIN(d3.date), d2.date) DIV 7 as nextdiff FROM t_date as d JOIN t_date as d2 JOIN t_date as d3 WHERE d.date >= @TMP_date and DAYOFWEEK(@TMP_date) = DAYOFWEEK(d.date) = DAYOFWEEK(d2.date) = DAYOFWEEK(d3.date) and d2.date > d.date and d3.date > d2.date and DATEDIFF(d2.date, d.date) = 7 GROUP BY d.date ORDER BY d.date ASC ) as t -- these 3 lines below are the ones I need to figure out how to accomplish without an outer query -- ie, find the first (lowest date) result that has a nextdiff greater than 1 (a gap of more than 1 week between results) WHERE nextdiff >1 ORDER BY date ASC LIMIT 1 ) , CONCAT('dow: ',@dayofweek,' ',@TMP_date) ) -- , 1) -- commented this out while debugging, debug output is line above; in final, if return is null, that means offset is one week , 0 ) as clash_offset, @NEW_date:= (SELECT DATE_ADD((SELECT @TMP_date), INTERVAL @clash_offset WEEK)) as date, w.extra FROM t_weekday as w JOIN (SELECT @idxw := 0, @idxw_U := 0, @idxw_M := 0, @idxw_T := 0, @idxw_W := 0, @idxw_R := 0, @idxw_F := 0, @idxw_S := 0, @next_date := DATE_ADD((SELECT t.date FROM t as t ORDER BY t.date DESC LIMIT 1), INTERVAL 1 DAY) ) as varrs ORDER BY date DESC, weekday, id ASC @TMP_date is the starting (minimum) date. The subquery is the one inside the IFNULL, and tested outside of this, as its own query, it works perfectly, when specifying a literal value (or setting the variable in a JOIN()). As a test, I did: SELECT @next_date as next_date, @tmp_i := CASE w.weekday + 0 WHEN 0 THEN @idxw_U:=@idxw_U+1 WHEN 1 THEN @idxw_M:=@idxw_M+1 WHEN 2 THEN @idxw_T:=@idxw_T+1 WHEN 3 THEN @idxw_W:=@idxw_W+1 WHEN 4 THEN @idxw_R:=@idxw_R+1 WHEN 5 THEN @idxw_F:=@idxw_F+1 WHEN 6 THEN @idxw_S:=@idxw_S+1 END as idxW, @idxw_offset := (7 + w.weekday - dayofweek(@next_date)) mod 7 as idxw_offset, @idxw_corr := (@tmp_i *7)-7 + (7 + w.weekday - dayofweek(@next_date)) mod 7 as idxW_corr, w.weekday as weekday, @dayofweek:= w.weekday+0 as dweekday, @TMP_date:= DATE_ADD(@next_date, INTERVAL (@idxw_corr) DAY) as tmp_date, @clash_offset:= IF((SELECT 1 FROM t_date WHERE date = @TMP_date), IFNULL(:= IF((SELECT 1 FROM t_date WHERE show_after = @TMP_show_after), IFNULL( ( -- SELECT f FROM ( -- uncommenting this line and the ftmp one results in NULL, with them commented out, @TMP_date is picked up no problem SELECT @TMP_date as f LIMIT 1 -- ) as ftmp ) , CONCAT('dow: ',@dayofweek,' ',@TMP_show_after) ) -- , 1) , 0 ) as clash_offset, @NEW_date:= (SELECT DATE_ADD((SELECT @TMP_date), INTERVAL @clash_offset WEEK)) as date, w.extra FROM t_weekday as w JOIN (SELECT @idxw := 0, @idxw_U := 0, @idxw_M := 0, @idxw_T := 0, @idxw_W := 0, @idxw_R := 0, @idxw_F := 0, @idxw_S := 0, @next_date := DATE_ADD((SELECT t.date FROM t as t ORDER BY t.date DESC LIMIT 1), INTERVAL 1 DAY) ) as varrs ORDER BY date DESC, weekday, id ASC Which confirms what the docs say. However, I don't know how to modify my query (the first one) to get the result I want. ---------- Edit: I found an error in the logic, I need to return the highest result (date) in a series of *nextdiff*s if all return 1, or the first higher-than-1 result. It currently only would return the latter. So that needs fixing too.

This is a distilled version of the query that I am trying to run: SELECT * FROM table1 t1 INNER JOIN (SELECT * FROM table2 WHERE foo = 'bar') t2 ON t1.id = t2.id; The above query is incredibly slow because MYSQL fails to notice that the subquery in the join i.e. SELECT * FROM table2 WHERE foo = 'bar' has id as a primary key which it can use in the join clause. Ofcourse, I should have written this query as: SELECT * FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.id WHERE t2.foo = 'bar'; But, the problem is that the subquery(SELECT * FROM table2 WHERE foo = 'bar') is generated by a library that I do not control. What is the best way for me to optimize this given that I cannot do much about the subquery since I programmatically get it from somewhere else.

I'm trying to incrementally load data from a remote server to a local one (using SSIS and linked server). Remote table has 1.7 million of records, increasing every hour. So far, I have been able to load new records and update existing records using their RECID and LASTMODIFIEDDATEANDTIME fields. But when I try to find records which are deleted since last refresh, I face a never-ending operation: DELETE FROM localdb.dbo.INVENTTRANS WHERE RECID NOT IN (SELECT RECID FROM REMOTESERVER.remotedb.dbo.INVENTTRANS) I tried running SELECT RECID FROM REMOTESERVER.remotedb.dbo.INVENTTRANS and it loads data in less than 10 seconds, hence there is no network/performance issue. But when I run the above DELETE query, it doesn't finish even after 15 minutes. I tried copying RECIDs to a local table to prevent possible reciprocations between local and remote server, no luck. Can someone guide me to improve performance of such a query?

In Postgres I'm trying to automatize the creation of a non-existing db-user (using it in Debian's postinst script) in the following way: SELECT 'CREATE USER :v1' WHERE NOT EXISTS (SELECT FROM pg_user WHERE usename = ':v1')\gexec and calling via psql -v v1="flop" This certainly dosen't work, as :v1 is taken as username string itself and is not substituted. The following error is returned: > LINE 1: CREATE USER :v1 Modifying the line to ... 'CREATE USER ':v1' ' WHERE NOT ... returns: > Syntax error at >>' ' > LINE 1: SELECT 'CREATE USER 'flop' ' WHERE NOT EXISTS (SELECT FROM ... The variable :v1 is substituted, but it seems to me, the whole command, beginning from SELECT, isn't accepted. Changing by adding some single quotes according to some guidelines, to: SELECT 'CREATE USER ':'v1'' ' WHERE NOT EXISTS ... returns > Syntax error at 'flop' > > LINE 1: CREATE USER 'flop' Which looks at me as the substring CREATE USER produces the error due to quotation error. After adding some extra space between the single quotes like SELECT 'CREATE USER ':'v1' ' ' WHERE ... the query returns the following error: > Syntax error at >>' ' > LINE 1: SELECT 'CREATE USER ''flop' ' ' WHERE ... When removing the forst variable :v1 and putting the username explicitly, like SELECT 'CREATE USER flop' WHERE NOT ... everything works together with the variable substitution at usename=. So is seems to me it's "just" a quotation problem. But how cat I remove the single quotes and get the script working?

I'd like to create a view from the query below, but the query contains a sub query which I need to apply a filter to (the place is marked with a comment). I'm wondering if it's possible somehow to adjust the query to have the subquery filtered by those params. The query select form_name, nvl(form_pravo,0) as form_pravo, nvl(fl_pravo,0) as menu_pravo, menu_nazov as menu_name from( SELECT form_name, pravo as form_pravo FROM ( SELECT f.ID as form_id, f.NAME as form_name, f.XF_MODULY_ID as modul_id, u.ID as uziv_id, f.XF_MODULY_ID FROM xf_forms f, xf_uziv u) uf LEFT JOIN xp_forms_uzivs fu ON form_id = fu.XF_FORM_ID AND uziv_id = fu.XF_UZIV_ID where modul_id = 14 and uziv_id = 1 -- <--HERE ) full join ( select SUBOR, FL_PRAVO, menu_nazov, uziv_id, modul_id from xv_uziv_menu ) um on form_name = um.subor where uziv_ID = 1 and modul_id = 14

We have some really strange behaviour on MySQL (8.0.29) that we can't explain. We have 2 tables + 1 link table in-between, illustrated by this schema : We run a query whereby we need to fetch the id and organization_id from table1 for all the records that have a linked record in table2. However, if there are 2 linked records in table2, we still only want a single record from table1. We use Doctrine (PHP) integrated in a complex application so the query is generated for us. The resulting query is :

sql
SELECT
  table1.organization_id,
  table1.id AS id_0
FROM
  table1
  LEFT JOIN table2 ON (
    EXISTS (
      SELECT
        1
      FROM
        link1
      WHERE
        link1.table2_id = table2.uuid
        AND link1.table1_id IN (table1.id)
    )
  )
WHERE
  table1.location_id = 605
  AND table1.status IN ('confirmed')
  and table1.organization_id=1
ORDER BY table1.id DESC
LIMIT
  1000

This query is supposed to return 260 rows, but it returns only 1. Unless we just restarted MySQL, then it returns 0 and will continue to return 0 until we remove either the *LIMIT* clause or the *ORDER BY* clause. On MariaDB it gets worse : the query just uses CPU and we killed it after a few minutes. If you want to give it a go, the data dump is at https://gist.github.com/wimg/c8af87bd30b036c4de5e386e095f6416 Tried it on MySQL 8.0.29 (currently the most recent version). Anyone have any idea what's going on here ?

I have problem, where i know how to proceed with multiple queries, but dont really now I Have a table with BP { name: varchar systolic : int diastolic : int timestamp: date } I need to get the Names from BP table that satisfy the following **ANY** of the following conditions. 1. If systolic > 180 OR diastolic > 110 2. If `(systolic >= 140 AND systolic = 140. Then Condition Satisfied. 3. Same diastolic. If `(diastolic >= 90 AND systolic = 90. Then Condition Satisfied. ### There will be more many rows with the same unique name. (meaning many BP Recordings per person). I can certainly get this working if i jus get all the values and parse it myself in PHP. But Im wondering if theres a better way to do it directly in MySQL. #### My goal here was to find the people that have Hypertension. And those conditions above the guidelines in categorising a person as Hypertensive. In this example, I simplified the table. And I want to get the name(s) of those that meet these hypertensive guidelines. Condition 1 is very simple to address. Condition 2 & 3 are whats troubling me. ### What I do now I just get all those values using SELECT and ORDER BY timestamp ASC, name. And parse everything in PHP I simply get the list of people with systolic >=140 AND systolic = 140 AND systolic < 180, i break enumeration and check the next 2 readings of that name and take the average. I repeat that with diastolic. and Merge the results with all the three conditions, removing any duplicate entries. I really want to learn if there was a good way to do this in sql itself. Im assuming using a Stored Procedure would be a must. But I'd be glad to know if there was a better way using some kind of subquerying.

I have following table for storing item information in PostgreSQL. id | name | category | seller | m_age | status | type_id | region | -------------------------------------------------------------------------- 10 | TBox | Gun | 14 | 4 | Q4 | 12 | EU | 11 | Globe | Gun | 36 | 12 | Q1 | 17 | EU | 12 | Nike | CFK | 12 | 5 | Q2 | 12 | AS | 32 | Raven | Knife | 21 | 14 | Q1 | 12 | EU | 46 | TBox | Gun | 14 | 10 | Q4 | 12 | AS | 46 | Blaster | Gun | 14 | 10 | Q4 | 12 | EU | 78 | PaintGun | Gun | 12 | 10 | Q2 | 54 | US | 98 | TBox | OD | 21 | 4 | Q3 | 54 | AS | I want to find related items on basic of certain criteria. Category > title > Tech Info Matched(m_age, status, type_id, region) > Seller So for id 10 - TBox item rank should be following(name and category column value and Info matched and reason cololms are for visual reference only) id | name | category | INFO MATCHED | RANK | REASON | -------------------------------------------------------------------------------- 10 | TBox | Gun | - | - | - | -------------------------------------------------------------------------------- 11 | Diff | Same | 1 | 3 | same cat diff name 1 info match | 12 | Diff | Diff | 1 | 6 | diff cat diff name 1 info match | 32 | Diff | Diff | 2 | 7 | diff cat diff name 2 info match | 46 | Same | Same | 3 | 1 | same cat and name | 46 | Diff | Same | 4 | 2 | same cat diff name 4 info match | 78 | Diff | Same | 0 | 4 | same cat diff name 0 info match | 98 | Same | Diff | 1 | 5 | diff cat same name | I am still a novice to SQL. I have tried with normal CASE and function. but haven't got proper results. Rank will not work with this many condition. Is there any way to pull this results anyhow with PostgreSQL?

I have the following problem (in PostgreSQL in combination with PostGIS): I have the following nice query who clusters multiple records. Although it uses PostGIS function, my problem is not PostGIS related, so bear with me ;) SELECT count(*), ST_Centroid(ST_Collect("geom")) AS "geom" FROM ( SELECT kmeans(ARRAY[ST_X("geom"), ST_Y("geom")], 5) OVER (), "geom" FROM "table" WHERE "geom" && ST_GeomFromText("Bounding box") --It's this line ) AS ksub GROUP BY kmeans ORDER BY kmeans; Where "Bounding box" is a polygon. This gives me a result. So far so good. I use Geoserver which retrieves the data from the database based on a bounding box. It does so by requesting all the records using the following query: SELECT "count", "geom" FROM "table" WHERE "geom" && ST_GeomFromText("Bounding Box"); Where "Bounding Box" is again a polygon. I'm not able to change the structure of this query. The reason I need Geoserver is because it will convert the results to a different format (GeoJSON). Is it possible to write a view that sends the WHERE parameters (or the value "Bounding box" to the subquery? It's not possible to leave the WHERE statement outside of the subquery because the subquery has to calculate on the bounding box. If you put the WHERE statement outside of the subquery, the subquery will calculate the clusters for all the data and then the WHERE statement will limit it to the bounding box. This is not what I want. Or perhaps somebody knows how to rewrite the query so the WHERE statement from Geoserver will succeed?

I am working on a database with Kickstarter database, and I'm trying to: 1. Define short, medium-lengthed, and long campaigns 2. See how much each length of campaign raise 3. Convert different currencies into USD My original code is as follows: SELECT CASE WHEN DATEDIFF(campaign.deadline,campaign.launched)30 AND DATEDIFF(campaign.deadline,campaign.launched)30 AND DATEDIFF(campaign.deadline,campaign.launched)<=60 THEN 'Mid-lengthed Campaign' ELSE 'Long Campaign' END AS 'Campaign Length', CASE WHEN currency.name='GBP' THEN ROUND(SUM(campaign.pledged)*0.80,2) WHEN currency.name='CAD' THEN ROUND(SUM(campaign.pledged)*1.36) WHEN currency.name='AUD' THEN ROUND(SUM(campaign.pledged)*1.43) WHEN currency.name='NOK' THEN ROUND(SUM(campaign.pledged)*9.28) WHEN currency.name='EUR' THEN ROUND(SUM(campaign.pledged)*0.87) WHEN currency.name='MXN' THEN ROUND(SUM(campaign.pledged)*22.48) WHEN currency.name='SEK' THEN ROUND(SUM(campaign.pledged)*0.04) WHEN currency.name='NZD' THEN ROUND(SUM(campaign.pledged)*1.53) WHEN currency.name='CHF' THEN ROUND(SUM(campaign.pledged)*0.94) WHEN currency.name='DKK' THEN ROUND(SUM(campaign.pledged)*6.52) WHEN currency.name='HKD' THEN ROUND(SUM(campaign.pledged)*7.75) WHEN currency.name='SGD' THEN ROUND(SUM(campaign.pledged)*1.39) WHEN currency.name='JPY' THEN ROUND(SUM(campaign.pledged)*107.11) ELSE ROUND(SUM(campaign.pledged),2) END AS 'Amount Raised' FROM campaign LEFT JOIN currency ON currency.id=campaign.currency_id GROUP BY Campaign Length,currency.id; But the result is instead not grouping as I intended: Campaign Length. ---- Amount Raised Short Campaign. ---- XXXXXXXXXXXXX Mid-Lengthed Campaign ---- XXXXXXXXXXXXX Long Campaign. ---- XXXXXXXXXXXXX Mid-Lengthed Campaign ---- XXXXXXXXXXXXX Mid-Lengthed Campaign ---- XXXXXXXXXXXXX Short Campaign. ---- XXXXXXXXXXXXX I've tried many ways, but couldn't find a solution