Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2003
    Location
    Calgary, Alberta.
    Posts
    11

    Unanswered: ORing on multiple tables.

    Hi, I have found something unexpected ..

    $query = "select tbl_basic1.HID, neighborhood, lp
    from tbl_basic1, tbl_basic2 where tbl_basic1.HID = tbl_basic2.HID and tbl_basic1.HID = 1 OR tbl_basic1.HID = 2";

    neighborhood is the column from tbl_basic1, and lp is the column from tbl_basic2.

    And, now, I expected two rows of data that HID is 1 and 2. But, it returns more than two data.. somewhere around 6..

    I don't know why..

    how can I make joins tables first, and run mixed (AND/OR) queries?

    In the above example, what I want to have is two data. how?

    in real world words, I want to have "data that joined crossed table with same ID for each table, I want to get ID = 1 as well as ID = 2"

    Thanks.

  2. #2
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697

    Re: ORing on multiple tables.

    Originally posted by xgamen
    Hi, I have found something unexpected ..

    $query = "select tbl_basic1.HID, neighborhood, lp
    from tbl_basic1, tbl_basic2 where tbl_basic1.HID = tbl_basic2.HID and tbl_basic1.HID = 1 OR tbl_basic1.HID = 2";
    Try
    Code:
    SELECT t1.HID, t1.neighborhood, t2.lp
    FROM tbl_basic1 t1 INNER JOIN tbl_basic2 t2
        ON t1.HID = t2.HID
    WHERE t1.HID = 1 or t1.HID = 2
    You could also use:

    Code:
    WHERE t1.HID IN (1, 2)
    Note how I aliased tbl_basic1 to t1 and the same from tbl_basic2. You should also change their names to something descriptive.

    Read section 6.1.1 of The Fine Manual, or better yet, an introductory book on database systems. Just the first few chapters of "Introduction to Database Systems" by CJ Date would be very helpful.

    You need to learn how to break the problem into steps, rather than just throwing SQL at it and hoping something sticks. That means you really need to learn what joins actually do.

    You might also be running into one of SQL's "features": duplicates in your table. Make sure both tables have a primary key, this should probably be HID though I can't tell for certain without knowing your business rules.

Posting Permissions

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