Seems like a another way of not doing the same thing. The reason name is
in group by is because this was a dumbed down query. The original had
gender or style, possibly both.
However, this still does not work. What you gave me did not specifiy
"gender". When I run it, I get all of the Item_attribute_link records for
that particular old sku, like color, fabrication, etc. If I do put
"gender" in as a criteria,
SELECT item.old_sku, item_attribute_link.value AS Gender
FROM [SELECT attrib_id, name FROM attribute WHERE name="Gender" ]. AS x
RIGHT JOIN (item_attribute_link RIGHT JOIN item ON
item_attribute_link.item_id = item.item_id) ON x.attrib_id =
item_attribute_link.attrib_id
WHERE (((item.old_sku)="44641313"))
GROUP BY item.old_sku, item_attribute_link.value, x.name
HAVING (((x.name)="Gender"))
ORDER BY item.old_sku, item_attribute_link.value;
Then if there is no "gender", I get nothing. I want to get the
Item.old_sku regardless of whether there is a matchign gender record or
not.
Michel said:
SELECT item.old_sku, item_attribute_link.value AS Gender
FROM (SELECT attrib_id, name FROM attribute WHERE name="Gender" ) AS x
RIGHT JOIN (item_attribute_link RIGHT JOIN item
ON item_attribute_link.item_id = item.item_id)
ON x.attrib_id = item_attribute_link.attrib_id
WHERE item.old_sku = "44641313"
GROUP BY item.old_sku, item_attribute_link.value, x.name
ORDER BY item.old_sku, item_attribute_link.value;
I don't know what the field "name" does in the GROUP BY clause (you
supplied it initially, so, I kept it).
Vanderghast, Access MVP
What I want is pretty straightforward. I want all the records for the
desired old_sku,(44641313 in this example) from [item], always, and any
"gender" records that happen to be attached. With the code I have now,
any records which match on old_sku are dropped UNLESS it has a matching
Gender record.
Old SKU item_attributelink.value attribute.name
4414114 mens gender
purple color
large size
4414114 womens gender
purple color
large size
44641313 green color
large size
If I ask for 4414114 I want to see
OLDSKU GENDER
4414114 mens 4414154 womens
If I ask for 44641313 I want to see
OLDSKU GENDER
44641313
as it is, 44641313 never shows up because there was no Gender record.
Michel Walsh wrote:
An OR will return what EITHER has an old_sku of 446413413 ( as string,
not as number) EITHER a gender.
If that is not what you want, can you supply a numerical example of
records, and mark those you want from those you don't:
old_sku attribute wanted
44641313 gender yes
44641313 null yes
44641111 gender yes
44641111 yellow no
Vanderghast, Access MVP
IF I used an OR, wouldn't I get all sorts of items that have a "gender"
record., but do not have a old_sku of 44641313?
I dumbed this down a little for the forum. The old_sku typically
refers to a dozen or so items, and would be chosen from a form. Some
of the skus refer to clothing, which has a gender. Some of them refer
to generic hardware. What I have now does not return anything UNLESS is
has a gender record.
It seems your fix would give me all records that have a gender,
regardless of old_sku....
Michel Walsh wrote:
It seems you need a OR, instead of an AND. Since your HAVING clause
involves only non-aggregated expression, you can safely change it for
a WHERE clause:
SELECT item.old_sku, item_attribute_link.value AS Gender
FROM attribute RIGHT JOIN (item_attribute_link RIGHT JOIN item ON
item_attribute_link.item_id = item.item_id) ON attribute.attrib_id =
item_attribute_link.attrib_id
GROUP BY item.old_sku, item_attribute_link.value, attribute.name
WHERE (item.old_sku="44641313") OR ( (attribute.name = "Gender") )
ORDER BY item.old_sku, item_attribute_link.value;
I kept the condition
item.old_sku="44641313"
even if your description was about keeping old_sku "regardless of
anything else". It also assumes old_sku is a string, not a number.
Vanderghast, Access MVP
OK. Here is the SQL.
SELECT item.old_sku, item_attribute_link.value AS Gender
FROM attribute RIGHT JOIN (item_attribute_link RIGHT JOIN item ON
item_attribute_link.item_id = item.item_id) ON attribute.attrib_id =
item_attribute_link.attrib_id
GROUP BY item.old_sku, item_attribute_link.value, attribute.name
HAVING (((item.old_sku)="44641313") AND ((attribute.name)="Gender"))
ORDER BY item.old_sku, item_attribute_link.value;
This query gives me a value for item.old_sku and
item_attribute_link.value, ONLY IF attribute.name="Gender"
Not what I want. I want it to return a value for item.old_sku
REGARDLESS of anything else, and a value for
item_attribute_link.value IF attribute.name = "Gender."
Help?
Phil
