www.webdeveloper.com
Results 1 to 3 of 3

Thread: Problem with Inner Join

  1. #1
    Join Date
    Nov 2005
    Posts
    18

    Problem with Inner Join

    Hello,

    I have 2 tables I need to join and I'm having a problem (SQL 2000) ...

    (1) The first table "basic" contains the following fields I need...
    - first_name, last_name, sport1, sport2

    (2) The next table "sport" I need to grab fields as follows...
    - sportID (which may match either sport1 or sport2), sportyear, active

    I have tried the stuff below and it doesn't work...

    SELECT ALL * FROM basic WHERE sport1 = '$sportID' OR sport2 = '$sportID' ";
    INNER JOIN sport B on B.sportid = A.sport1 OR B.sportid = A.sport2

    How do I do this?

    Thanks.


    Kathy

  2. #2
    Join Date
    Feb 2003
    Posts
    2,745
    Code:
    SELECT	b.first_name, b.last_name,
    	b.sport1, s1.sportID sport1id, s1.sportYear sport1year, s1.active sport1active,
    	b.sport2, s2.sportID sport2id, s2.sportYear sport2year, s2.active sport2active
    FROM basic b
    LEFT JOIN
    	sport s1
    On	b.sport1 = s1.sportID
    LEFT JOIN
    	sport s2
    On	b.sport2 = s2.sportID

  3. #3
    Join Date
    Jul 2005
    Location
    South Carolina
    Posts
    395
    Having sport1, sport2, ..., sportn fields is an indication to me that part of your database is not normalized. This can make querying very convoluted. Here's how I'd approach the design:
    Code:
    PEOPLE
    
    nam_id     |     nam_first     |     nam_last
    -----------+---------------+----------------
    1          |        bob       |       smith
    2          |       chuck     |       taylor
    
    SPORTS
    
    spo_id      |     spo_desc
    -----------+---------------
    1          |      baseball
    2          |       football
    
    PEOPLE_SPORTS
    
    pes_id       |      nam_id     |      spo_id
    ------------+------------------+--------------
    1           |         2        |        1
    2           |         2        |        2
    3           |         1        |        2
    If you choose to keep your current design, then I'll give you a hint: WHERE must come after INNER JOIN... ON.
    Last edited by bubbisthedog; 09-20-2007 at 12:55 PM.
    I can solve differential equations and build huge databases, but I have no idea how to change my oil.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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



Recent Articles