I'm trying to use a subquery for the first time, and am having some
problems. I'm hoping someone can give me some suggestions on ways of
doing what I want, and further suggestions if there's a better way of
going about my task.
I have a database of publications in different languages. "main
categories" are organized into "sub categories" with "baseitems" of
publications. Each baseitem can be printed in one or more of six
languages. My SQL query so far is:
SELECT
m.title AS "Main Category",
s.title AS "Sub Category",
b.partno AS "Part Number",
(SELECT lv.title
FROM langversion AS lv
JOIN baseitem AS b2 ON lv.baseitemid = b2.baseitemid
WHERE lv.langid = "1" # English = 1
AND b.baseitemid=lv.baseitemid
) as "English Title",
IF(ISNULL(SELECT lv.langversionid
FROM langversion AS lv
JOIN baseitem AS b3 ON lv.baseitemid =
b3.baseitemid
WHERE lv.langid = "1"
AND b.baseitemid = lv.baseitemid
)), 'Y', 'N') AS "Lang Avail"
FROM maincategory AS m
JOIN subcategory AS s ON m.maincatid=s.maincatid
JOIN baseitem AS b ON s.subcatid=b.subcatid
WHERE
b.available = "Y"
ORDER BY m.title, s.title;
This gives me an error at line 11, "IF(ISNULL(SELECT ...". This should
give me a "Y" if the English version exists, and a "N" otherwise.
If I modify it like this, it works:
SELECT
m.title AS "Main Category",
s.title AS "Sub Category",
b.partno AS "Part Number",
(SELECT lv.title
FROM langversion AS lv
JOIN baseitem AS b2 ON lv.baseitemid = b2.baseitemid
WHERE lv.langid = "1"
AND b.baseitemid=lv.baseitemid
) as "English Title",
(SELECT lv.langversionid
FROM langversion AS lv
JOIN baseitem AS b3 ON lv.baseitemid =
b3.baseitemid
WHERE lv.langid = "1"
AND b.baseitemid = lv.baseitemid
) AS "Lang Avail"
FROM maincategory AS m
JOIN subcategory AS s ON m.maincatid=s.maincatid
JOIN baseitem AS b ON s.subcatid=b.subcatid
WHERE
b.available = "Y"
ORDER BY m.title, s.title;
I think this demonstrates that the two subqueries are working.
What I'd ultimately like to do is produce a string like "YNNYYN" where Y
is printed if the language version of the baseitem exists (is not
null?). I was going to do this by creating a SELECT subquery for each
language version possible and CONCAT the "Y" or "N" together.
Can anyone suggest where I'm going wrong in my attempts? Is there a
better way overall to produce this information?
Thanks in advance for your help and suggestions.
-Kevin
Kevin Zembower
Internet Services Group manager
Center for Communication Programs
Bloomberg School of Public Health
Johns Hopkins University
111 Market Place, Suite 310
Baltimore, Maryland 21202
410-659-6139
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
