I'm having a bit of a brain jam. These are the sql statements I want
to run but I can't quite figure it out:

1. Be able to return all entries in the ITEM table with the
attributes, but sorted by a particular ITEM_ATTRIBUTES.NAME (Sort by
Color).

2. Search by attribute value.

3. Be able to return the item with it's attributes all on one row. E.g
itemid title, description, name1, value1, name2, value2, name3,
value3
1, shirt, really nice shirt, color, red, color, blue,
size, s, size, m
(or something similar).

Sub-selects cannot be used.

Here's the tables:

ITEM
--------
ITEMID
TITLE
DESCRIPTION

ITEM_ATTRIBUTES
------------------
ITEMID
NAME
VALUE

Example:
ITEM
-------------
ITEMID, TITLE, DESCRIPTION
1 , shirt , really nice shirt

ITEM_ATTRIBUTES
-----------------
ITEMID, NAME, VALUE
1 , Color, Red
1 , Color, Blue
1 , Size, S
1 , Size, M

Thanks for your thoughts.

Justin Kennedy <ng (AT) NO_SPAMmaritimesource (DOT) ca> wrote

Hmm. Are mid term exams coming up already?

You use the buzzwords like attribute and subselect but don't even know
when to use a WHERE Clause???

okay some hints:
1 look up ORDER BY
2 look up WHERE clause
2 look up JOIN and SELECT clauses

You did at least buy the textbook didn't you?

HTH (I really do),
Ed

This is a report and not a query; do it in the front end where it belongs.