SQL SP puzzle - Is there a simple way to do this?

SQL SP puzzle - Is there a simple way to do this?

Post by Eric Style » Sun, 02 Apr 2000 04:00:00



Hi - wonder if you can help me with this sql problem.

I have a Categories, a People table and an intermediate PeopleCat table
to implement the many to many relationship

CATEGORIES           PERSONCAT          People
ID NAME              IDCAT IDPERSON     ID NAME
-- ----              ----- --------     -- ----
1  Interesting       1     1            1  Jeff
2  Funny             4     1            2  Peter
3  Boring            8     1            
.etc                 5     2
.etc                 9     2    
10 Popular

I want to write some sql to return the following for a Person E.g. for
Jeff....

1  Interesting   True
2  Funny         False
3  Boring        False
4  Lame          True
5  Vital         False
6  Sweet         False
7  Invaluable    False
8  Rich          True
9  Kind          False
10 Homely        False

I want all the categories and a true/false depending if Jeff has these
categories.
Can you help? Thanks in advance.

 
 
 

SQL SP puzzle - Is there a simple way to do this?

Post by Paul Vorontso » Mon, 03 Apr 2000 05:00:00


Hi, Eric!

It's simple!
Try

select c.id, c.name case when exists( select 1 from personcat pc where

from categories c

or

select c.id, c.name, case when inull(pc.personid,0)=0 then 'False' else
'True' end
from categories c left outer join personcat pc on pc.idcat = c.id and

Regards,
Pavel S.Vorontsov

Quote:> Hi - wonder if you can help me with this sql problem.

> I have a Categories, a People table and an intermediate PeopleCat table
> to implement the many to many relationship

> CATEGORIES           PERSONCAT          People
> ID NAME              IDCAT IDPERSON     ID NAME
> -- ----              ----- --------     -- ----
> 1  Interesting       1     1            1  Jeff
> 2  Funny             4     1            2  Peter
> 3  Boring            8     1
> .etc                 5     2
> .etc                 9     2
> 10 Popular

> I want to write some sql to return the following for a Person E.g. for
> Jeff....

> 1  Interesting   True
> 2  Funny         False
> 3  Boring        False
> 4  Lame          True
> 5  Vital         False
> 6  Sweet         False
> 7  Invaluable    False
> 8  Rich          True
> 9  Kind          False
> 10 Homely        False

> I want all the categories and a true/false depending if Jeff has these
> categories.
> Can you help? Thanks in advance.


 
 
 

SQL SP puzzle - Is there a simple way to do this?

Post by Itzik Ben-Ga » Mon, 03 Apr 2000 05:00:00


Schema attached below.

SELECT *
FROM Categories AS C LEFT OUTER JOIN Personcat AS PC
  ON C.cid = PC.cid AND PC.pid = 1

cid         cname                     cid         pid
----------- ------------------------- ----------- -----------
1           Interesting               1           1
2           Funny                     NULL        NULL
3           Boring                    NULL        NULL
4           Lame                      4           1
5           Vital                     NULL        NULL
6           Sweet                     NULL        NULL
7           Invaluable                NULL        NULL
8           Rich                      8           1
9           Kind                      NULL        NULL
10          Homely                    NULL        NULL

SELECT C.cid, cname, CASE
                       WHEN PC.pid IS NOT NULL THEN 'True'
                       ELSE 'False'
                     END AS isincat
FROM Categories AS C LEFT OUTER JOIN Personcat AS PC
  ON C.cid = PC.cid AND PC.pid = 1

cid         cname                     isincat
----------- ------------------------- -------
1           Interesting               True
2           Funny                     False
3           Boring                    False
4           Lame                      True
5           Vital                     False
6           Sweet                     False
7           Invaluable                False
8           Rich                      True
9           Kind                      False
10          Homely                    False

-- schema
CREATE TABLE Categories(
cid int NOT NULL CONSTRAINT PK_categories_cid PRIMARY KEY,
cname varchar(25))

CREATE TABLE People(
pid int NOT NULL CONSTRAINT PK_people_pid PRIMARY KEY,
pname varchar(25) NOT NULL)

CREATE TABLE Personcat(
cid int NOT NULL CONSTRAINT FK_personcat_categories REFERENCES
Categories(cid),
pid int NOT NULL CONSTRAINT FK_personcat_people REFERENCES People(pid),
CONSTRAINT PK_personcat_cid_pid PRIMARY KEY(cid, pid))

INSERT INTO Categories VALUES(1,'Interesting')
INSERT INTO Categories VALUES(2,'Funny')
INSERT INTO Categories VALUES(3,'Boring')
INSERT INTO Categories VALUES(4,'Lame')
INSERT INTO Categories VALUES(5,'Vital')
INSERT INTO Categories VALUES(6,'Sweet')
INSERT INTO Categories VALUES(7,'Invaluable')
INSERT INTO Categories VALUES(8,'Rich')
INSERT INTO Categories VALUES(9,'Kind')
INSERT INTO Categories VALUES(10,'Homely')

INSERT INTO People VALUES(1, 'Jeff')
INSERT INTO People VALUES(2, 'Peter')

INSERT INTO Personcat VALUES(1, 1)
INSERT INTO Personcat VALUES(4, 1)
INSERT INTO Personcat VALUES(8, 1)
INSERT INTO Personcat VALUES(5, 2)
INSERT INTO Personcat VALUES(9, 2)

--
BG

Hi-Tech College, Israel
http://www.mcse.org.il/isql


Quote:> Hi - wonder if you can help me with this sql problem.

> I have a Categories, a People table and an intermediate PeopleCat table
> to implement the many to many relationship

> CATEGORIES           PERSONCAT          People
> ID NAME              IDCAT IDPERSON     ID NAME
> -- ----              ----- --------     -- ----
> 1  Interesting       1     1            1  Jeff
> 2  Funny             4     1            2  Peter
> 3  Boring            8     1
> .etc                 5     2
> .etc                 9     2
> 10 Popular

> I want to write some sql to return the following for a Person E.g. for
> Jeff....

> 1  Interesting   True
> 2  Funny         False
> 3  Boring        False
> 4  Lame          True
> 5  Vital         False
> 6  Sweet         False
> 7  Invaluable    False
> 8  Rich          True
> 9  Kind          False
> 10 Homely        False

> I want all the categories and a true/false depending if Jeff has these
> categories.
> Can you help? Thanks in advance.

 
 
 

SQL SP puzzle - Is there a simple way to do this?

Post by Mike Leh » Mon, 03 Apr 2000 05:00:00


I have tried to repeat this and I received the following error:

CASE is not supported

I assume that it means that the keyword 'CASE' does not work on my machine --
would anone know why this is happening??

thanks

Mike


> Schema attached below.

> SELECT *
> FROM Categories AS C LEFT OUTER JOIN Personcat AS PC
>   ON C.cid = PC.cid AND PC.pid = 1

> cid         cname                     cid         pid
> ----------- ------------------------- ----------- -----------
> 1           Interesting               1           1
> 2           Funny                     NULL        NULL
> 3           Boring                    NULL        NULL
> 4           Lame                      4           1
> 5           Vital                     NULL        NULL
> 6           Sweet                     NULL        NULL
> 7           Invaluable                NULL        NULL
> 8           Rich                      8           1
> 9           Kind                      NULL        NULL
> 10          Homely                    NULL        NULL

> SELECT C.cid, cname, CASE
>                        WHEN PC.pid IS NOT NULL THEN 'True'
>                        ELSE 'False'
>                      END AS isincat
> FROM Categories AS C LEFT OUTER JOIN Personcat AS PC
>   ON C.cid = PC.cid AND PC.pid = 1

> cid         cname                     isincat
> ----------- ------------------------- -------
> 1           Interesting               True
> 2           Funny                     False
> 3           Boring                    False
> 4           Lame                      True
> 5           Vital                     False
> 6           Sweet                     False
> 7           Invaluable                False
> 8           Rich                      True
> 9           Kind                      False
> 10          Homely                    False

> -- schema
> CREATE TABLE Categories(
> cid int NOT NULL CONSTRAINT PK_categories_cid PRIMARY KEY,
> cname varchar(25))

> CREATE TABLE People(
> pid int NOT NULL CONSTRAINT PK_people_pid PRIMARY KEY,
> pname varchar(25) NOT NULL)

> CREATE TABLE Personcat(
> cid int NOT NULL CONSTRAINT FK_personcat_categories REFERENCES
> Categories(cid),
> pid int NOT NULL CONSTRAINT FK_personcat_people REFERENCES People(pid),
> CONSTRAINT PK_personcat_cid_pid PRIMARY KEY(cid, pid))

> INSERT INTO Categories VALUES(1,'Interesting')
> INSERT INTO Categories VALUES(2,'Funny')
> INSERT INTO Categories VALUES(3,'Boring')
> INSERT INTO Categories VALUES(4,'Lame')
> INSERT INTO Categories VALUES(5,'Vital')
> INSERT INTO Categories VALUES(6,'Sweet')
> INSERT INTO Categories VALUES(7,'Invaluable')
> INSERT INTO Categories VALUES(8,'Rich')
> INSERT INTO Categories VALUES(9,'Kind')
> INSERT INTO Categories VALUES(10,'Homely')

> INSERT INTO People VALUES(1, 'Jeff')
> INSERT INTO People VALUES(2, 'Peter')

> INSERT INTO Personcat VALUES(1, 1)
> INSERT INTO Personcat VALUES(4, 1)
> INSERT INTO Personcat VALUES(8, 1)
> INSERT INTO Personcat VALUES(5, 2)
> INSERT INTO Personcat VALUES(9, 2)

> --
> BG

> Hi-Tech College, Israel
> http://www.mcse.org.il/isql



> > Hi - wonder if you can help me with this sql problem.

> > I have a Categories, a People table and an intermediate PeopleCat table
> > to implement the many to many relationship

> > CATEGORIES           PERSONCAT          People
> > ID NAME              IDCAT IDPERSON     ID NAME
> > -- ----              ----- --------     -- ----
> > 1  Interesting       1     1            1  Jeff
> > 2  Funny             4     1            2  Peter
> > 3  Boring            8     1
> > .etc                 5     2
> > .etc                 9     2
> > 10 Popular

> > I want to write some sql to return the following for a Person E.g. for
> > Jeff....

> > 1  Interesting   True
> > 2  Funny         False
> > 3  Boring        False
> > 4  Lame          True
> > 5  Vital         False
> > 6  Sweet         False
> > 7  Invaluable    False
> > 8  Rich          True
> > 9  Kind          False
> > 10 Homely        False

> > I want all the categories and a true/false depending if Jeff has these
> > categories.
> > Can you help? Thanks in advance.

 
 
 

SQL SP puzzle - Is there a simple way to do this?

Post by Itzik Ben-Ga » Tue, 04 Apr 2000 04:00:00


CASE is ANSI compliant.
Which DBMS are you running?

--
BG

Hi-Tech College, Israel
http://www.mcse.org.il/isql


> I have tried to repeat this and I received the following error:

> CASE is not supported

> I assume that it means that the keyword 'CASE' does not work on my
machine --
> would anone know why this is happening??

> thanks

> Mike


> > Schema attached below.

> > SELECT *
> > FROM Categories AS C LEFT OUTER JOIN Personcat AS PC
> >   ON C.cid = PC.cid AND PC.pid = 1

> > cid         cname                     cid         pid
> > ----------- ------------------------- ----------- -----------
> > 1           Interesting               1           1
> > 2           Funny                     NULL        NULL
> > 3           Boring                    NULL        NULL
> > 4           Lame                      4           1
> > 5           Vital                     NULL        NULL
> > 6           Sweet                     NULL        NULL
> > 7           Invaluable                NULL        NULL
> > 8           Rich                      8           1
> > 9           Kind                      NULL        NULL
> > 10          Homely                    NULL        NULL

> > SELECT C.cid, cname, CASE
> >                        WHEN PC.pid IS NOT NULL THEN 'True'
> >                        ELSE 'False'
> >                      END AS isincat
> > FROM Categories AS C LEFT OUTER JOIN Personcat AS PC
> >   ON C.cid = PC.cid AND PC.pid = 1

> > cid         cname                     isincat
> > ----------- ------------------------- -------
> > 1           Interesting               True
> > 2           Funny                     False
> > 3           Boring                    False
> > 4           Lame                      True
> > 5           Vital                     False
> > 6           Sweet                     False
> > 7           Invaluable                False
> > 8           Rich                      True
> > 9           Kind                      False
> > 10          Homely                    False

> > -- schema
> > CREATE TABLE Categories(
> > cid int NOT NULL CONSTRAINT PK_categories_cid PRIMARY KEY,
> > cname varchar(25))

> > CREATE TABLE People(
> > pid int NOT NULL CONSTRAINT PK_people_pid PRIMARY KEY,
> > pname varchar(25) NOT NULL)

> > CREATE TABLE Personcat(
> > cid int NOT NULL CONSTRAINT FK_personcat_categories REFERENCES
> > Categories(cid),
> > pid int NOT NULL CONSTRAINT FK_personcat_people REFERENCES People(pid),
> > CONSTRAINT PK_personcat_cid_pid PRIMARY KEY(cid, pid))

> > INSERT INTO Categories VALUES(1,'Interesting')
> > INSERT INTO Categories VALUES(2,'Funny')
> > INSERT INTO Categories VALUES(3,'Boring')
> > INSERT INTO Categories VALUES(4,'Lame')
> > INSERT INTO Categories VALUES(5,'Vital')
> > INSERT INTO Categories VALUES(6,'Sweet')
> > INSERT INTO Categories VALUES(7,'Invaluable')
> > INSERT INTO Categories VALUES(8,'Rich')
> > INSERT INTO Categories VALUES(9,'Kind')
> > INSERT INTO Categories VALUES(10,'Homely')

> > INSERT INTO People VALUES(1, 'Jeff')
> > INSERT INTO People VALUES(2, 'Peter')

> > INSERT INTO Personcat VALUES(1, 1)
> > INSERT INTO Personcat VALUES(4, 1)
> > INSERT INTO Personcat VALUES(8, 1)
> > INSERT INTO Personcat VALUES(5, 2)
> > INSERT INTO Personcat VALUES(9, 2)

> > --
> > BG

> > Hi-Tech College, Israel
> > http://www.mcse.org.il/isql



> > > Hi - wonder if you can help me with this sql problem.

> > > I have a Categories, a People table and an intermediate PeopleCat
table
> > > to implement the many to many relationship

> > > CATEGORIES           PERSONCAT          People
> > > ID NAME              IDCAT IDPERSON     ID NAME
> > > -- ----              ----- --------     -- ----
> > > 1  Interesting       1     1            1  Jeff
> > > 2  Funny             4     1            2  Peter
> > > 3  Boring            8     1
> > > .etc                 5     2
> > > .etc                 9     2
> > > 10 Popular

> > > I want to write some sql to return the following for a Person E.g. for
> > > Jeff....

> > > 1  Interesting   True
> > > 2  Funny         False
> > > 3  Boring        False
> > > 4  Lame          True
> > > 5  Vital         False
> > > 6  Sweet         False
> > > 7  Invaluable    False
> > > 8  Rich          True
> > > 9  Kind          False
> > > 10 Homely        False

> > > I want all the categories and a true/false depending if Jeff has these
> > > categories.
> > > Can you help? Thanks in advance.

 
 
 

SQL SP puzzle - Is there a simple way to do this?

Post by Mitche » Tue, 04 Apr 2000 04:00:00


select a.name,b.name from People a inner join(Categories b inner join
personcat c on b.id = c.idcat) on a.id = c.idperson
where a.name = 'Jeff'

HTH

Quote:>Hi - wonder if you can help me with this sql problem.

>I have a Categories, a People table and an intermediate PeopleCat table
>to implement the many to many relationship

>CATEGORIES           PERSONCAT          People
>ID NAME              IDCAT IDPERSON     ID NAME
>-- ----              ----- --------     -- ----
>1  Interesting       1     1            1  Jeff
>2  Funny             4     1            2  Peter
>3  Boring            8     1            
>.etc                 5     2
>.etc                 9     2    
>10 Popular

>I want to write some sql to return the following for a Person E.g. for
>Jeff....

>1  Interesting   True
>2  Funny         False
>3  Boring        False
>4  Lame          True
>5  Vital         False
>6  Sweet         False
>7  Invaluable    False
>8  Rich          True
>9  Kind          False
>10 Homely        False

>I want all the categories and a true/false depending if Jeff has these
>categories.
>Can you help? Thanks in advance.

 
 
 

SQL SP puzzle - Is there a simple way to do this?

Post by Mike Leh » Tue, 04 Apr 2000 04:00:00


I am using Microsoft SQL Server -- when I tried to install the Server I got the
following error:

The Developer Edition server componet cannot be installed on Windows 95 using
this CD.  Only client components will be available for installation.

Does this mean that I got a chitzy verion of SQL server -- is that it.  Any
comment helpful.  The CD that I am trying to install form is the MCDE CD that is
included in Visual Studion Professional.


> I have tried to repeat this and I received the following error:

> CASE is not supported

> I assume that it means that the keyword 'CASE' does not work on my machine --
> would anone know why this is happening??

> thanks

> Mike


> > Schema attached below.

> > SELECT *
> > FROM Categories AS C LEFT OUTER JOIN Personcat AS PC
> >   ON C.cid = PC.cid AND PC.pid = 1

> > cid         cname                     cid         pid
> > ----------- ------------------------- ----------- -----------
> > 1           Interesting               1           1
> > 2           Funny                     NULL        NULL
> > 3           Boring                    NULL        NULL
> > 4           Lame                      4           1
> > 5           Vital                     NULL        NULL
> > 6           Sweet                     NULL        NULL
> > 7           Invaluable                NULL        NULL
> > 8           Rich                      8           1
> > 9           Kind                      NULL        NULL
> > 10          Homely                    NULL        NULL

> > SELECT C.cid, cname, CASE
> >                        WHEN PC.pid IS NOT NULL THEN 'True'
> >                        ELSE 'False'
> >                      END AS isincat
> > FROM Categories AS C LEFT OUTER JOIN Personcat AS PC
> >   ON C.cid = PC.cid AND PC.pid = 1

> > cid         cname                     isincat
> > ----------- ------------------------- -------
> > 1           Interesting               True
> > 2           Funny                     False
> > 3           Boring                    False
> > 4           Lame                      True
> > 5           Vital                     False
> > 6           Sweet                     False
> > 7           Invaluable                False
> > 8           Rich                      True
> > 9           Kind                      False
> > 10          Homely                    False

> > -- schema
> > CREATE TABLE Categories(
> > cid int NOT NULL CONSTRAINT PK_categories_cid PRIMARY KEY,
> > cname varchar(25))

> > CREATE TABLE People(
> > pid int NOT NULL CONSTRAINT PK_people_pid PRIMARY KEY,
> > pname varchar(25) NOT NULL)

> > CREATE TABLE Personcat(
> > cid int NOT NULL CONSTRAINT FK_personcat_categories REFERENCES
> > Categories(cid),
> > pid int NOT NULL CONSTRAINT FK_personcat_people REFERENCES People(pid),
> > CONSTRAINT PK_personcat_cid_pid PRIMARY KEY(cid, pid))

> > INSERT INTO Categories VALUES(1,'Interesting')
> > INSERT INTO Categories VALUES(2,'Funny')
> > INSERT INTO Categories VALUES(3,'Boring')
> > INSERT INTO Categories VALUES(4,'Lame')
> > INSERT INTO Categories VALUES(5,'Vital')
> > INSERT INTO Categories VALUES(6,'Sweet')
> > INSERT INTO Categories VALUES(7,'Invaluable')
> > INSERT INTO Categories VALUES(8,'Rich')
> > INSERT INTO Categories VALUES(9,'Kind')
> > INSERT INTO Categories VALUES(10,'Homely')

> > INSERT INTO People VALUES(1, 'Jeff')
> > INSERT INTO People VALUES(2, 'Peter')

> > INSERT INTO Personcat VALUES(1, 1)
> > INSERT INTO Personcat VALUES(4, 1)
> > INSERT INTO Personcat VALUES(8, 1)
> > INSERT INTO Personcat VALUES(5, 2)
> > INSERT INTO Personcat VALUES(9, 2)

> > --
> > BG

> > Hi-Tech College, Israel
> > http://www.mcse.org.il/isql



> > > Hi - wonder if you can help me with this sql problem.

> > > I have a Categories, a People table and an intermediate PeopleCat table
> > > to implement the many to many relationship

> > > CATEGORIES           PERSONCAT          People
> > > ID NAME              IDCAT IDPERSON     ID NAME
> > > -- ----              ----- --------     -- ----
> > > 1  Interesting       1     1            1  Jeff
> > > 2  Funny             4     1            2  Peter
> > > 3  Boring            8     1
> > > .etc                 5     2
> > > .etc                 9     2
> > > 10 Popular

> > > I want to write some sql to return the following for a Person E.g. for
> > > Jeff....

> > > 1  Interesting   True
> > > 2  Funny         False
> > > 3  Boring        False
> > > 4  Lame          True
> > > 5  Vital         False
> > > 6  Sweet         False
> > > 7  Invaluable    False
> > > 8  Rich          True
> > > 9  Kind          False
> > > 10 Homely        False

> > > I want all the categories and a true/false depending if Jeff has these
> > > categories.
> > > Can you help? Thanks in advance.