Why aren't these two sql statements returning same output?

Oliver

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:

enter image description here

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.

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.

Gabriel Durac

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.

edited at
0

Comments

0 comments
Login to comment

Related

Why aren't variables defined within the same function but in different if/else statements nonlocal to each other?

Why is the output of these two statements different?

If dictionaries aren't ordered, why do two dictionaries with the same key names return values in the same order?

Why aren't programs affecting this pipe output?

My statements aren't working and I don't know why

Why aren't the two code blocks equivalent?

Golang Why aren't these two strings equal?

Why aren't these two R objects identical?

Why aren't these two string equals?

Why aren't these two strings equivalent? C

Back button and a custom scroller, why aren't they acting the same on two webpages?

Why aren't my URL.Path statements getting hit

Why aren't these continue statements working in my loops?

Why aren't my If and Else statements doing nothing? (c++)

Why aren't these lines of code returning any result?

SQL: Why do I not get the same output with the two queries below?

Returning rows by users that aren't blocked sql server

If statements aren't functioning correctly

Are the two js statements always returning the same result? s.replace(/\t/g, '|') vs s.split('\t').join('|')

Why is the same jsonpath query returning a different output?

Why this InnerHtml returning same output as innerText

Why aren't Jinja2 extend statements and block statements not working?

How does this code work as conditional statements, why aren't there any "if" or "else if" statements?

Two methods with same erasure aren't necessary override-equivalent (or they signatures aren't subsignatures between them)?

Why aren't my unit tests running in the project output folder?

Ensuring two metavariables aren't unified to the same result

I think this two divs shoule be the same height, but they aren't

The two Bootstrap4 containers aren't resizing the same way

How to compare and match two tables that aren't the same?