New answers tagged join
1
vote
Drop the constant recurring JOINs in SQL queries on a SQL-Server, is this possible?
I'm afraid that there's no NATURAL JOIN or any other language element so you don't have to specify the join condition in MSSQL. (I wish that exists...)
As an aside, if you stick to the old join syntax,...
- 13.9k
1
vote
Getting 502 on join eloquent laravel
Add these composite indexes:
ad_camp_sid: INDEX(sid, cid)
ad_dsp_sites: INDEX(exchange, site_id)
When adding a composite index, DROP index(es) with the same leading columns.
That is, when you have ...
- 68.1k
-1
votes
How to Join two tables with SUM and GROUP BY in SQL server
SELECT Designation, SUM(Qty)
FROM Product P
JOIN sub_product Q
ON Q.Product_ID = P.id
GROUP BY Designation
ORDER BY Designation
? downvoted? LOL
DECLARE @Product TABLE (ID INT, Designation VARCHAR(10)...
- 333
0
votes
Accepted
How to optimized self-join view of 50 millions rows table
The parts of an SQL statement are processed in a certain order (at least logically). In the case at hand, the WHERE condition is evaluated before a window function like row_number(). Your view ...
- 32.1k
3
votes
Accepted
How to Join two tables with SUM and GROUP BY in SQL server
This can be accomplished simply with a JOIN and GROUP_BY clause like so:
SELECT
p.designation,
SUM(ISNULL(sp.quantity, 0)) AS [total quantity]
FROM product AS p
LEFT JOIN sub_product AS sp
...
- 24k
3
votes
Can I write a FULL OUTER JOIN without OR IS NULL?
Another option:
SELECT *
FROM a
FULL JOIN b
ON a.a_id = b.b_id
WHERE EXISTS
(
SELECT 'blah'
INTERSECT
VALUES (a.a_prop), (b.b_prop)
);
a_id
a_prop
b_id
b_prop
1
blah
1
blah
2
blah
...
- 69.1k
5
votes
Accepted
Can I write a FULL OUTER JOIN without OR IS NULL?
without OR IS NULL?
col = 'x' OR col IS NULL
Original:
SELECT *
FROM a
FULL JOIN b ON a_id = b_id
WHERE (a_prop = 'blah' OR a_prop IS NULL)
AND (b_prop = 'blah' OR b_prop IS NULL);
Use col <...
- 150k
0
votes
Best practices for large JOINs - Warehouse or External Compute (e.g. Spark)
I am not sure whether exporting such amount of data would be faster/cheaper.
Instead of self-join I would try sorting or grouping:
GROUP by name
HAVING COUNT(*) > 1
OR
WITH cte as (
select ...
- 308
0
votes
Simple query with a single join very slow
If you were to upgrade to at least MySQL 8.0, you'd be able to take advantage of lateral joins:
create index order_ordered on order (company_id, id desc);
SELECT
lateral_o.id,
lateral_o....
- 2,289
3
votes
Accepted
child to parent in same table - there must be a better way to do this
What you're looking for is a recursive query:
with recursive nodes(id, path) as (
-- selecting the parent/root (anchor)
select r.id, r.name
from mytable r
where r.id = <choose starting ...
- 934
1
vote
Accepted
Select a row using a foreign key in another table
Firstly (although it doesn's apply in this case), you should always include your version of PostgreSQL with any questions here.
What you are referring to is an [INNER] JOIN. The different types of ...
2
votes
Accepted
mssql - query with results from same table and association table
Using inner join, starts from the Parts table and self join to get the Parent's information.
select e.EquipmentNumber,
p.PartNumber,
ChildPartNumber = c.PartNumber,
c.CTypeId,
...
- 465
Top 50 recent answers are included