One to many join


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: One to many join

  1. #1
    Join Date
    Apr 2004
    Posts
    109

    One to many join

    There are 3 records in table A.

    id custid
    1 1234
    2 2345
    3 3456

    and 6 records in table B.

    id date
    1 10/01/05
    2 10/01/05
    2 10/03/05
    3 09/30/05
    3 10/01/05
    3 10/02/05

    The following SQL statement returns 5 records:

    select a.id, a.custid from a inner join b on a.id = b.id
    where b.date >= '10/01/05' and b.date <= '10/03/05'
    group by a.id

    Here is the return from the above query:

    id custid
    1 1234
    2 2345
    2 2345
    3 3456
    3 3456

    I only wanted 3 records. Can someone please revise my SQL statement?

    Thanks,
    Dan-Yeung

  2. #2
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    SELECT DISTINCT a.id, a.custid
    FROM a INNER JOIN b ON a.id = b.id
    WHERE b.date>= '10/1/2005' AND b.date<= '10/3/2005'
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  3. #3
    Join Date
    Apr 2004
    Posts
    109
    Hi Phil,

    I tried both distinct and group by. As long as the b.date is different, it retures two records fore the same id. Can you please help?

    Thanks.
    Dan-Yeung

  4. #4
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    I cannot reproduce that behavior. You're asking for distinct combinations of a.id and a.custid; b.date should not make any difference unless you're including it in the SELECT clause.
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

Similar Threads

  1. Add table to view
    By franklch in forum Database
    Replies: 3
    Last Post: 05-19-2005, 04:39 PM
  2. JOIN vs WHERE clause.
    By Igor in forum Database
    Replies: 2
    Last Post: 12-31-2002, 10:53 AM
  3. Problem with stored procedure
    By yip in forum Database
    Replies: 1
    Last Post: 09-24-2002, 12:31 AM
  4. Replies: 1
    Last Post: 06-26-2002, 10:30 PM
  5. Inner Join VS SubQuery
    By Bob Feldsien in forum Database
    Replies: 2
    Last Post: 05-30-2001, 06:59 PM

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