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, ...
- 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,...
- 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
...
- 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
...
- 71.2k
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 ...
- 23k
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 [...
- 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 "...
- 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 ...
- 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....
- 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 ...
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....
- 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
...
- 27.2k
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 ...
- 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);
...
- 1,125
Only top scored, non community-wiki answers of a minimum length are eligible