New answers tagged

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,...
user avatar
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 ...
user avatar
  • 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)...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 <...
user avatar
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 ...
user avatar
  • 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....
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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, ...
user avatar
  • 465

Top 50 recent answers are included