[Wolves] Happy Christmas ...and help me with this mysql query

[Andy Smith]

On Thu, Dec 27, 2012 at 05:51:33PM +0000, James Turner wrote:
> On 27/12/12 16:54, Andy Smith wrote:
> >On Thu, Dec 27, 2012 at 04:46:53PM +0000, Wayne Morris wrote:
> >>How do I select from
> >>Item      Parts
> >>
> >>Item A   Part1
> >>Item A  Part2
> >>--------    -------
> >>ItemA   Part11
> >>ItemA    Part 12
> >>ItemB    Part1
> >>ItemC    Part1
> >>ItemD    Part 1
> >>ItemD    Part 2
> >
> >I am having great difficulty understanding your table structure.
> 
> I'd imagine the above is a junction table used to resolve a
> many-to-many relationship in the schema ("each item can be composed
> of many parts"; "each part is potentially used to construct several
> items") and the "Item" and "Parts" fields are essentially foreign
> keys to their respective tables.

Which is the correct way to do it, however it is not at all clear
how many tables are involved here, or what their names or columns
are, or what Wayne is trying to achieve.

The introduction of a partial text match is completely wrong and
likely to give poor performance aside from being really cumbersome
to use.

> (see also: http://en.wikipedia.org/wiki/Associative_Entities )
> 
> >>I want to select only the first part for each item, but if I do
> >>select Item where Parts like '%1%', I get Part1 and Part11 and Part
> >>12, etc
> 
> I'd guess that Andy is asking how you'd construct a query that would
> return a result set like:
> 
> {Item A,Part 1}
> {Item B,Part 1}
> {Item C,Part 1}
> {Item D,Part 1}

(Wayne is asking, not me).

> ...where the second field is the first-listed part identifier from
> the table (e.g. when ordered alphabetically, or however desired).

So, Wayne, if we assume schema like:

CREATE TABLE `Items` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

CREATE TABLE `Parts` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

Then a table to link them might be like:

CREATE TABLE `ItemsParts` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `item_id` int(10) unsigned NOT NULL,
  `part_id` int(10) unsigned NOT NULL,
  `quantity` int(10) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `item_id` (`item_id`,`part_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

Some example items:

mysql> select * from Items;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | Chair        |
|  3 | Coffee table |
|  2 | Sofa         |
|  4 | Stool        |
+----+--------------+

The parts that are available:

mysql> select * from Parts;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | Leg          |
|  2 | Seat cushion |
|  3 | Top          |
+----+--------------+

How those parts combine to make up those items:

mysql> select * from ItemsParts;
+----+---------+---------+----------+
| id | item_id | part_id | quantity |
+----+---------+---------+----------+
|  1 |       1 |       1 |        4 |
|  2 |       1 |       2 |        1 |
|  3 |       2 |       1 |        4 |
|  4 |       2 |       2 |        3 |
|  5 |       3 |       1 |        4 |
|  6 |       3 |       3 |        1 |
|  7 |       4 |       1 |        3 |
|  8 |       4 |       2 |        1 |
+----+---------+---------+----------+

So, for example, a Sofa (item #2) has 4x part #1 ("Leg") and 3x part
#2 ("Seat cushion"):

mysql> select ItemsParts.quantity, Parts.name from ItemsParts, Parts where ItemsParts.part_id=Parts.id and ItemsParts.item_id=2;
+----------+--------------+
| quantity | name         |
+----------+--------------+
|        4 | Leg          |
|        3 | Seat cushion |
+----------+--------------+

A Stool (item #4) has 3x part #1 ("Leg") and 1x part #2 ("Seat
cushion"):

mysql> select ItemsParts.quantity, Parts.name from ItemsParts, Parts where ItemsParts.part_id=Parts.id and ItemsParts.item_id=4;
+----------+--------------+
| quantity | name         |
+----------+--------------+
|        3 | Leg          |
|        1 | Seat cushion |
+----------+--------------+

If you were only interested in the first Part from any given Item
then you could use LIMIT:

mysql> select ItemsParts.quantity, Parts.name from ItemsParts, Parts where ItemsParts.part_id=Parts.id and ItemsParts.item_id=4 limit 1;
+----------+------+
| quantity | name |
+----------+------+
|        3 | Leg  |
+----------+------+

however, the order this would come back in would be unpredictable as
it would be based on the order of the rows in MySQL. As suggested by
James you could order it alphabetically:

mysql> select ItemsParts.quantity, Parts.name from ItemsParts, Parts where ItemsParts.part_id=Parts.id and ItemsParts.item_id=4 order by Parts.name desc limit 1;
+----------+--------------+
| quantity | name         |
+----------+--------------+
|        1 | Seat cushion |
+----------+--------------+

The trouble is I struggle to to imagine in what scenario this would
be useful, so it is likely that we do not understand your use case
and you had better explain further. Depending on what you're trying
to achieve it will affect your chosen schema.

Either way, selecting stuff based on partial text match is just the
wrong way to go.

Hope that helped.

Cheers,
Andy

-- 
http://bitfolk.com/ -- No-nonsense VPS hosting
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 198 bytes
Desc: Digital signature
URL: <http://mailman.lug.org.uk/pipermail/wolves/attachments/20121227/b04dd092/attachment.pgp>