CODEWITHSUNDEEP
mysqlsql
Ben Benli
Ben Benli

Reputation: 25

SQL- duplication of values from a query

Assuming that company name and product name are primary keys at their relation, I don't understand how come that name of company can't be repeated in this query:

     SELECT  Company.name
     FROM    Company, Product
     WHERE   Company.name=Product.maker
              AND  Product.name  IN
                         (SELECT Purchase.product
                           FROM   Purchase
                           WHERE Purchase.buyer = ‘Joe Blow‘);

If S company made two products X,Y which Joe Blow bought them both, so at the beginning we would make a cartesian product for the condition "Company.name=Product.maker": S-X, S-Y and for both, Product.name is in the set which compute, and therefor S should be output twice, no?

Upvotes: 0

Views: 65

Answers (3)

VahiD
VahiD

Reputation: 1064

It's OK that returns two S, because you projected company names from a query that returns company and products, each row of this query is unique but when you project some columns they maybe not unique, or can use DISTINCT

Upvotes: 0

roman
roman

Reputation: 117530

I'd say, in majority of RDBMS your query will return two 'S'.

you can try it yourself here SQL FIDDLE

you can put distinct to have no duplicates

 SELECT  distinct Company.name
 FROM    Company, Product
 WHERE   Company.name=Product.maker
          AND  Product.name  IN
                     (SELECT Purchase.product
                       FROM   Purchase
                       WHERE Purchase.buyer = 'Joe Blow')

Upvotes: 1

Travesty3
Travesty3

Reputation: 14479

Without seeing your table structure or an error message, I can't really tell what's wrong. Your syntax appears to be correct.

However, your query could be improved by using better joins:

SELECT
    c.name
FROM
    Company c
    INNER JOIN Product prod
        ON c.name = prod.maker
    INNER JOIN Purchase pch
        ON prod.name = pch.product
WHERE
    pch.buyer = 'Joe Blow'

I would recommend using explicit joins (INNER JOIN with an ON join condition) instead of implicit joins (comma-separated tables and join conditions in the WHERE clause) because it makes it clearer exactly how the tables are joined together.

Upvotes: 1

Related Questions