creating a mysql query with no duplicates -
this question has answer here:
- find duplicate records in mysql 20 answers
- create query on mysql 3 answers
create query list store_name, part_id, model_name, manufacturer_name, , onhand passenger type tires. sort list manufacturer name.
this put down:
select storelocation.store_name, tires.part_id, model_name, manufacturers.manufacturer_name, inventory.onhand storelocation, tires, manufacturers, inventory tire_type = 'passenger' order manufacturer_name; i got 4100 records. need no duplicates. got
thank can 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 1 | downtown | robert | | es | lucky 2 | eastside | megan | | ns | lucky 3 | northside | harold | +----------+-------------+-----------+---------------+ 3 rows in set (0.00 sec) query ok, 0 rows affected (0.13 sec) +----------+--------------+------+-----+---------+-------+ | field | type | null | key | default | | +----------+--------------+------+-----+---------+-------+ | part_id | char(5) | no | pri | | | | store_id | char(3) | no | pri | | | | onhand | decimal(5,0) | yes | | null | | +----------+--------------+------+-----+---------+-------+
if have actual duplicates can change 'select' 'select distinct' , done; have bigger problem; "duplicates" aren't duplicates
basically, constraint gave tire type must 'passenger'. how these tires relate to, example, manufactore names? if no constraint given (like now), returns every possible combination. example, if want link tires table tire table based on partid, can change statement to
where tire_type = 'passenger' , tires.part_id = inventory.part_id i may have wrong database names think understand problem lies. solve "duplicates" problem, need define relation between different tables in while statement.
Comments
Post a Comment