help with some sql

Hi,

i need some help with a query. Ive got 3 tables, one for the magazin(uid, name, price), subscriber (uid, firstname, lastname) and subscription(personID, magazineuid).

What i want to do is perform a query that lets me show all the names of the magazine in one column and whether or not a specific person is subscribed to that magazine. so it would somethin like this:

mags subscriptions for joe bloggs:

mag name---- subscribed
mag 1 ----------yes
mag 2 ----------yes
mag 3 ----------no
..etc

any ideas?

Create a function that checks for the existence of a record in the subscription table.

ie.: (not properly coded)
function IsSubscribed(@person,@mag)
if exists(select personID from where personID=@person and magazineuid=@mag)
return 'y'
else
return 'n'


then use...
SELECT m.name AS 'magazine', p.name AS 'person', IsSubscribed(p.personID , m.magazineuid) AS 'subscribed'
FROM magazin m INNER JOIN subscriber p ON 1=1

This will join where subscriber to every magazine and check if they're subscribed. Filter it properly to check only the ones you want to...

eg.:
SELECT m.name AS 'magazine', p.name AS 'person', IsSubscribed(p.personID , m.magazineuid) AS 'subscribed'
FROM magazin m INNER JOIN subscriber p ON p.personID=999


SELECT m.name AS 'magazine', p.name AS 'person', IsSubscribed(p.personID , m.magazineuid) AS 'subscribed'
FROM magazin m INNER JOIN subscriber p ON m.magazineuid=999

thanks for that but i need to do it in a single sql query.

ok, that was ideal for a view
here's how to do it in one query:

Code: Select all

SELECT
   m.magazineuid, p.personID
   m.name AS 'magazine name',
   p.name AS 'person name', 
 CASE
  WHEN
    EXISTS(SELECT magazineuid FROM subscription WHERE personID=@personID AND magazineuid=@magazineuid)
  THEN 'Y'
  ELSE 'N'
 END
 AS 'subscribed'
FROM magazin m INNER JOIN subscriber p ON 1=1