mysql subqueries keeps repeating

Question

Good Day Everyone, I am having a hard time doing this query where in I needed to List the boat name, owner number, owner last name, and owner first name for each boat in Marina 1.

I have a table of marina_slip and marina_owner

I have code of

SELECT ms.boat_name, ms.owner_num, o.last_name, o.first_name
FROM marina_slip ms, owner o
WHERE o.owner_num IN(SELECT ms.owner_num FROM marina_slip ms WHERE marina_num = '1');

the output is correct but it is repeatinng

query_output

Would you mind editing your question and making some use of the `<>` button above the text box, to format the code? — Caius Jard, Mar 12 '20 at 11:26
hello, thank youu the suggestion. Sorry, I am new here in this community. Still a student. — Maus0728, Mar 12 '20 at 11:30
Also, please don't post screenshots unless someone specifically asks. You've posted a screenshot of a text output, which a) is a lot more effort than just copy/pasting it, b) isn't searchable by search engines and c) can't be copied by us (if we want to set up a test of your problem. We prefer text pasted, not screenshots of text — Caius Jard, Mar 12 '20 at 11:32
Now, to your problem - specifically speaking your output isn't repeated/containing duplicates. All the rows are unique. Have a read up on "what is a Cartesian product" so you can better understand why it doesn't match what you expect — Caius Jard, Mar 12 '20 at 11:33

score 1 · Answer 1 · answered Mar 12 '20 at 11:31

You are doing an old style join but without the join conditions.
Use a proper join with an ON clause:

SELECT ms.boat_name, ms.owner_num, o.last_name, o.first_name
FROM marina_slip ms INNER JOIN owner o
ON o.owner_num = ms.owner_num
WHERE o.owner_num IN (SELECT ms.owner_num FROM marina_slip ms WHERE marina_num = '1');

or:

SELECT ms.boat_name, ms.owner_num, o.last_name, o.first_name
FROM marina_slip ms INNER JOIN owner o
ON o.owner_num = ms.owner_num
WHERE ms.marina_num = '1';

Caius Jard · Answer 2 · 2020-03-12T11:47:27.343

0

We haven't done joins like this for about 30 years:

FROM marina_slip ms, owner o

Chiefly because it encourages the problem you see now; every row from marina_slip is being combined with every row from owner. There is no relation between the tables expressed here. Use the modern join syntax an you won't have a problem:

SELECT ms.boat_name, ms.owner_num, o.last_name, o.first_name
FROM 
  marina_slip ms

  --express the relationship between the tables. -> owner num in each table is equal 
  INNER JOIN owner o ON o.owner_num = ms.owner_num

WHERE ms.marina_num = '1';

Please see this other question or jeff atwood's coding blog for more info on how to use INNER/LEFT/RIGHT JOIN syntax

edited Mar 12 '20 at 11:47

answered Mar 12 '20 at 11:40

Caius Jard

69,583
5
45
72

the syntax I followed is from the book from my university and I think it is outdated. Thank you for your help folks. – Maus0728 Mar 12 '20 at 12:05
Yeah, definitely one to complaint to your uni about. What book is it, out of curiosity? – Caius Jard Mar 12 '20 at 15:39

A-Nicoladie · Answer 3 · 2020-03-12T12:09:30.063

0

You have no joints between your tables.

You can do it with a LEFT JOIN

SELECT * FROM marina_slip
LEFT JOIN owner ON owner.owner_num=marina_slip.owner_num
WHERE marina_num=1

Or, if you want to use your select, add ms.owner_num=o.owner_num in your WHERE clause.

SELECT *
FROM marina_slip ms, owner o
WHERE ms.owner_num=o.owner_num
    AND o.owner_num IN(SELECT ms.owner_num FROM marina_slip ms WHERE marina_num = '1');

dbfiddle demo

edited Mar 12 '20 at 12:09

answered Mar 12 '20 at 11:49

A-Nicoladie

226
2
8

Why a LEFT JOIN? Does it make any difference? – forpas Mar 12 '20 at 11:53
With a `INNER JOIN` you only have boat with owner. With `LEFT JOIN` you have all boat even if they don"t have an owner – A-Nicoladie Mar 12 '20 at 11:53
All boats have an owner. – forpas Mar 12 '20 at 11:54
In this case, don't forget to add a foreign key to owner_num into marina_slip table – A-Nicoladie Mar 12 '20 at 11:57

mysql subqueries keeps repeating

3 Answers3