New answers tagged

0

You can push the WHERE predicates into the joins, and check that at least one of the joins match: SELECT purchase.id FROM purchase LEFT JOIN user on purchase.buyer = user.id LEFT JOIN user_documents on user.id = user_documents.user and user_documents.value = '123' LEFT JOIN buyer_info on purchase.id = buyer_info.purchase and ...


7

I would add OPTION(USE HINT 'DISABLE_OPTIMIZER_ROWGOAL') to the query to disable row goals. The easiest way to spot the use of row goals is when using the TOP() operator, used to return 45 rows as quickly as possible in your case. The use of loop joins is more common here as it is estimating smaller result sets & using non blocking operators. Row ...


1

If you select from the results of a union all you will be able to filter the results you are looking for in a speedy fashion and only have to have a single where clause. SELECT info.id FROM (SELECT purchase.id FROM purchase LEFT OUTER JOIN user ON purchase.buyer = user.id LEFT OUTER JOIN user_documents ON user.id = user_documents.user ...


0

you can also use cte's: with definitely_better as (SELECT id FROM account WHERE login_id = ${loginId}) SELECT t.id, t.date, t.amount, t.description FROM transaction t INNER JOIN definitely better ac ON t.account = ac.id WHERE t.processed = false AND t.updated_by_user = false AND t.category = 'uncategorized' ;


0

Bingo, I figured it out but will accept @Joe W et. al. for getting me there. SELECT m.module, m.content FROM `modules` m LEFT JOIN contents c ON m.id = c.module_id LEFT JOIN languages l ON c.language_id = l.id WHERE l.tag = 'en-US' OR l.tag IS NULL Without the OR l.tag IS NULL I was still pulling in rows from non-US languages as well. I don't know why it ...


1

I would go with something like this that would include all records from the modules table which would include records where it doesn't match up in the contents/languages table and limit it to the language you want. SELECT m.module, m.content FROM `modules` m LEFT JOIN contents c ON m.id = c.module_id LEFT JOIN languages l ON c.language_id = l.id WHERE l....


1

It's pretty obvious. You JOIN to languages filter out the null rows because there's no matching to the languages table. The combination of RIGHT JOIN and LEFT JOIN is bad, but it should work. SELECT m.module, m.content FROM `modules` m LEFT JOIN contents c ON m.id = c.module_id LEFT JOIN languages l ON c.language_id = l.id WHERE l.tag =...


-2

You should use TRY_CONVERT instead of CASE WHEN ISNUMERIC ... THEN CONVERT( How does this condition work? AND cat1.clave = '101' AND cat1.clave IN ('01', '02','03', '04', '05') What is your problem? Slow delete or slow select? Slow select cannot be improved due to type casting in WHERE Why are you doing "heavy duty joins and aggregate query twice"?


0

Check SELECT tae.id, taw.MaxDate, taw.id workid FROM tbl_agro_exploatare tae LEFT JOIN ( SELECT MAX(id) id, exploatare_id, MAX(tarih) MaxDate FROM tbl_agro_works GROUP BY exploatare_id ) taw ON taw.exploatare_id = tae.id ORDER BY tae.id fiddle Is that what you need?...


1

SELECT d.id deviceid, u.id userid, dp.preferenceid FROM Users u, Devices d, Device_Preference dp WHERE u.id = dp.userid AND u.id = d.userid ORDER BY 1,3 fiddle


1

You did not specify which RDBMS (SQL Server, Oracle, etc.) you are targeting for this question, but here is a SQL Server solution which you could probably adapt. --demo setup set nocount on; DROP TABLE IF EXISTS dbo.Invoice; DROP TABLE IF EXISTS dbo.Allocation; CREATE TABLE dbo.Invoice (Invoice_Id int, Allocated_Invoice_Amount decimal(11,2)); CREATE TABLE ...


0

SELECT NEWS.news_uid, NEWSREAD.news_read FROM NEWS LEFT JOIN NEWSREAD ON NEWS.uid = NEWSREAD.news_uid AND NEWSREAD.user_uid = 'A'


0

Your second select query does what you want ... - deliver all imageupload entries - together with who took it and where / when - and who marked it favorite ... I suggest you put a proper alias for favorites.uid to make it more prominent http://sqlfiddle.com/#!9/b04a03/4 SELECT CASE WHEN favorites.uid=1 THEN 1 ELSE 0 END AS `my_favorite`,...


0

Is there a difference if I put the where condition at the join section? In your case, NO. Optimizer is smart enough to understand. Where Clause further put filter on what is produce by JOIN. INNER JOIN :Here WHERE clause do not have much to to put filter.It has smaller resultset to apply filter. In fact, in case of, INNER JOIN, it do not matter where ...


2

My solution does only 1 scan of #TempBucket but it adds an additional sort, I'm not sure it's more efficient, you should test it on volumes of data: ;with cte as ( select t.*, tb.id as BucketId, row_number() over(partition by t.id order by tb.FromVal) as rn from #TempData t join #TempBucket tb on (t.value = tb.filter) or (tb....


Top 50 recent answers are included