i need an sql statement to list all the papers and whether or not Jacque chirac is subscribed to them. It must be outputted in the following way, and not in a view.

Paper Subscribed
Daily mail True
Guardian True
Metro False
Sun True
Times False

with data coming from the following tables

PAPER
paperid Papername Price
1 Guardian 45
2 Times 45
3 Sun 25
4 Metro 0
5 Daily mail 35

PERSON
personid firstname lastname
1 Jacque Chirac
2 George Bush

SUBSCRIBED
personid paperid
1 1
1 3
1 5
2 3
2 4


Thanks in advance guys

sounds like homework

what have you managed to come up with on your own so far?

rudy

LOL Homework. I have just come out of university and i am trying to extend my sql skills. I am unsure about the boolean part of this query and how to make the column with true/false in it. So far i can find out which papers Jacque is subscribed to using the following query


select paper.papername
from paper, person, subscription
where paper.paperid = subscription.paperid and subscription.personid = person.personid and person.firstname='Jacque' and person.lastname = 'Chirac';



help please! i am really stuck

okay, change what you have as follows --

select paper.papername
, case when person.firstname='Jacque'
and person.lastname = 'Chirac'
then 'True' else 'False' end
from paper, person, subscription
where paper.paperid = subscription.paperid
and subscription.personid = person.personid

rudy

Thanks mate. i really appreciate that