Multiple Self-Join based on GROUP BY results
You apparently have two intermediate inner join
output tables and you want to get columns from each about some things identified by a common key. So inner join
them on the key.
select
g.client_name,
g.dataset,
g.plugin_name,
LastGood,
g.status_code,
LastGood_bytes
LastAttempt,
l.status_code,
LastAttempt_bytes
from
( -- cut & pasted Last Good http://sqlfiddle.com/#!15/f15556/16
select
a2.client_name,
a2.dataset,
a2.plugin_name,
a2.LastGood,
a3.status_code,
a3.bytes_modified as LastGood_bytes
from v_activities_2 a3
join (
select
client_name,
dataset,
plugin_name,
max(completed_ts) as LastGood
from v_activities_2 a2
where
type like '%Backup%'
and status_code in (30000,30005) -- Successful (Good) Status codes
group by
client_name, dataset, plugin_name
) as a2
on a3.client_name = a2.client_name and
a3.dataset = a2.dataset and
a3.plugin_name = a2.plugin_name and
a3.completed_ts = a2.LastGood
) as g
join
( -- cut & pasted Last Attempt http://sqlfiddle.com/#!15/f15556/3
select
a1.client_name,
a1.dataset,
a1.plugin_name,
a1.LastAttempt,
a3.status_code,
a3.bytes_modified as LastAttempt_bytes
from v_activities_2 a3
join (
select
client_name,
dataset,
plugin_name,
max(completed_ts) as LastAttempt
from v_activities_2 a2
where
type like '%Backup%'
group by
client_name, dataset, plugin_name
) as a1
on a3.client_name = a1.client_name and
a3.dataset = a1.dataset and
a3.plugin_name = a1.plugin_name and
a3.completed_ts = a1.LastAttempt
) as l
on l.client_name = g.client_name and
l.dataset = g.dataset and
l.plugin_name = g.plugin_name
order by client_name, dataset, plugin_name
This uses one of the applicable approaches in Strange duplicate behavior from GROUP_CONCAT of two LEFT JOINs of GROUP_BYs. However the correspondence of chunks of code might not be so clear. Its intermediate are left
vs your inner
& group_concat
is your max
. (But it has more approaches because of particulars of group_concat
& its query.)
A correct symmetrical INNER JOIN approach: LEFT JOIN q1 & q2--1:many--then GROUP BY & GROUP_CONCAT (which is what your first query did); then separately similarly LEFT JOIN q1 & q3--1:many--then GROUP BY & GROUP_CONCAT; then INNER JOIN the two results ON user_id--1:1.
A correct cumulative LEFT JOIN approach: JOIN q1 & q2--1:many--then GROUP BY & GROUP_CONCAT; then left join that & q3--1:many--then GROUP BY & GROUP_CONCAT.
Whether this actually serves your purpose in general depends on your actual specification and constraints. Even if the two join
s you link are what you want you need to explain exactly what you mean by "merge". You don't say what you want if the join
s have different sets of values for the grouped columns. Force yourself to use the English language to say what rows go in the result based on what rows are in the input.
PS 1 You have undocumented/undeclared/unenforced constraints. Please declare when possible. Otherwise enforce by triggers. Document in question text if not in code. Constraints are fundamental to multiple subrow value instances in join
& to group by
.
PS 2 Learn the syntax/semantics for select
. Learn what left
/right
outer join on
s return--whatinner join on
does plus unmatched left/right table rows extended by null
s.
PS 3 Is there any rule of thumb to construct SQL query from a human-readable description?
How to use GROUP BY with SELF JOIN?
I'm not quite sure what you're trying to achieve?
If you're after a query that returns one row per employee, with two optional columns containing the manager's info, then your original query is correct (without the group by). The relationship is many-to-one, you're starting with a row per "many" that each has a single (optional) "one", so there is no need to group by.
This however is assuming that your data is correct and that prs_number is in fact unique for each employee. If you have two or more managers sharing a prs_number, you will end up with people having multiple managers.
By making this an outer join you're also returning people without a manager (i.e. top of the food chain :)), was this your intention?
EDIT
If you want only managers returned, then you can't keep the first column (P.prs_id) and get one row per manager. If you want the list of people that manage one or more people, this will do the trick:
SELECT M.prs_id AS 'Manager_id', M.prs_email AS 'Manager_email'
FROM qrd_prs_person AS P
INNER JOIN qrd_prs_person AS M
ON P.prs_manager_number = M.prs_number
GROUP BY M.prs_id, M.prs_email
Multiple self joins plus one inner join
Ok, take a look at below query and at the results:
SELECT *
FROM (SELECT
cs.cd, cs.dsc, cp.part_cd, cp.qty, cp.dllrs, cp.cu_type
FROM ck_startup cs
JOIN ck_price cp ON (cs.prd_type_cd = cp.prd_type_cd))
PIVOT (SUM(dllrs) AS dlllrs FOR (cu_type) IN ('A' AS a, 'AC' AS ac))
ORDER BY cd, qty
;
Output:
CD DSC PART_CD QTY A_DLLLRS AC_DLLLRS
-------- ----------------- ---------- ------- ---------- ----------
3D Stuff 1 100 10
3D Stuff 1 200 20
3D Stuff 1 300 30 30
DC Different stuff 1 100 50 50
DC Different stuff 1 200 100 100
DC Different stuff 1 300 150
DC Different stuff 1 400 200
DN2 Similar stuff 1 100 50 50
DN2 Similar stuff 1 200 100 100
DN2 Similar stuff 1 300 150
DN2 Similar stuff 1 400 200
It is not what you would expect, because I do not understand why you have different values in DLLRS_AC
column that are in the CK_PRICE
table? I mean, for example, why do you have 400
in last line of your output, not 200
? Why is this value doubled (as others are in DLLRS_AC
column)?
If you are using Oracle 10g, you can achieve the same result using DECODE
and GROUP BY
, take a look:
SELECT
cd,
dsc,
part_cd,
qty,
SUM(DECODE(cu_type, 'A', dllrs, NULL)) AS dllrs_a,
SUM(DECODE(cu_type, 'AC', dllrs, NULL)) AS dllrs_ac
FROM (
SELECT
cs.cd, cs.dsc, cp.part_cd, cp.qty, cp.dllrs, cp.cu_type
FROM ck_startup cs
JOIN ck_price cp ON (cs.prd_type_cd = cp.prd_type_cd)
)
GROUP BY cd, dsc, part_cd, qty
ORDER BY cd, qty;
Result is the same.
If you want to read more about pivoting, I recommend article by Tim Hall: Pivot and Unpivot at Oracle Base
Group by and Self Join
I'm not sure what is wrong with your query, but you can simplify it to:
SELECT myTable.Txn_id,
MAX(CASE WHEN role = 'Sender' THEN myTable.acid END) AS sender_acid,
MAX(CASE WHEN role = 'Recipient' THEN myTable.acid END) AS recipient_acid
FROM myTable join
myTable Subquery
on SubQuery.txn_id = myTable.txn_id and
Subquery.role = 'Transporter'
WHERE myTable.role in ('Sender', 'Receiver')
GROUP BY mytable.txn_id;
If you are getting two rows for what look like the same Txn_id
, it is because those ids are really different. Perhaps one is ending in spaces. To see this, try this select
:
select '|'||myTable.Txn_id||'|',
That will put vertical bars around the id so you can see any extra characters.
multiple self joins or unions in Access
As mentioned, consider MS Access' unique pivot query, the crosstab, which is a listed object in the Access UI Create ribbon query tab. In the design view, you would select columns in two GROUP BY
types (except Values
last being aggregate type)
- Row Heading fields (e.g., ID, Month, Year) - can be multiple table columns
- Column Heading field (e.g., Indicator) - can only be one where column result in separate columns
- Values field (e.g., Sum) - can only be one column using an aggregate function - Sum(), Max(), Avg(), etc.
The resulting SQL will be created (notice the aggregate query embedded):
TRANSFORM Sum(dummytable.Sum) AS SumOfSum
SELECT dummytable.ID, dummytable.Month, dummytable.Year
FROM dummytable
GROUP BY dummytable.ID, dummytable.Month, dummytable.Year
PIVOT dummytable.Indicator;
With results
ID Month Year Ind1 Ind2 Ind3
1 3 2016 10 20
2 3 2016 15 19
50 3 2016 5 5 5
For a generalized RDMS pivot query to work outside Access, simply use conditional aggregates. Do note in other SQL dialects, IIF()
function will have to be replaced with CASE/WHEN
or IF/THEN
:
SELECT dummytable.ID, dummytable.Month, dummytable.Year,
SUM(IIF(dummytable.Indicator = 'Ind1', [Sum], NULL)) As Ind1,
SUM(IIF(dummytable.Indicator = 'Ind2', [Sum], NULL)) As Ind2,
SUM(IIF(dummytable.Indicator = 'Ind3', [Sum], NULL)) As Ind3
FROM dummytable
GROUP BY dummytable.ID, dummytable.Month, dummytable.Year;
self join after an inner join
I assume that the table cities
has a column like state_id
that references a column state_id
in the table states
(change the names to the actual names of the columns).
First do a self join for cities
with the conditions:
c1.city = c2.city AND c1.state_id < c2.state_id
The <
operator makes sure that each pair of cities wil be returned only once.
Then join 2 copies of states
, because each of them will be used to get the name of the state for each of the 2 cities:
SELECT c1.city city1, s1.state state1,
c2.city city2, s2.state state2
FROM cities c1
INNER JOIN cities c2 ON c1.city = c2.city AND c1.state_id < c2.state_id
INNER JOIN states s1 ON s1.state_id = c1.state_id
INNER JOIN states s2 ON s2.state_id = c2.state_id
ORDER BY city1
Joined tables single row with desired results
The query :
select A.code, B1.start, B2.end
from TableB B1, TableB B2, TableA A
where B1.tableA_id = B2.tableA_id
and B1.tableA_id = A.id
and B1.start is not null and B2.end is not null
Related Topics
Left Outer Join and an Additional Where Clause
What Is the Effect of Omitting Size in Nvarchar Declaration
SQL Query for Index/Primary Key Ordinal
How to Convert Timestamp with Milliseconds to Date in Oracle
SQL Server:Export Query as a .Txt File
How to Unpivot a Table in Postgresql
Oracle Autoincrement with Sequence and Trigger Is Not Working Correctly
How to Get the Latest 2 Items Per Category in One Select (With MySQL)
Can You Use a Column for the Timezone Parameter of at Time Zone in Presto/Athena
How to Convert Hh:Mm:Ss to Seconds in SQL Server with More Than 24 Hours
Window Functions: Partition by One Column After Order by Another
How to Convert the System Date Format to Dd/Mm/Yy in SQL Server 2008 R2
Ora-30926: Unable to Get a Stable Set of Rows in the Source Tables When Merging Tables
Convert Timestamp to Date in Oracle SQL
Varchar2(N Byte|Char) Default -> Char or Byte