Re: simple sql query, but don't get it ...

From: Bjorn Abelli (DoNotSpam.bjorn_abelli_at_hotmail.com)
Date: 02/10/05

  • Next message: Karsten Baumgarten: "Re: Sybase, JDBC, AutoCommit, DDL IN TRAN" 
    Date: Thu, 10 Feb 2005 09:23:10 +0100

    "Bjorn Abelli" wrote...

    >>> STUDENT
    >>> =======
    >>> id BIGINT (PK)
    >>> firstname VARCHAR
    >>> lastname VARCHAR
    >>>
    >>> SUBSCRIBE
    >>> =========
    >>> stu_id BIGINT (FK to student.id)
    >>> mag_id BIGINT (FK to magazin.id)
    >>>
    >>> MAGAZIN
    >>> =======
    >>> id BIGINT (PK)
    >>> title VARCHAR
    >>> audience SMALLINT (can be of value 1, 2, 3)
    >>>
    >>>
    >>> I would like to know how the SQL query should look like if I want to
    >>> know the following thing:
    >>> ===============
    >>> Find all magazins for a student with the id=42 that are not subscribed
    >>> by him and are not of audience=3!
    >>
    >> So when's this due? ;)
    >>
    >> (and it's magazinE - with an 'E')
    >>
    >> (note: I think this is standard SQL, but I'm used to Oracle, so forgive
    >> me if
    >> it has any oracle specific syntax in it with the '!=')
    >>
    >> select MAGAZIN.title
    >> from MAGAZIN, SUBSCRIBE
    >> where SUBSCRIBE.mag_id=MAGAZIN.id
    >> and SUBSCRIBE.stu_id != 42
    >> and MAGAZIN.audience != 3;
    >
    > I suggest you use DISTINCT as well, as there can be several subscribing to
    > the same magazin(e)s:
    >
    > select DISTINCT MAGAZIN.title
    > from MAGAZIN, SUBSCRIBE
    > where SUBSCRIBE.mag_id=MAGAZIN.id
    > and SUBSCRIBE.stu_id <> 42
    > and MAGAZIN.audience <> 3;
    >
    > ...and yes, != is Oracle-specific, but you can use the standard SQL
    > operator <> in Oracle as well...

    On the other hand, this solution only takes those magazines that someone
    else already is subscribing to, so if there are some of that type, this
    should be better:

    select title
    from MAGAZIN
    where audience <> 3
    and id not in
      (select mag_id
       from SUBSCRIBE
       where stu_id = 42);

    // Bjorn A

  • Next message: Karsten Baumgarten: "Re: Sybase, JDBC, AutoCommit, DDL IN TRAN"