creating a mysql query with no duplicates

Question

Create a query to list the Store_Name, Part_ID, Model_Name, Manufacturer_Name, and OnHand for all Passenger Type tires. Sort the List by Manufacturer Name.

This is what i put down:

SELECT
  STORELOCATION.STORE_NAME,
  TIRES.PART_ID,
  MODEL_NAME,
  MANUFACTURERS.MANUFACTURER_NAME,
  INVENTORY.ONHAND
FROM
  STORELOCATION, TIRES, MANUFACTURERS, INVENTORY
WHERE
  TIRE_TYPE = 'Passenger'
ORDER BY MANUFACTURER_NAME;

I got like 4100 records. I need no duplicates. to which i got

thank you to anyone who can help me Craig

+---------+--------------------+----------------+------------+-------------+
| PART_ID | MODEL_NAME         | MANUFACTURERID | UNIT_PRICE | TIRE_TYPE   |
+---------+--------------------+----------------+------------+-------------+
| C424P   | Kestral            | M3             |      45.99 | Passenger   |
| C434P   | Peregrine          | M3             |      49.99 | Passenger   |
| C435T   | Eagle              | M3             |      56.99 | Truck       |
| C475X   | Hawk               | M3             |      63.99 | Performance |
| G738P   | Cross-Country      | M1             |      27.99 | Passenger   |
| G812T   | All-Terrain        | M1             |      39.99 | Truck       |
| G814T   | Expedition         | M1             |      47.99 | Truck       |
| G868P   | Urban              | M1             |      34.99 | Passenger   |
| G898X   | Performance-Radial | M1             |      56.99 | Performance |
| M225P   | Symmetry           | M2             |      39.99 | Passenger   |
| M235P   | Harmony            | M2             |      49.99 | Passenger   |
| M325X   | Energy             | M2             |      54.99 | Performance |
| M545X   | Grand-Prix         | M2             |      89.99 | Performance |
| Y320P   | Touring            | M4             |      19.99 | Passenger   |
| Y430P   | Assurance          | M4             |      29.99 | Passenger   |
| Y435P   | Freedom            | M4             |      24.99 | Passenger   |
| Y440T   | Cargo              | M4             |      29.99 | Truck       |
| Y450T   | Heavy-Duty         | M4             |      24.99 | Truck       |
+---------+--------------------+----------------+------------+-------------+
18 rows in set (0.00 sec)

Query OK, 0 rows affected (0.06 sec)

Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

+----------------+-------------------+-------------------+------------------+
| MANUFACTURERID | MANUFACTURER_NAME | MANUFACTURER_CITY | MANUFACTURER_REP |
+----------------+-------------------+-------------------+------------------+
| M1             | GoodTread         | Akron             | Ben              |
| M2             | Michelle          | Columbus          | Sarah            |
| M3             | ChickenCoop       | Findlay           | George           |
| M4             | Yomama            | Toledo            | Steve            |
+----------------+-------------------+-------------------+------------------+
4 rows in set (0.00 sec)

Query OK, 0 rows affected (0.07 sec)

Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

+----------+-------------+-----------+---------------+
| STORE_ID | STORE_NAME  | LOCATION  | STORE_MANAGER |
+----------+-------------+-----------+---------------+
| DT       | LUCKY ONE   | Downtown  | Robert        |
| ES       | LUCKY TWO   | Eastside  | Megan         |
| NS       | LUCKY THREE | Northside | Harold        |
+----------+-------------+-----------+---------------+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.13 sec)

+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| PART_ID  | char(5)      | NO   | PRI |         |       |
| STORE_ID | char(3)      | NO   | PRI |         |       |
| ONHAND   | decimal(5,0) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+

@ByronWhitlock This is a duplicate of their previous question. — Taryn, May 02 '13 at 21:32
@CraigOlander You should add it to your original question, don't post a new one — Taryn, May 02 '13 at 21:32

score 0 · Answer 1 · answered May 02 '13 at 23:55

If you have actual duplicates you can change 'select' to 'select distinct' and you are done; but you have a bigger problem; your "duplicates" aren't really duplicates

Basically, the only constraint you gave was that the tire type must be 'passenger'. How do these tires relate to, for example, manufactore names? If no constraint is given (like now), it just returns every possible combination. For example, if you want to link the tires table to the tire table based on the partid, you can change your where statement to

WHERE TIRE_TYPE = 'Passenger' AND TIRES.PART_ID = INVENTORY.PART_ID

I may have the wrong database names but I think you understand where the problem lies. To solve your "duplicates" problem, you need to define the relation between the different tables in your while statement.

creating a mysql query with no duplicates

1 Answers1