Results 1 to 5 of 5

Thread: Help. sql query

  1. #1
    Join Date
    Sep 2003
    Posts
    3

    Unanswered: Help. sql query

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sounds like homework

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

    rudy

  3. #3
    Join Date
    Sep 2003
    Posts
    3
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2003
    Posts
    3

    Thumbs up

    Thanks mate. i really appreciate that

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •