“Faking” a sub-table of results from 3 tables, in SQL
mysql,sql,subquery,nested-lists
Assembling a query result in the exact format you want is .. somewhat of a pain. It can be done, but presentation stuff like that is best left to the application. That said, this will do what you want: select case when case_id > floor(case_id) then '' else case_number end...
Subquery returns more than 1 row, but I want it to
Although you can phrase the query in different way, if you want to compare multiple values, use IN not =: SELECT title FROM song WHERE lineupid IN ( SELECT artistlineupid FROM artistlineup WHERE artistid IN ( SELECT artistid FROM artist WHERE name = "Black Sabbath")); ...
MYSQL Distinct Query on multiple columns
From your comment, use a group by and a min on cfId select min(cfId), foodname, userid, brand from food group by foodname, userid, brand --where userid=234 ...
SQL subquery (stockin quantity runtime by sum up the stock in and stock out group by store and than subtract from stockin to stock out)
sql,switch-statement,subquery,inner-join,correlated-subquery
This (select ISNULL(Items_store.Item_ID,0) from Items_Store where ...) gets you the Item_ID from Items_Store. In case it is null (and I suppose this is never the case) you replace it by 0. In case no record can be found, you get NULL. Replace this by ISNULL((select Items_store.Item_ID from Items_Store where ...),...
How to update multiple columns in the same table with the same sub-query in Oracle SQL
You can update multiple columns in one go: drop table t1; drop table t2; create table t1 (col1 number, col2 number, col3 number); create table t2 (col1 number, col2 number, col3 number); insert into t1 values (1, 10, 100); insert into t1 values (2, 20, 200); insert into t2 values...
Solr subquery merging issue
First of all you have a fq (filterquery clause) inside your query clause (check parenthesis) which is wrong. fq={!geofilt d=40.2335}&pt=9.9312328,76.26730409999999&sfield=latlng You can try things like puting the geofilt filter query OUTSIDE your main query with tests so it will be skipped if... http://www.example.com:8983/solr/collection1/select?rows=10&start=0&wt=json&indent=true&sort=event_start_date asc&q=status:1 AND event_start_date:[2015-04-23T21:38:00Z TO *] AND (tags:5539d77455061a650f96c67e...
Rails 4, ActiveRecord SQL subqueries
sql,ruby-on-rails-4,subquery,select-n-plus-1
Here are two ways of doing it: parent.rb class Parent < ActiveRecord::Base has_many :children # Leaves choice of hitting DB up to Rails def age_of_oldest_child_1 children.max_by(&:age) end # Always hits DB, but avoids instantiating Child objects def age_of_oldest_child_2 Child.where(parent: self).maximum(:age) end end The first method uses the enumerable module's max_by...
SQL-Server. Calculate formula with subquery in aggregate function
sql,sql-server,tsql,subquery,aggregate-functions
I would be inclined to use conditional aggregation to get the values of the variables: select max(case when name = 'B' then quantity end) as B, . . ., max(case when name = 'D1' then quantity end) as D from sample s; You can then incorporate this into an update:...
Oracle ORA:00904: Subquery in LEFT JOIN
sql,oracle,subquery,correlated-subquery
You are trying to do a lateral join. You cannot use an external table alias in the from clause. In general, the work-around is to use aggregation: SELECT ... FROM vehicle_transaction vt INNER JOIN registration_transaction reg ON vt.transaction_id = reg.transaction_id LEFT OUTER JOIN (SELECT laddt2.address_number, MIN(laddt2.address) as address FROM lien_address_transaction...
Rewriting a slow SQL (sub) query in JOIN
Here is a different version of your query where the subquery is converted with a join clause select * from local.advice aa JOIN webdb.account oa ON oa.shortname = aa.shortname join( select max(aa_id) as aa_id,shortname from local.advice group by shortname )x on x.aa_id = aa.aa_id where oa.cat = '111' order by...
Why do these queries return differently?
sql,sql-server,sql-update,subquery,isnull
I suspect you have some duplicate column names between @empInfo and performanceyear and because you are just selecting data from one table, there is no warning. If you use tablename.columnname, you might fix your first query. I would rewrite your query as follows (untested) UPDATE ei SET LastPromotionDate = ISNULL(py.begindate,...
Four (4) Sub Queries
The reason you can do INSERT INTO .... SELECT ... is that the SELECT is being used as the input into the INSERT query. However, an INSERT query doesn't return anything in that way. You are much better off just writing 3 distinct queries like this: --Create user in user...
mysql query retrieve too slow from 10,000 of data (Query Optimizing)
php,join,mysqli,subquery,left-join
ISSUE The page is slowing down (3-5 mins instead of seconds) in mysql queries which has multiple joins and sub-queries to retrieve massive amount of data (~10,000) in tables. SOLUTION - Used Index to the columns Added indexes and done various search queries, It is retrieving better. NOTES Indexes...
Queries to retreive data in MySQL
1. SELECT tripID,SSN,fromCity,tocity,departureDate,returnDate FROM (SELECT Trip.tripID, Trip.SSN, Trip.fromCity, Trip.tocity, Trip.departureDate, Trip.returnDate,sum(Expense.amount) total FROM Trip left join Expense ON Trip.tripID = Expense.tripID GROUP BY Trip.tripID) data WHERE data.total>2000 2. SELECT distinct SSN FROM Trip WHERE fromCity='Chicago' 3. SELECT sum(Expense.amount) total FROM Trip left join Expense ON Trip.tripID = Expense.tripID GROUP BY...
Subquery returns more than one row? Can't write in database
You just can't use INSERT INTO that way. Subquerys are not allowed in this context. But scalar expressions will to the trick: INSERT INTO task (description, subject_id, task_type_id, enddate, priority) SELECT 'desc', A.id_subject, B.id_task_type, '2015-05-05', 2 FROM (SELECT id_subject FROM subject WHERE name = 'Blau') A, (SELECT id_task_type FROM task_type...
How to handle no rows returned in an Oracle update using a common sub-select
oracle,null,sql-update,subquery
I would change the subselect to include a left outer join on t1, and then nvl the results in that case, eg: drop table t1; drop table t2; create table t1 (col1 number, col2 number, col3 number); create table t2 (col1 number, col2 number, col3 number); insert into t1 values...
Select Query returns NULL if subquery fails in the from Clause
use "LEFT OUTER JOIN" as @BaconBits advised and IFNULL: SELECT `aasr`.`account_id`, `aasr`.`sub_account_id`, IFNULL(`tt`.`opening_balance`, `aasr`.`opening_balance`) AS `opening_balance`, IFNULL(`tt`.`closing_balance`, `aasr`.`opening_balance`) AS `closing_balance`, IFNULL(`tt`.`shadow_balance`, `aasr`.`shadow_opening_balance`) AS `shadow_balance`, IFNULL(`tt`.`lein_balance`, `aasr`.`lein_opening_balance`) AS `lein_balance` FROM `accnt_acc_subacc_rel` `aasr` LEFT OUTER JOIN ( SELECT `tti`.`transaction_id`, `tti`.`accounts_id`,...
Oracle sql search rows containing one or more keyword and sum by its keyword
Let's try to break it down, starting from the innermost query and climb up to the outermost query. In your innermost query, the having part is redundant. You will not get any rows if the count(*) is 0 even without it. Having said that, there is no need for group...
MySQL group_concat twice in SubQuery DISTINCT
Use a JOIN to get co-authors: SELECT s1.name, GROUP_CONCAT(DISTINCT s2.name) FROM sample AS s1 JOIN sample AS s2 ON s1.title = s2.title GROUP BY s1.name DEMO If you don't want to show an author as a co-author of himself, add AND s1.name != s2.name to the ON condition....
Mysql: sub-query issue
The problem is that you are doing two sub-queries and then trying to call the values return directly. This will behave as selecting one value from table x and matching it to every single value in table y. This obviously creates the squared return effect. What you need to use...
MySQL Two Table Min Value and Group By
You seem to want the cheapest product for each product name. The query is a bit complicated because the price and name information are separated. One way of writing the query is as: select p.Product_ID, ppd.Product_Name, p.price from product_description pd join products p on pd.product_id = p.product_id join (select pd2.Product_name,...
finding length of longest coulmn in a database - SELECT subquery
I don't have any real knowledge of SQLbase, so I may be off-base here: but if I was trying to do this on SQL Server, a simple approach would be to do something like the following: SELECT tbname, name, 'SELECT ''' + tbname + ''' AS TableName, ''' + name...
Subquery aggregate function with SUM(CASE SUBQUERY)
sql,sql-server,sum,subquery,aggregate-functions
I think it would be better to move the subquery to the from clause. Based on your logic, you are looking for joins, not left joins -- after all, the having clause is simply looking for any match. I think the following query does what you want: SELECT S.id, COUNT(dd.number)...
Condition in WHERE clause (Oracle)
You could join the subquery, i.e. the CTE with the table, and then use the column name in the filter predicate. The result of the subquery in the WITH clause acts like a temporary table. For example, WITH SUBQ AS (SELECT dim.MONTH_NAME AS current_month_name , dim.year_period AS current_month , dim.PERIOD_YEAR...
Subquery with Sum of top X rows
You could use row_number() to get the top five rows, and then group by the other fields: SELECT LastName, FirstName, Class, BibNum, SUM(points) FROM (SELECT LastName, FirstName, Class, BibNum, points, ROW_NUMBER() OVER (PARTITION BY LastName, FirstName, Class, BibNum ORDER BY points DESC) AS rn FROM results WHERE Season='2015' and Mountain='Ski...
Update a table based on another table in PostgreSQL
If a type is attributed to only one product in maximum so: UPDATE types set price = products.price from products where products.type_id= types.id ...
SQL Solving with Subqueries?
Instead of subqueries use another join and add constraints to the join conditions: SELECT E.Name FROM EMPLOYEE E INNER JOIN ASSIGNMENT A ON ( E.ID = A.ID AND A.Country = 'Canada' ) INNER JOIN ASSIGNMENT B ON ( E.ID = B.ID AND B.Country = 'USA' AND B.Start = A.End )...
MYSQL joining subquery issue
I tried to rebuild your schema and create a simple query of what you need. SQLfiddle: http://sqlfiddle.com/#!9/74eb5e/61 Query for getting success and fail: SELECT name AS 'Name', CONCAT( 'Fail: ', CAST(SUM(CASE WHEN (grade1+grade2+grade3)/3 <= 65 THEN 1 ELSE 0 END) AS CHAR(20)), ' Success: ', CAST(SUM(CASE WHEN (grade1+grade2+grade3)/3 > 65...
“SQL command not properly ended” when using subquery
So, to fix the first problem, as I said in the update, in Oracle we shouldn't use the as keyword on aliases. So, the query becomes: select * from (select a.column_value artigo from encomenda e, table(e.artigos) a where e.id = 2) subquery where subquery.artigo is of (menu_t); But after this,...
How to limit a subquery in cypher?
This is probably the best way to do it. I can't unwind the collection or else I'll lose the empty :Thing. MATCH (t:Thing) OPTIONAL MATCH (t)-->(s:SubThing) WITH t, s ORDER BY id(s) RETURN t, collect(s)[0..2] ORDER BY id(t) And it returns this: t collect(s)[0..2] (0:Thing) [(3:SubThing), (4:SubThing)] (1:Thing) [(7:SubThing), (8:SubThing)]...
Retrieving only rows for which a join has a dependancy
I think I understand what you are after now, give this a go: SELECT DISTINCT TestID, TestName FROM `Users` AS u RIGHT JOIN `Users_have_Tests` AS ut ON u.UserID = ut.UserID LEFT JOIN `Tests` AS t ON ut.TestID = t.TestID WHERE TestID NOT IN (SELECT TestID FROM TestResults Where Percentage =...
Selecting from Different Tables, Sub queries or Joins
Per your posted query it looks like there is a relation exists b/w the tables `post`.`post_id` = `comments`.`post_id` So you can try using a INNER JOIN like SELECT c.`comment_id`, p.`post_id`, c.`friendly_url`, c.`heading` FROM `post` p JOIN `comments` c ON p.`post_id` = c.`post_id` WHERE `username` = 'u1' ...
SQL: Get the value of friend column based on current user
I think you can simplify your query to get what you want: SELECT forename, surname FROM entity LEFT JOIN friends ON entity.fb_id = friends.friendA WHERE friendB = :entity_id UNION SELECT forename, surname FROM entity LEFT JOIN friends ON entity.fb_id = friends.friendB WHERE friendA = :entity_id; The WHERE condition is on...
Combining SQL grouped and ungrouped results with a cross join?
Okay - this works: SELECT people.name, COALESCE(PlannedDays, 0) as planned, COALESCE(ActualDays, 0) as actual FROM ( SELECT PeopleName as name FROM Internal_Resources WHERE TaskNo = 100 UNION SELECT DISTINCT UserName as name FROM TimeSheetEntries WHERE TaskNumber = 100 ORDER BY Name) AS people LEFT JOIN ( SELECT PeopleName AS name,...
Using the division operator in a query that gets data from 3 tables
Here's a fiddle You were close: SELECT j.uid, count(j.job_id) , count(distinct j.Input) FROM Protein_info P INNER JOIN Job J ON j.input = p.sequence GROUP BY j.uid HAVING count(distinct j.Input) = (SELECT count(pid) FROM Protein); What you had wrong was: Group by needed to be by uID. Having needed to count...
How to count two separate columns in the same table and sum them into a new column
sql,postgresql,join,count,subquery
I'd use one join to get all the matches a player participated in, and then count a case expression to extract only the ones he won: SELECT playernames.id, name, COUNT(CASE playernames.id WHEN winner THEN 1 ELSE NULL END) AS wins, COUNT(match_id) AS matches FROM playernames LEFT JOIN matches ON playernames.id...
MySQL select and count 2 colums with different where clauses
check this please , would it work for you? please try this SELECT item,quantity, COUNT(case when status =1 then 1 end) AS status1, COUNT(case when status =0 then 1 end) AS status0 FROM table ...
Update only rows where cust_id is present in other table
Since the answer was delivered in a comment, I thought I'd add it as an answer. Adding "IN" instead of "=" solved the issue I had. where cu_number IN (select * from #_t); Thank you, @Jarlh! And thank you to the rest as well for great tips going forward....
Optimizing a sub-sub-query in MySQL
If you want only the most recent comment for a given user, then you can express this as: SELECT rc.* FROM requests r JOIN requests_comments rc ON rc.request_id = r.id WHERE r.username = 'someuser' ORDER BY rc.commented_at DEC LIMIT 1; For performance, you want an index on requests(username, id) and...
SQL query to select the latest records with a distinct subject
sql,sql-server,group-by,subquery,aggregate-functions
You can use ROW_NUMBER(): SELECT id, subject, content, moreContent, modified FROM ( SELECT id, subject, content, moreContent, modified, ROW_NUMBER() OVER (PARTITION BY subject ORDER BY modified DESC) AS rn FROM [CareManagement].[dbo].[Careplans] ) t WHERE rn = 1 rn = 1 will return each record having the latest modified date per...
mysql multiple grouping on one table
mysql,group-by,subquery,group-concat
In MySQL, on a large table, the best approach to getting the last 5 is to use variables to enumerate the results. The rest is just aggregation: SELECT node, SUM(subr > 0) as numfails FROM (SELECT node, jobid, subr, (@rn := if(@n = node, @rn + 1, if(@n := node,...
Declare a BIT variable and set it as a Subquery in SQL Oracle Developer
sql,oracle,variables,boolean,subquery
You need something like the following. First, Oracle doesn't allow @ signs for variable names. It also doesn't have a BIT datatype (although PL/SQL will allow a BOOLEAN). You also can't insert a column value of any type into a BOOLEAN variable. DECLARE HasntPassedCourse BOOLEAN; v_cnt NUMBER; BEGIN HasntPassedCourse :=...
Order by subquery
sql,oracle,subquery,sql-order-by
The ordering is done by results from other table. In this case the query returns only results from employees table, but the ordering is done by department_name, which is stored in departments table. You could achieve identical result by using join, selecting only values from employees table, and ordering by...
Get all matching records where IDs are joined to a comma separated list
You cannot use IN to search for specific value inside comma delimited "string". You can use FIND_IN_SET for this: SELECT FormFields.* FROM Form INNER JOIN FormFields ON FIND_IN_SET(FormFields.id, Form.Fields) > 0 WHERE Form.Id = 2 Result: +------+-------+-----------+ | Id | Name | InputType | +------+-------+-----------+ | 1 | FName |...
Error when using WHERE in Correlated Subquery
sql-server,subquery,where-clause,correlated-subquery
Remove the distinct since your group by and use CASE SELECT A.Name AS BankName, A2.Name as SupplierName, COUNT(C.Id), SUM(CASE WHEN C.ContractStatus = 1 THEN 1 ELSE 0 END) FROM Companies A INNER JOIN Financing F ON F.IdFinancialCompany = A.Id INNER JOIN Contracts C ON F.IdContract = C.Id INNER JOIN Companies...
SQL subqueries ques :
You can create a group for each sailor, and then demand that there are at least 2 distinct colors in that group: select s.sid , s.sname , count(distinct b.color) as NumberOfBoatColorsReserved from Reserves r join Sailors s on s.sid = r.sid join Boats b on b.bid = r.bid group by...
Is is possible to have multiple SQL commands within one VALUES entry?
SQLite is an embedded database; it is designed to be used together with a 'real' programming language, which can do all the logic. Just execute the SELECT first, and then execute the INSERT if needed. If the id column is INTEGER PRIMARY KEY, you can get its value with last_insert_rowid:...
Syntax error in Microsoft Access SQL Select query in VBA procedure
sql,ms-access,access-vba,subquery,select-query
Try changing these lines: "WHERE (DateDiff('d', X.Date, E.Date) >= 30 AND E.Date >= #" & Format(StartingDateTxt, "yyyy\/mm\/dd") & "# and " & _ "E.Date <= #" & Format(endDate, "yyyy\/mm\/dd") & "#) AS T ;", _ ...
Subquery Order by Oracle
sql,oracle,subquery,sql-order-by
Your row_number() analytic call is getting the row number across all companies, so you'll only get a match for the most-recently-changed company; no other company will get r = 1. You need to partition by the company ID: row_number() over (partition by pa.company_id order by pa.last_update desc) r, Or move...
SQL Select Query between 3 tables
Here's one suggestion. Others in the thread have suggested distinct that will result in the same thing, but using an exists predicate clarifies the intention of the query (IMO): SELECT jm.sku , jm.quantity , p.description FROM products as p JOIN job_sku as jm ON p.sku = jm.sku WHERE EXISTS (...
SubQuery execution time : localhost = 2 seconds / server = 98+ seconds
As a general rule, you should avoid using IN when dealing with complex or big subqueries. That's because the IN condition must be evaluated once for each row in the data source. So, if your data source has 1000 rows and the IN condition has 1000 elements, the execution will...
left join with subquery with one result
SELECT k.[mg_KarId] AS [mg_KarId], k.[SymKar] AS [SymKar], k.[OpiKar] AS [OpiKar], k.[Status] AS [Status], ( SELECT TOP 1 kml.SymLok FROM dbo.mg_KarMagLok kml WHERE kml.Mag LIKE 'GLS1' AND kml ON k.SymKar = kmlg.SymKar ) , ( SELECT TOP 1 kml.SymLok FROM dbo.mg_KarMagLok kml WHERE kml.Mag LIKE 'KRS1' AND k.SymKar = kml.SymKar )...
Mysql query using subselects needs to use joins or exists
mysql,sql,performance,join,subquery
Whatever comments you supply to this answer, I will continue with helping try revision for you, but was too much to describe in a comment to your original post. You are looking at services within a given UNIX time range, yet your qualifier on service_id in sub-selects is looking against...
WHERE id = (SELECT parent_id FROM sametable LIMIT 1)
UPDATE `images` SET `featured` = IF(image_id = :image_id, 1, 0) WHERE `gallery_id` = :gallery_id so you simply reset the images for a given gallery into a desired state. The IF(image_id = :image_id, 1, 0) expression returns 1 if the image_id matches the given :image_id or 0 otherwise. One that prefers...
Text search on many-to-many relationship
mysql,full-text-search,subquery,many-to-many
SELECT c.*, cl.colour, (MATCH (cl.colour AGAINST ('blue') + MATCH(c.name) AGAINST ('blue')) AS score FROM cars AS c LEFT JOIN cars_has_colours AS cc ON cc.car_id = c.id LEFT JOIN colours AS cl ON cc.colour_id = cl.id AND MATCH(cl.colour) AGAINST ('blue') WHERE MATCH (c.name) AGAINST ('blue') OR cl.id IS NOT NULL ORDER...
Problems With FOR XML AUTO
sql,asp.net,sql-server,subquery,sqlxml
Change XML PATH('') to XML PATH('tag')
Mysql nested match against not returning any results
php,mysql,select,subquery,match-against
The indexing performance on your current method is going to be exceptionally poor. Instead, create a temporary table, index that, and full text against that. CREATE TEMPORARY TABLE `temp_movie` SELECT * FROM Movie WHERE MATCH(keywords) AGAINST('$mk'); ALTER TABLE `temp_movie` ADD FULLTEXT INDEX(genres); SELECT * FROM `temp_movie` WHERE MATCH(genres) AGAINST('$mg') And...
SQL Select within Sub Query
sql-server,select,subquery,select-case
Are you actually looking for this? ALTER PROCEDURE [dbo].[POBalance] @PONumber NVARCHAR(50) AS BEGIN DECLARE @Status NVARCHAR(MAX) SELECT @Status = X.STATUS FROM tblPOHeader AS X WHEREx.PONo = @PONumber IF @Status = 'False' BEGIN SELECT A.Description ,C.qty AS POqty ,B.Qty AS PDQty ,CASE WHEN A.partialflag = 'false' THEN '0' ELSE A.qty END...
Mysql count and return just one row of data
mysql,join,count,subquery,having
Please give this query a shoot SELECT COUNT(DISTINCT(u.id)) AS users_count FROM users AS u INNER JOIN ( SELECT user_id, COUNT(DISTINCT profile_option_id) AS total FROM profile_answers WHERE profile_option_id IN (37,86,102) GROUP BY users.id HAVING COUNT(DISTINCT profile_option_id) = 3 ) AS a ON a.user_id = u.id If you have lots of data...
Select Last Record from LEFT JOIN Subquery
First, you're missing customerid as a field you are selecting in your subquery - that's why you are getting your error. Second, even if you add that in, you're not going to get the results you want. I assume that you have a customer, a customer can have more than...
In MySQL, get number of pregnant women who received 3 ANC checkups
mysql,stored-procedures,subquery
In MySQL, a boolean operation is treated as a number in a numeric context, with 1 for true and 0 for false. This makes it easy to count the number of non-NULL values: select count(*) from table t where ((visit1_date is not null) + (visit2_date is not null) + (visit3_date...
Correlated subqueries: How to get the ID of the outer query?
sql,sum,subquery,correlated-subquery
AFAIK you can't do a correlation into a Derived Table. But you can rewrite your query to a non-correlated subquery: SELECT People.IDPerson, People.NAME FROM ( -- Selects people that never made a sticker transaction SELECT p.IDPerson, p.NAME FROM Person p LEFT JOIN StickerTransaction sT ON p.IDPerson = sT.IDPerson WHERE sT.IDPerson...
MySQL Subquery related to LEFT JOINS
mysql,subquery,correlated-subquery
Try this: SELECT customer.Name, address.Street, address.City, address.State, address.Zip, customer.Contact, email.Address, customer.CTYPE, GROUP_CONCAT(telephone.Number) FROM `customer` LEFT JOIN customeraddress ON customer.CustomerID = customeraddress.CustomerID LEFT JOIN address ON customeraddress.AddressID = address.AddressID LEFT JOIN customeremail ON customer.CustomerID = customeremail.CustomerID LEFT JOIN email ON customeremail.EmailID = email.EMailID LEFT JOIN customertelephone ON customer.CustomerID = customertelephone.CustomerID LEFT...
How to optimize or speed up my subquery in mysql or in php
php,mysql,sql,subquery,query-optimization
Here is a modified version of your query select t1.t1_id, t1.name, t2.address, t3.message from table1 t1 join table2 t2 on t1.t1_id = t2.t2_id join table3 t3 on t3.logid = t1.t1_id join( select max(logs) as logs,logid from table3 where message NOT LIKE "[ SYSTEM%" group by logid )x on x.logs =...
Do not include select columns in group by
sql,postgresql,group-by,subquery
You can achieve this with a window function which doesn't require everything to be grouped: select * from ( select addresses.phone, addresses.name, orders.order_number, count(orders.order_number) over (partition by addresses.phone) as cnt from orders inner join carts on orders.order_number = carts.id inner join address on carts.address_id = addresses.id ) t where cnt...
First query data is used in second query
sql,oracle,subquery,common-table-expression,inline-view
You could use the WITH clause. For example, WITH get_product AS (SELECT A.product_id, A.name, A.description, A.type_id, B.series_name product_data A INNER JOIN series B ON A.series_raw_id = B.series_raw_id WHERE A.product_id = 503061 AND A.registration_type_id = 4 ORDER BY B.series_name ) SELECT B.series_name, A.TEMPACC_STATUS FROM ACCESS_FACT A INNER JOIN get_product B ON...
Count rows after specific one
The straight-forward way is to have two derived tables: One to get the last login date per user, the other to get the last successful login date per user. Then select from users, outer join the two derived tables and look whether the last login was successful and count the...
MySQL Query - Lowest Values with Multiple Tables / Joins
This can be accomplished using some simple aggregation. As long as you are able to properly join all of your tables, you can do this: SELECT player, course, MIN(score) AS lowestScore FROM myTables GROUP BY player, course; ...
SQL Query : Cannot create view due to error #1349 : Help to restructure query
mysql,subquery,mysql-error-1349
A view in MySQL cannot handle subqueries in the from clause. So, you will need somewhat different logic. This version should work: SELECT c.*, cl.completed_on as last_completed_on, cl.completed_by FROM checks c INNER JOIN checks_log cl ON c.check_id = cl.check_id WHERE cl.completed_on = (SELECT MAX(cl2.completed_on) FROM checks_log cl2 WHERE cl2.check_id =...
SQL WHERE Subquery in Field List
You can do this if you use Sql Server: SELECT field, ca2.c2, ca3.c3 FROM table t cross apply(SELECT COUNT(*) c2 FROM table2 t2 WHERE t2.field = t.field)ca2 cross apply(SELECT COUNT(*) c3 FROM table3 t3 WHERE t3.field = t.field)ca3 where ca2.c2 <> ca1.c1 ...
SQL - Select equal numbers of result of each IN parameter
Using the union approach you can use something like (shown here for two categories) SELECT * FROM ( SELECT id FROM game_data WHERE category_id = :category_id1 LIMIT :size UNION ALL SELECT id FROM game_data WHERE category_id = :category_id2 LIMIT :size ) q1 ORDER BY rand() where :size is the number...
Is there a more efficient way to execute this nested SQL query?
sql,ms-access,odbc,subquery,correlated-subquery
You may need not need any subqueries but possibly a self-join: SELECT counties.id, counties.county_name, members_1.county FROM counties INNER JOIN members ON counties.state = members.state LEFT JOIN members_1 ON counties.id = members_1.county WHERE members.id = 53 AND members_1.id = 53 ...
MySQL implode subquery result and use in WHERE IN
use FIND_IN_SET () SELECT `name` FROM `users` WHERE FIND_IN_SET (`id`,( SELECT `manager` FROM `users_info` WHERE `id_user`='1' )) >0; ...
MySQL statement in SQLAlchemy
Subqueries are what you are looking for. http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html#using-subqueries...
MySQL SELECT to subquery a many-to-many relationship
if I understand you correctly you want to select all values from table partofspeech based on lemma table. Your query should look like this: SELECT part.partofspeech FROM partofspeech part INNER JOIN postolemma post ON part.posID = post.posID_p INNER JOIN lemma l ON post.lemmaID_p = l.lemmaID Also I would suggest you...
Subquery between three tables using Northwind database with SQL Server
sql,sql-server,subquery,northwind
The following query will return to you the productid with maximum discount for each customer. Please note that if for specific customer, you have more than one product that might have the max discount, I you want to return them, then you need to replace ROW_NUMBER() with DENSE_RANK() WITH CTE...
Laravel - named subquery using Query Builder
php,mysql,laravel,subquery,query-builder
Unfortunatelly no. The Query Builder has its limitations and more complex queries are outside its scope, that's why DB::raw() is there. But, if you want to make it a little more elegant and generate the subquery using the Query Builder, you could do something like this this: $subquery = DB::table('items')->select('id',...
MYSQL LIMIT on COUNT of DISTINCT row
This query may return the result you specified. The inline view t2 returns the maximum Season for each Series. The inline view t3 returns the maximum Episode for each Season of each Series. The results from the inline views (derived tables) can be joined to the original table, to retrieve...
Erroneous query works when used as subquery - how? Why?
sql,sql-server,select,subquery,sql-server-2000
Always use table aliases, especially with correlated subqueries. You think your query is: SELECT t1.* FROM MY_DB.dbo.MY_TABLE1 t1 WHERE t1.MY_PROBLEMATIC_COLUMN IN ( SELECT t2.MY_PROBLEMATIC_COLUMN FROM MY_DB.dbo.MY_TABLE2 t2 ) But, because t2.MY_PROBLEMATIC_COLUMN does not exist, SQL avoids an error and looks for a column in an outer scope. The query is...
Using result in the outer query in the inner query
Try this query with GROUP BYclause SELECT DISTRICTNAME, MIN(`ID`) AS 'MIN', MAX(`ID`) AS 'MAX' FROM [DATABASE_NAME] WHERE [CONDITION] GROUP BY DISTRICTNAME ORDER BY DISTRICTNAME;; ...
trouble with creating subquery
mysql,sql,subquery,mysql-workbench
kinda like this...? mysql> select * -> from employee e -> where e.employee_salary > ( -> select avg(employee_salary) avg -> from employee ee -> -- this is how you join them without a join... -> where ee.department_id=e.department_id -> ); +-------------+---------------+---------------+-----------------+ | employee_id | department_id | employee_name | employee_salary | +-------------+---------------+---------------+-----------------+...
Complex Sql Select statement with latest data
sql,sql-server,select,subquery
You could do a JOIN on DiscountTransactions.TransactionId = Transactions.OriginalId and use ROW_NUMBER() to get the latest DiscountPercent: ;WITH Cte AS( SELECT t.*, dt.DiscountPercent, RN = ROW_NUMBER() OVER(ORDER BY dt.Date DESC, RunId DESC) FROM Transactions t LEFT JOIN DiscountTransactions dt ON dt.TransactionId = t.OriginalId WHERE t.Date = CAST('20150102' AS DATE) AND...
Error in query (1054): Unknown column 'TableValue' in 'where clause'
You can't use the alias name in where clause You need to either use subquery or having clause SELECT *, ( SELECT `TableName` FROM `TableNames` WHERE `TableID`=`IndexType` ) AS `IndexTypeName`, CASE WHEN `IndexType`=1 THEN ( SELECT `Username` FROM `Users` WHERE `IndexRowID`=`UserID` ) WHEN `IndexType`=2 THEN ( SELECT `MessageContent` FROM `Messages`...
Optimize MS Access Double SubQuery
sql,ms-access,subquery,ms-access-2010
MsAccess is supposed to be slower on NOT IN than on NOT EXISTS. I don't know whether this is true, but you can try anyway. Moreover you can move this restriction from your WHERE clause to a HAVING clause, as vin is in the GROUP BY clause. This may reduce...
SQL find references with single occurences
sql,group-by,subquery,aggregate
First, filter out all brushes. This can be done in the where clause. Then you need to process each order as a bunch of records. This can be achieved with group by OrderNumber, Product_Type which breaks your table into order groups. Then you can filter these groups in the having...
SQL Reference Large Equation in Select Statement
mysql,sql,sql-server,performance,subquery
What happens when you filter the rows in outer query or CTE. With CTE as ( SELECT 'HeaderDetail' AS rowType, ... , [large CASE/WHEN statement] AS Tracking ... ) select * from CTE WHERE NOT(@DNC IN(Tracking)) AND Tracking IS NOT NULL or use Sub-Select select * from ( SELECT 'HeaderDetail'...
How to use subquery table alias in WHERE clause in MySQL
You can't calculate max for the previously calculated alias in sub query alternatively you can rewrite your query as SELECT a.invoice_id, SUM(a.amount) AS sum_amount, c.max_amount FROM invoice AS a CROSS JOIN ( SELECT MAX(sum_amount) max_amount FROM( SELECT invoice_id, SUM(amount) AS sum_amount FROM invoice GROUP BY invoice_id ) b ) c...
MySQL SQL Select Query with Multiple Sub-Selects
select s.*, CASE WHEN b_id.brandName IS NOT NULL THEN b_id.brandName WHEN b.brandName IS NOT NULL THEN b.brandName ELSE s.brand END as bname from service s LEFT JOIN brand b_id ON CAST(s.brand AS UNSIGNED) = b_id.id LEFT JOIN brand b ON s.brand = b.brandName ...
using Not in in SubQuery with not equal to
Table ClientList Table employee Id Id Status -- -- ------ 1 1 Y 2 2 N 3 3 null 4 First query counts id's: 2, 3, 4 - rows which are in ClientList and are not in employee and marked as Y. Second query: id 2 - shows rows...
NESTED SELECT QUERY WITH CASE
sql,oracle,select,subquery,case
Your problem is in the double nesting. Why don't you change: (SELECT TARIFF FROM ( SELECT * FROM A_TARIFF WHERE TRIM(A_TARIFF.FROM_LOCATION) = TRIM(**FROM_LOCATION**) AND TRIM(A_TARIFF.TO_LOCATION) = TRIM(**TO_LOCATION**) ORDER BY A_TARIFF.FROM_DATE DESC ) WHERE ROWNUM = 1) with SELECT TARIFF FROM A_TARIFF WHERE TRIM(A_TARIFF.FROM_LOCATION) = TRIM(B_TARIFFS.FROM_LOCATION) AND TRIM(A_TARIFF.TO_LOCATION) = TRIM(B_TARIFFS.TO_LOCATION) WHERE...
mysql subquery to sum like checknumbers
You could do the calculation in a derived table. The query should be self-explanatory. Select i.id, i.CheckNumber, i2.AmountPaid from HS i join ( select checknumber, sum(amountpaid) AmountPaid from HS group by checknumber ) i2 on i2.checkNumber = i.checkNumber where i.id in ( SELECT id FROM HS_dups WHERE concatckBatch IN (...
Nested Join in Subquery and failing correlation
Got it working with NOT IN subselect in the on clause. Whereas the correlation link u.id is not given within the LEFT JOIN scope, it is for the scope of the ON clause. Here is how it works: SELECT u.id, sub.idcube FROM sc_user as u LEFT JOIN ( SELECT idcube...
Select multiple value using subquery
You can try something like this: select a.name, b.TagName from article_articleTags_Rel c inner join articles a on a.ID = c.ArticleID_FK left outer join articleTags b on b.ID = c.TagID_FK Result: name TagName --------------- art A tag 1 art A tag 2 art A tag 3 art B tag 1 art...
SQL server Temp table with joins inside other select
sql-server,subquery,union,temp-tables,sql-server-2014-express
Actually you can do it without temp-table: WITH myCTE [ ( column_name [,...n] ) ] AS ( here you define your query ) and after that you just do your Select but use CTE Select ... from myCTE join .... where.... about CTE you can read Here After Update Select...
Selecting Account Numbers with latest date
Just use row_number(), if I understand correctly: select s.* from (select s.*, row_number() over (partition by customer, bankId order by date desc) as seqnum from stat s ) s where seqnum = 1; ...
PostgresSQL database has date field, I want all data but the date column as year only
I think you are overthinking this one: SELECT EXTRACT(YEAR from datefield), The, Other, Fields, You, Want FROM tableName ...