New answers tagged join
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
