I'm just getting started with sql and have the objective to transform this:
select X.persnr
from Pruefung X
where X.persnr in (
select Y.persnr
from pruefung Y
where X.matrikelnr <> Y.matrikelnr)
output:
into the same output but using a form of join. I tried it the way below but I can't seem to get "rid" of the cartesian product as far as i can see. Or maybe i misunderstood the above statement what it should actually do. For me the above says "for each unique matrikelnr display all corresponding persnr".
select X.persnr
from Pruefung X
join pruefung y on x.persnr=y.persnr
where x.matrikelnr<>y.matrikelnr
output: A long list (I don't want to fill the entire question with it) - i am guessing the cartesian product from the join
This is the relation I am using.
Edit: Distinct (unless i am using it in the wrong place) won't work because then persnr is only displayed once, thats not the objective though.
Your initial query actually does: select persnr from Pruefung if the same persnr exists for a a diferent matrikelnr.
"for each unique matrikelnr display all corresponding persnr" This is achieved using aggregation:
Depending on the DBMS you are using you could use something like (SQL Server uses STRING_AGG, but MySQL uses GROUP_CONCAT)
SELECT matrikelnr,STRING_AGG(matrikelnr,',')
GROUP BY matrikelnr
You cannot easily achieve what you got from a correlated query (your first attempt) by using a join.
Edit: A join does not result in a "Cartesian product" expect from when there is no join condition (CROSS JOIN). A join matches two sets based on a join condition. The reason why you get more entries is that the join looks at the join key (PERSNR) and does its matching.
For example for 101 you have 3 entries. That means you will get 3x3 reults. You then filter out the results for the cases where X.matrikelnr <> Y.matrikelnr If we assume matrikelnr is unique that would mean the row matched with itself. so you will lose 3 results ending up with 3x3 - 3 = 6.
If you want to achieve something in SQL you must first define what you are expecting to use and then use the appropiate tools (in this case correlated queries not joins)
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments