SQL question


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: SQL question

  1. #1
    Join Date
    Apr 2008
    Posts
    1

    Post SQL question

    Please answer the following SQL question
    There are 3 tables.
    cat dog animal
    id, name id, name id, color

    what's the sql for listing the name of the animals with color = brown?

  2. #2
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    SELECT [name id] FROM Cat WHERE color = 'brown'
    UNION SELECT [name id] FROM dog WHERE color = 'brown'
    UNION SELECT [name id] FROM animal WHERE color = 'brown';

  3. #3
    Join Date
    Sep 2004
    Location
    Surrey, UK
    Posts
    21
    Use

    Code:
    SELECT c.name FROM cat c, animal a WHERE c.id = a.id AND a.color = 'brown'
    UNION
    SELECT d.name FROM dog d, animal a WHERE d.id = a.id AND a.color = 'brown'
    or

    Code:
    SELECT x.name FROM (
        SELECT id, name FROM cat
        UNION
        SELECT id, name FROM dog
    ) x, animal a
    WHERE x.id = a.id AND a.color = 'brown'
    The efficiency and execution times of these two queries vary depending on the organisation of the tables, indexes, etc.

  4. #4
    Join Date
    Feb 2006
    Posts
    55
    From a performance point of view, you might want to use UNION ALL rather than UNION.

    UNION will return a distinct result set - if you know there will be no duplicates, it's more efficient to use UNION ALL.

    SELECT [name] FROM Cat WHERE color = 'brown'
    UNION ALL
    SELECT [name] FROM dog WHERE color = 'brown'
    UNION ALL
    SELECT [name] FROM animal WHERE color = 'brown';

Similar Threads

  1. SQL Server Insert Question
    By David Satz in forum Database
    Replies: 1
    Last Post: 05-09-2002, 04:09 PM
  2. Sql server vs. Access Question
    By PhilpL in forum VB Classic
    Replies: 0
    Last Post: 10-22-2001, 04:23 PM
  3. SQL if statement question
    By Ted Young in forum Database
    Replies: 1
    Last Post: 07-20-2001, 11:37 AM
  4. Access to SQL server
    By Nate in forum Database
    Replies: 29
    Last Post: 05-09-2001, 10:04 AM
  5. Question for a SQL Pro
    By ben in forum Database
    Replies: 5
    Last Post: 09-12-2000, 03:44 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center