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...
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 ...
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-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...
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 )...
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...
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....
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...
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...
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...
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,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 :=...
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...
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,duplicates,subquery,case,distinct
You need to add the outer select for current query with the Group on the the columsn you want to do the aggrigate.Just add the Outer select to your query, SELECT data.id, data.lastname, data.firstname, SUM(data.WebData) AS WebData, SUM(data.InternalData) AS InternalData, SUM(data.countid) AS Countid FROM ( SELECT id,lastname, firstname,datasource, CASE WHEN...
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...
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...
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...
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...
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...
Subqueries are what you are looking for. http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html#using-subqueries...
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...
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...
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`...
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,...
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 ;", _ ...
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...
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...
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,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 | +-------------+---------------+---------------+-----------------+...
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...
use FIND_IN_SET () SELECT `name` FROM `users` WHERE FIND_IN_SET (`id`,( SELECT `manager` FROM `users_info` WHERE `id_user`='1' )) >0; ...
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...
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...
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 ...
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,...
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,...
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)]...
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...
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,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...
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`,...
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 ...),...
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....
I think you are overthinking this one: SELECT EXTRACT(YEAR from datefield), The, Other, Fields, You, Want FROM tableName ...
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...
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'...
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...
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 )...
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...
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...
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 ...
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 =...
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...
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...
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...
i think i found a solution... INSERT INTO table2 (value1_asd,value2_asd,value1_qwe,value_2_qwe,test_id) SELECT T1.v1,T1.v2,T2.v1,T2.v2,T2.testid FROM (SELECT AVG(`value1`) as v1, AVG(`value2`) as v2, `channel`, `testid` FROM table1 WHERE `channel` = 'asd' GROUP BY `testid`) AS T1, (SELECT AVG(`value1`) as v1, AVG(`value2`) as v2, `channel`, `testid` FROM table1 WHERE `channel` = 'qwe' GROUP BY...
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...
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...
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',...
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 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...
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...
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...
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")); ...
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...
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...
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; ...
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...
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:...
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; ...
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 (...
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...
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 |...
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 ...
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...
sql,asp.net,sql-server,subquery,sqlxml
Change XML PATH('') to XML PATH('tag')
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:...
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,...
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...
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...
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,...
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' ...
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...
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 ...
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...
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...
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...
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 =...
mysql,sql,select,subquery,left-join
OK, this is your current schema. As you can see, one match can be played on multiple surfaces and one match can be played within multiple tournament types. If this schema is OK, you can get your result with this query: SELECT am.*, asu.surface_name, att.tournament_type_name FROM atp_matchs AS am LEFT...
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...