Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Posts
    1

    Unanswered: Search table1 table get a result from table2

    Hello I am knew to sql so please be gentle, I am a student and I have got my head around most of the basics however I have two areas I am unable to work out.

    I want to workout what has not been purchased

    t1

    p_id------description---------price
    1------------nut------------------1
    2------------screw---------------2
    3------------bolt-----------------3
    4------------washer-------------4
    5------------cap-----------------5

    t2
    o_id------p_id----------qty
    1------------1------------4
    2------------5------------1
    3------------4------------1
    4------------1------------3
    5------------5------------2

    the answer should be 2,3 but I am not able to get the answer. I have tried something along

    Select t1.p_id, t1.description
    from t1, t2
    Where t2.p_id <> t1.p_id;

    but this doesn't work, I would appreciate any help many thanks from a novice
    Last edited by denerious; 04-22-04 at 19:03.

  2. #2
    Join Date
    Mar 2004
    Posts
    480
    Generally there are two types of joins that are used, an inner join where you want to match all rows in common and an outer join (usually left outer join is used in preference to right outer join).

    In the case where you are looking for rows that are not matching you want to use an outer join.

    In this case you want to join both tables and find out which items in the right hand table are null, or not joined in the left table since they aren't ordered yet.

    In this query use:

    SELECT t1.p_id
    , t1.description
    FROM t1
    LEFT
    OUTER JOIN
    t2 ON t1.p_id = t2.p_id
    WHERE
    t2.p_id is null;

    Your idea joined the tables but gave you a list of all the items included in the table, the left outer join using the is null gives you items in the right table that are not present.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Select t1.p_id
    from t1
    minus
    select t2.p_id
    from t2;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    anacedent, nice one

    but does mysql actually support the MINUS operator?

    no

    it is a new feature "planned for the mid-term future"

    specifically, some time after release 5, which itself is a long way from production status
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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