Hot answers tagged

6 votes
Accepted

MariaDb - How to ensure a (LEFT) JOIN only if a single right side record exists

An alternative way to Lennart's window function answer, is to just use a GROUP BY and HAVING clause against the peoples table to filter out the ones with the same postcode like so: SELECT towns.id, ...
user avatar
  • 24.6k
5 votes

MariaDb - How to ensure a (LEFT) JOIN only if a single right side record exists

You should be able to left join peoples with: (select name, postcode from ( select name, postcode, count(1) over (partition by postcode) as cnt from peoples ) as t where cnt = 1) i.e. SELECT t.id,...
user avatar
  • 21.4k
5 votes

MariaDb - How to ensure a (LEFT) JOIN only if a single right side record exists

Even simpler: SELECT t.id, t.town, IF (COUNT(DISTINCT p.names) = 1, MAX(p.names), NULL) AS names FROM towns AS t LEFT JOIN peoples AS p ON t.postcode = p.postcode GROUP BY t.id ...
user avatar
  • 68.7k
3 votes

Avoiding duplicates when joining multiple tables

Join against the aggregation of each table: select c.*, hdd.hdd_capacity, ram.ram_capacity from computer c join ( select computer_id, sum(computer_hdd_capacity) as hdd_capacity ...
user avatar
3 votes
Accepted

Is it a bad idea to have a "kind" column on a join table?

For the simple example given the answer is meh. Either will work equally well for what's described. Each can be converted to the other using views with a WHERE clause, or a UNION. For a real-world ...
user avatar
3 votes
Accepted

No column name was specified for column 1 of 'd'

You don't need to join to tblTemp again in your sub-query. You can use APPLY operator to make your query more readable. SELECT tp.id as tmpltID, STUFF ( pd.Name, 1, 2, '' ) as colNames FROM [...
user avatar
  • 518
2 votes

Why is this simple query making up 50% execution time?

With the right indexes (you haven't told us what indexes the other table has) you could get this to be efficient by writing the query this way: SELECT whatever FROM "auth_user" where "...
user avatar
  • 32k
2 votes

Why does Postgres not use an index when joining a materialized view?

The horrible estimation of 'transfers' condition (expected 2600, found 1) caused the cost of the hash join and the nested loop to appear falsely similar. Why does misestimating by 2600 fold cause one ...
user avatar
  • 32k
2 votes

MariaDb - How to ensure a (LEFT) JOIN only if a single right side record exists

Look, mum, no subqueries and aggregate or windowing functions! SELECT t.id , t.town , IF( p2.id IS NOT NULL, NULL, p1.names ) AS names FROM towns AS t LEFT JOIN peoples AS p1 ON p1....
user avatar
  • 4,114
2 votes

How can I join 2 tables together, and then count by 2 columns?

You could try the below query, I don't have mySQL, but it is standard SQL that should do the work. SELECT country, category, count(1) FROM users u JOIN submissions s ON u.userid= s.userid GROUP BY ...
user avatar
1 vote
Accepted

How can I correct this mysql join query and put 1 to many in the same row?

You can first take a row-number, then conditionally aggregate (pivot) over that. The joins and grouping/partitioning columns are unclear, you may need to modify. SELECT grh.* d....
user avatar
  • 5,643
1 vote

How can I join 2 tables together, and then count by 2 columns?

You can do the following (all of the code below is available on the fiddle here): CREATE TABLE users ( user_id INTEGER NOT NULL PRIMARY KEY, u_date DATE NOT NULL, country VARCHAR (100) NOT NULL ...
user avatar
1 vote

MySQL - Optimizing a large table's join

Don't have a column for each language, have a row for each. And a column for language. In EXISTS, don't select a column. (The standard pattern is just 1.) Change AND EXISTS (SELECT `r`.`types` FROM ...
user avatar
  • 68.7k
1 vote
Accepted

Getting all shared values of two or more given values in one table (SQL)

Based on the data and expected result I think you are looking for MySQL HAVING clause. Your data: CREATE TABLE test( uid INT, cid int ); insert into test values (1,1), (2,1),(3,1),(2,2),(3,2); ...
user avatar
  • 1,125

Only top scored, non community-wiki answers of a minimum length are eligible