Multiple Self-Join Based on Group by Results

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 joins 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 joins 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 ons return--whatinner join on does plus unmatched left/right table rows extended by nulls.

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)

  1. Row Heading fields (e.g., ID, Month, Year) - can be multiple table columns
  2. Column Heading field (e.g., Indicator) - can only be one where column result in separate columns
  3. 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



Leave a reply



Submit