Results 1 to 4 of 4

Thread: Select query

  1. #1
    Join Date
    Apr 2003
    Posts
    5

    Unanswered: Select query

    Hello,

    I have two tables: Authors(aut_id, aut_name) and books(b_id,aut_id,b_name)

    How should i write my query in oder to display the aut_name from the table aut where there are no any book names under this aut_id.

    Table: Authors
    -----------------
    1. Taub
    2. Proakis
    3. Paul gray

    Table: Books
    ---------------
    1. 1. C Techniques
    2. 1. Networks
    3. 2. DSP

    Now when i run this query, i want to show only Paul gray.

    Many thanks

  2. #2
    Join Date
    Jan 2003
    Location
    Duncan BC Canada
    Posts
    80
    Use a left outer join.

    www.mysql.com/doc/en/JOIN.html

    Code:
    SELECT a.auth_name
    FROM Authors a LEFT OUTER JOIN Books b
     ON b.aut_id = a.aut_id
    WHERE b.b_id IS NULL;
    Bradley

  3. #3
    Join Date
    Apr 2003
    Posts
    5
    I'm using the following query in-order to get topic names which does'nt have subtopics under them.when i run the query it shows me nothing.

    SELECT topic.* FROM topic LEFT JOIN subtopic ON topic.TOPIC_ID = subtopic.SUBTOPIC_ID WHERE subtopic.SUBTOPIC_ID IS NULL;

    The table structure looks like:

    topic
    ........
    TOPIC_ID TOPIC_NAME
    1 Communications
    2 High frequency

    subtopic
    ----------
    SUBTOPIC_ID TOPIC_ID SUBTOPIC_NAME
    1 1 Mobile
    2 1 Optical
    3 1 CDMA

    In the above example, I dont have subtopics for the topic high frequency.So, I need to get that topic.

    Could somebody please tell me whats wrong in the query.

    Many thanks

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    SELECT topic.* FROM topic
    LEFT JOIN subtopic
    ON topic.TOPIC_ID = subtopic.TOPIC_ID
    WHERE subtopic.TOPIC_ID IS NULL;

    rudy
    http://r937.com/

Posting Permissions

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