Create a query on mysql

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

+---------+--------------------+----------------+------------+-------------+
| 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    |       |
+----------+--------------+------+-----+---------+-------+

-> FROM INVENTORY;
+---------+----------+--------+
| PART_ID | STORE_ID | ONHAND |
+---------+----------+--------+
| C424P   | DT       |      8 |
| C424P   | ES       |     28 |
| C424P   | NS       |     18 |
| C434P   | DT       |     10 |
| C434P   | ES       |     20 |
| C434P   | NS       |     10 |
| C435T   | DT       |      2 |
| C435T   | ES       |     12 |
| C475X   | DT       |     24 |
| C475X   | ES       |     20 |
| C475X   | NS       |     24 |
| G738P   | DT       |     12 |
| G738P   | ES       |     32 |
| G738P   | NS       |     20 |
| G812T   | DT       |     40 |
| G812T   | ES       |     40 |
| G812T   | NS       |     34 |
| G814T   | DT       |     20 |
| G814T   | ES       |     20 |
| G814T   | NS       |     20 |
| G868P   | DT       |     36 |
| G898X   | DT       |     12 |
| G898X   | NS       |     12 |
| M225P   | DT       |      8 |
| M225P   | NS       |      8 |
| M235P   | DT       |      4 |
| M235P   | NS       |      4 |
| M325X   | DT       |     40 |
| M325X   | ES       |     40 |
| M325X   | NS       |     50 |
| M545X   | DT       |     40 |
| M545X   | NS       |     60 |
| Y320P   | DT       |     12 |
| Y320P   | ES       |     12 |
| Y320P   | NS       |     32 |
| Y430P   | DT       |     44 |
| Y430P   | ES       |     44 |
| Y430P   | NS       |     44 |
| Y435P   | DT       |     12 |
| Y435P   | ES       |     20 |
| Y435P   | NS       |     12 |
| Y440T   | DT       |      8 |
| Y450T   | DT       |     32 |
| Y450T   | ES       |     36 |
| Y450T   | NS       |     32 |
+---------+----------+--------+

Well you are not JOINing your tables on a particular column you are creating a cartesian result set. Can you post your table structures? — Taryn
– Taryn, Commented May 2, 2013 at 21:01
duplicates of what? entire rows? particular fields? some combination of fields? — Marc B
– Marc B, Commented May 2, 2013 at 21:01
@KhanhTran DISTINCT is 99% of the time a bad advice. This case/question does not belong to the rest 1%. — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented May 2, 2013 at 23:02

Taryn · Accepted Answer · 2013-05-02 21:53:53Z

2

The problem you are having in your current query is you are not joining the tables to the appropriate foreign keys so as a result you are generating a cartesian result.

Using the schema that you posted, you need to JOIN the tables on the foreign keys:

SELECT
  s.STORE_NAME,
  t.PART_ID,
  t.MODEL_NAME,
  m.MANUFACTURER_NAME,
  i.ONHAND
FROM STORELOCATION s
INNER JOIN INVENTORY i
  on s.STORE_ID = i.storeid
INNER JOIN TIRES t
  on i.partid = t.PART_ID
INNER JOIN MANUFACTURERS m
  on t.MANUFACTURERID = m.MANUFACTURERID
WHERE TIRE_TYPE = 'Passenger'
ORDER BY MANUFACTURER_NAME;

See SQL Fiddle with Demo

edited May 2, 2013 at 21:53

answered May 2, 2013 at 21:35

Taryn

249k57 gold badges374 silver badges409 bronze badges

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

5 Comments

Bohemian Over a year ago

+1 when you're right, you're right... And what does "blue feet" mean?

Taryn Over a year ago

@Bohemian I run, a lot. :) Or I am part smurf.

Craig Olander Over a year ago

what is INVENTORY i? do you mean INVENTORY ONHAND?

Taryn Over a year ago

@CraigOlander the i is the alias for the inventory table. The inventory onhand is in the select list

Craig Olander Over a year ago

thank you blue feet i got it to work...and to everyone for helping me thank you all

Jim Garrison · Accepted Answer · 2013-05-02 21:34:53Z

1

Assuming a minimal schema, reverse-engineered from your query, of:

storelocation
    store_location_id
    store_name

inventory
    store_location_id
    part_id
    onhand

tires
   part_id
   manufacturer_id
   model_name

manufacturer
   manufacturer_id
   manufacturer_name

This query is close to what you want

SELECT
  sl.STORE_NAME,
  t.PART_ID,
  t.MODEL_NAME,
  m.MANUFACTURER_NAME,
  inv.ONHAND
FROM
  inventory inv 
    left join storelocation sl on inv.store_location_id = sl.store_location_id
    left join tires t on inv.part_id = t.part_id
    left join manufacturer m on t.manufacturer_id = m.manufacturer_id
WHERE
  t.TIRE_TYPE = 'Passenger'
ORDER BY MANUFACTURER_NAME;

answered May 2, 2013 at 21:34

Jim Garrison

87k20 gold badges162 silver badges197 bronze badges

Comments

echo_Me · Accepted Answer · 2013-05-02 21:15:04Z

-2

you need to GROUP BY the column which you dont want have duplicates example

    WHERE
    TIRE_TYPE = 'Passenger'
    GROUP BY STORELOCATION.STORE_NAME
    ORDER BY MANUFACTURER_NAME;

answered May 2, 2013 at 21:15

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

Collectives™ on Stack Overflow

Create a query on mysql

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related