RE: Requesting help with subquery

2006-09-28 Thread Zembower, Kevin 
Thanks, again, for folks who suggested solutions to my problem. To help
users searching the archives, I've pasted in a working solution at the
end of this message. Also, I'd like to ask if there is a more efficient
or better way of checking if the language version exist than the six
lines I repeated 8 times below.

Thanks, again.

-Kevin
=\
I originally wrote:
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 eight
languages. My SQL query so far is:
snip

Working solution:
[EMAIL PROTECTED]:~$ cat OrderDB-requested.sql
SELECT 
   m.title AS Main Category, 
   s.title AS Sub Category, 
   b.partno AS Part Number,
   (SELECT lv.title
  FROM langversion AS lv 
  WHERE lv.langid = 1 # English = 1
  AND b.baseitemid = lv.baseitemid
   ) as English Title,
   CONCAT(
  IF(EXISTS (SELECT * 
  FROM langversion AS lv
  WHERE lv.langid = 1 # 1 = English
  AND b.baseitemid = lv.baseitemid
  AND lv.available = 'Y'
  ), 'E', '-'),
  IF(EXISTS (SELECT * 
  FROM langversion AS lv
  WHERE lv.langid = 2 # 2 = French
  AND b.baseitemid = lv.baseitemid
  AND lv.available = 'Y'
  ), 'F', '-'),
  IF(EXISTS (SELECT * 
  FROM langversion AS lv
  WHERE lv.langid = 3 # 3 = Spanish
  AND b.baseitemid = lv.baseitemid
  AND lv.available = 'Y'
  ), 'S', '-'),
  IF(EXISTS (SELECT * 
  FROM langversion AS lv
  WHERE lv.langid = 4 # 4 = Portuguese
  AND b.baseitemid = lv.baseitemid
  AND lv.available = 'Y'
  ), 'P', '-'),
  IF(EXISTS (SELECT * 
  FROM langversion AS lv
  WHERE lv.langid = 5 # 5 = Arabic
  AND b.baseitemid = lv.baseitemid
  AND lv.available = 'Y'
  ), 'A', '-'),
  IF(EXISTS (SELECT * 
  FROM langversion AS lv
  WHERE lv.langid = 6 # 6 = Swahili
  AND b.baseitemid = lv.baseitemid
  AND lv.available = 'Y'
  ), 'W', '-'),
  IF(EXISTS (SELECT * 
  FROM langversion AS lv
  WHERE lv.langid = 7 # 7 = Russian
  AND b.baseitemid = lv.baseitemid
  AND lv.available = 'Y'
  ), 'R', '-'),
  IF(EXISTS (SELECT * 
  FROM langversion AS lv
  WHERE lv.langid = 8 # 8 = Turkish
  AND b.baseitemid = lv.baseitemid
  AND lv.available = 'Y'
  ), 'T', '-')
   )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;


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Requesting help with subquery

2006-09-26 Thread Zembower, Kevin 
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]

Re: Requesting help with subquery

2006-09-26 Thread Johan Höök 

Hi Kevin,
I didn't look that close at it but it
should be IFNULL, not ISNULL which is SQLserver's
version of it.

/Johan

Zembower, Kevin skrev:

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]

Re: Requesting help with subquery

2006-09-26 Thread Chris Sansom 

At 11:40 -0400 26/9/06, Zembower, Kevin wrote:

   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


Looks to me as if your parentheses don't balance 
here - you have an extra ')' in that last line.


--
Cheers... Chris
Highway 57 Web Development -- http://highway57.co.uk/

I once preached peaceful coexistence with Windows.
You may laugh at my expense - I deserve it.
   -- Jean-Louis Gassé, former Apple executive ( Be CEO)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Requesting help with subquery

2006-09-26 Thread Zembower, Kevin 
Thank you, Johan and Chris, for finding my obvious mistakes. Unfortunately, 
even after fixing them, I still get an SQL error:

[EMAIL PROTECTED]:~$ cat OrderDB-requested.sql
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((IFNULL(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
   ), 0), '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;
[EMAIL PROTECTED]:~$ mysql orderDB OrderDB-requested.sql
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the 
manual that corresponds to your MySQL server version for the right syntax to 
use near 'SELECT lv.langversionid 
  FROM langversion AS lv
  JO' at line 11
[EMAIL PROTECTED]:~$

-Kevin

-Original Message-
From: Chris Sansom [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, September 26, 2006 12:45 PM
To: Zembower, Kevin; mysql@lists.mysql.com
Subject: Re: Requesting help with subquery

At 11:40 -0400 26/9/06, Zembower, Kevin wrote:
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

Looks to me as if your parentheses don't balance 
here - you have an extra ')' in that last line.

-- 
Cheers... Chris
Highway 57 Web Development -- http://highway57.co.uk/

I once preached peaceful coexistence with Windows.
You may laugh at my expense - I deserve it.
-- Jean-Louis Gassé, former Apple executive ( Be CEO)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]