How to access subquery alias

Hello everyone. I have been working on a query that I am unable to solve as a single query.

I have 3 tables:
Table A is a part table (called part):
ID
UPC
Short description
etc…

Table B is a part long description table (called description):
ID
Description

Table C is a part category table (called part_category):
ID
Category
Subcategory
Sub2category

My goal is to display a list of parts that the user could select. The problem I cannot solve is that a part can have multiple categories and subcategories (but not multiple sub2categories), however when displaying the list, I do not want an item to appear multiple times in the list, so what I have which works is this:

SELECT s.url,s.last_update,UNIX_TIMESTAMP(s.last_update) AS unixtime,s.website,s.image,d.short_description,
[INDENT](SELECT categoryid FROM part_category WHERE partid=s.partid LIMIT 1) AS categoryid,[/INDENT]
[INDENT](SELECT subcategoryid FROM part_category WHERE partid=s.partid LIMIT 1) AS subcategoryid,[/INDENT]
[INDENT](SELECT sub2categoryid FROM part_category WHERE partid=s.partid LIMIT 1) AS sub2categoryid [/INDENT]
FROM part s 
JOIN description d ON s.partid = d.partid 
ORDER BY s.last_update
DESC LIMIT 10

However when I want to select the same list of parts but I want to narrow based on a category, I tried this (which does not work - unknown column ‘categoryid’ in ‘where clause’):

SELECT s.url,s.last_update,UNIX_TIMESTAMP(s.last_update) AS unixtime,s.website,s.image,d.short_description,
[INDENT](SELECT categoryid FROM part_category WHERE partid=s.partid LIMIT 1) AS categoryid,[/INDENT]
[INDENT](SELECT subcategoryid FROM part_category WHERE partid=s.partid LIMIT 1) AS subcategoryid,[/INDENT]
[INDENT](SELECT sub2categoryid FROM part_category WHERE partid=s.partid LIMIT 1) AS sub2categoryid [/INDENT]
FROM part s 
JOIN description d ON s.partid = d.partid 
WHERE categoryid = 1
ORDER BY s.last_update
DESC LIMIT 10

The same error message comes up if I want to narrow by subcategory and sub2category. After a bit of research, I found this thread:

Which states that it is not legal syntax to try to reference the alias of a subquery. I tried to recreate this query by removing the subqueries and use joins, but with joins I was unable to figure out a way of only returning 1 result from the part_category table (if it existed, a JOIN DISTINCT).

I am hopeful that someone may have an idea whether this is possible in a single query.

Thank you for reading this post.

Thank you for your rapid response!!

Table A contains part information, for example a spark plug UPC, short description, last_update, etc.

Table B contains a long description for the spark plug

Table C contains category information for the spark plug. Specifically:
Partid Categoryid Subcategoryid Sub2categoryid
99 1 44 2354
99 1 44 2993
99 1 45 2865
99 2 87 3458

So in the above example, the spark plug (99) may fit an american made car (1) or foreign car (2). Of american made cars, it may fit a Ford (44) or GM (45) vehicle. The sub2categoryid is always unique for a given part (car model). One way this is used would be for a user to browse for parts starting from a parent list (not shown) and as they they narrow their need, they will get down to the part level. But if the user browses for spark plugs for american made cars (by category), I don’t want part 99 showing up on the list 3 times (even though it exists in multiple subcategories). The first query I listed shows a spark plug one time in the list when browsing by parts. However, when the user selects a spark plug when browsing by category (like in the second query listed above), I get the error message posted above.

Here is an example output:
<user browses by category for parts for an american made car>

Batteries

Spark Plugs
Starters

I do not want Spark Plugs for american cars (Categoryid 1) to appear multiple times, I just want it to appear on the list once.

I hope this gives a clearer idea what I am trying to do.

Thank you for taking your time to look at this issue.

for your first query, try this –


SELECT s.url
     , s.last_update
     , UNIX_TIMESTAMP(s.last_update) AS unixtime
     , s.website
     , s.image
     , d.short_description
     , pc.categoryid 
     , pc.subcategoryid 
     , pc.sub2categoryid 
  FROM part AS s 
INNER
  JOIN description AS d 
    ON d.partid = s.partid 
INNER
  JOIN part_category AS pc
    ON pc.partid = s.partid
ORDER 
    BY s.last_update DESC LIMIT 10

this returns each part with all the part_category rows that match

for the second query, you’ll start at the category table, and use DISTINCT to ensure parts only show up once

this means, however, that you cannot show the subcategoryid or sub2categoryid columns, because of course they will differ for the multiple part rows

SELECT DISTINCT
     , s.url
     , s.last_update
     , UNIX_TIMESTAMP(s.last_update) AS unixtime
     , s.website
     , s.image
     , d.short_description
  FROM part_category AS pc
INNER
  JOIN part AS s 
    ON s.partid = pc.partid
INNER
  JOIN description AS d 
    ON d.partid = s.partid 
 WHERE pc.categoryid = 1
ORDER 
    BY s.last_update DESC LIMIT 10

Thank you for your rapid response!

I will try this solution and report back!

it might be :slight_smile:

however, i don’t understand your tables, or what you’re trying to do, and especially not the part about not wanting an item to appear multiple times in the list

could you perhaps give a few sample rows of data for table C and then show the results that you want from the query