sqlmysqlselect

select * from table1, table2 where table1.id = 1 showing values with other id

I just can't see the problem with how I'm making my foreign keys and I'm just really confused why I keep getting the wrong result. Here are screenshots from my workbench

enter image description here

Here are my tables:

enter image description here enter image description here

And here's my diagram

enter image description here

I've also tried to normalize my tables and I was kinda expecting my query to return a similar result like in the sample table (Questions table) where it will only show 2 results since I want to query where idsurvey = 1 I made in this image: enter image description here

My question is that, how do I fix my foreign key so that if I want to query

select * from survey.survey, survey.questions where idsurvey = 1

it will only return 2 rows? (based on sample data in the workbench screenshot)

Any comments and suggestions on my diagram would also be greatly appreciated.

Solution

  • When you have two tables in the from clause, every row from the first table is matched with every table from the second table. This is known as a Cartesian Product. Usually, this isn't the behavior you'd want (like it isn't in this case), and you'd use a condition to tell the database how to match these two tables:

    SELECT *
FROM   survey.survey s, survey.questions q
WHERE  s.idsurvey = q.survey_id AND idsurvey = 1

    While this should work, it's quite outdated to use multiple tables in the same from clause. You should probably use an explicit join clause instead:

    SELECT *
FROM   survey.survey s
JOIN   survey.questions q ON s.idsurvey = q.survey_id
WHERE  idsurvey = 1