MySQL Query returning duplicated results

Question

Fairly new to mySQL . Trying to get a query to work, but keep getting the right results, but duplicated.

Here's the query

SELECT DISTINCT 
    band.band_name, concert.venue_name 
FROM 
    band, concert 
WHERE 
    concert.date="2012-10-17" AND band_name 
IN 
    (SELECT band.band_name FROM band WHERE band.band_id 
IN 
    (SELECT tour.band_id from tour where tour.tour_name 
IN 
    (SELECT tour_name from concert where concert.date="2012-10-17")));

DDL:

CREATE TABLE band (
    band_id      INT,
    band_name    VARCHAR(50),
    genre        VARCHAR(20),
    PRIMARY KEY (band_id) );

CREATE TABLE musician (
    person_id   INT,
    name        VARCHAR(50),
    band_id     INT, 
    PRIMARY KEY (person_id),
    FOREIGN KEY (band_id) REFERENCES band(band_id) );

CREATE TABLE tour(
    tour_name   VARCHAR(50),
    band_id     INT,
    PRIMARY KEY (tour_name),
    FOREIGN KEY (band_id) REFERENCES band(band_id) );

CREATE TABLE venue(
    venue_name  VARCHAR(30),
    hire_cost   INT,
    PRIMARY KEY (venue_name) );

CREATE TABLE concert(
    concert_id       INT,
    date             DATE,
    tour_name        VARCHAR(50),
    venue_name       VARCHAR(30),
    ticket_sales     INT, 
    PRIMARY KEY      (concert_id),
    FOREIGN KEY      (tour_name) REFERENCES tour(tour_name),
    FOREIGN KEY      (venue_name) REFERENCES venue(venue_name) );

I'm totally lost. Any help would be greatly appreciated.

Yes. See about JOINs. Any beginners book or tutorial would do. — Strawberry
– Strawberry, Commented Dec 20, 2017 at 8:36
I'm surprised that a distinct clause fails to remove duplicates. Can you add sample data to your question as text? — P.Salmon
– P.Salmon, Commented Dec 20, 2017 at 8:52

Patrick Artner · Accepted Answer · 2017-12-20 06:46:55Z

1

Joining tables is the way to go, you do not pile all your conditions into the where clause:

SELECT DISTINCT 
    b.band_name, c.venue_name 
FROM concert c 
  join venue v on v.venue_name = c.venue_name -- thats how those 2 tables connect
  join tour t on t.tour_name = c.tour_name    -- thats how those 2 tables connect
  join band b on b.band_id = t.band_id        -- thats how those 2 tables connect
WHERE c.date="2012-10-17"             -- and this gives you all the bandnames and  
                            -- venuenames that play on every concert on this date

This way the DB can optimize your query and due to the joins on the tables does not need to scan so much data.

answered Dec 20, 2017 at 6:46

Patrick Artner

51.9k10 gold badges50 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL Query returning duplicated results

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related