CODEWITHSUNDEEP
sqljoinmariadb
roibubble
roibubble

Reputation: 125

Why does my sql statment return the same output multiple times

Hell everyone.

I made a sql statment that returns me the name of a teacher, the time he has to attend the class and what type of class he is giving. My statement returns what i expect but it returns the same answer multiple time, why is that ? I don't quite undersand it.

Here is my statement

SELECT
`profile`.`name`,
`profile`.nachname,
unterichts_zeit.zeit,
unterichts_typ.typ
FROM
unterichts_zeit
INNER JOIN `profile` ON unterichts_zeit.lehrer = `profile`.id ,
unterichts_typ

And my output

teacher1    teacher1    9:35    klavier
teacher2    teacher2    10:35   klavier
teacher1    teacher1    9:35    gittare
teacher2    teacher2    10:35   gittare
teacher1    teacher1    9:35    generell
teacher2    teacher2    10:35   generell
teacher1    teacher1    9:35    schlagzeug
teacher2    teacher2    10:35   schlagzeug
teacher1    teacher1    9:35    trombone
teacher2    teacher2    10:35   trombone
teacher1    teacher1    9:35    generell
teacher2    teacher2    10:35   generell

Seeing this now it also doesnt really display what i want ... it display every type of class which is not correct obv... damn

if anyone has an idea how i can solve this

thank you

Upvotes: 0

Views: 461

Answers (1)

Caius Jard
Caius Jard

Reputation: 74700

You've mixed your join styles and created a Cartesian product. A Cartesian product is where the number of rows grows because unrelated rows are joined together. Sometimes it is useful, but usually it is not what is required

At the very end of your sql statement you have a comma and then another table name

The database has taken the work it already did with the INNER JOINed pair of tables and then just combined every row from them with every row from this table

If you intend to join 3 tables together use another inner join statement 

SELECT
`profile`.`name`,
`profile`.nachname,
unterichts_zeit.zeit,
unterichts_typ.typ
FROM
unterichts_zeit
INNER JOIN `profile` ON unterichts_zeit.lehrer = `profile`.id
INNER JOIN unterichts_typ ON <<fill-this-on-clause-out>>

In the old days we joined tables like this:

SELECT *
FROM a, b, c
WHERE a.id = b.id AND b.id = c.id

If you left off the WHERE clause the db would just output every row combination. If A contained 1 and 2 and B contained "x" and "y" and c contained true and false you'd get:

1,x,true
1,x,false
1,y,true
1,y,false
2,x,true
2,x,false
2,y,true
2,y,false

Now the modern equivalent is to use INNER JOIN, and it's a lot harder to forget the ON clause (equivalent of the old way using the where clause) because it's a syntax error to do so. But db still allow the old way too and even allow mixing. Your statement having "comma tablename" at the end is a mixed join style, old way of producing a Cartesian product (forgotten where clause)

Upvotes: 4

Related Questions