locked
Limit query result RRS feed

  • Question

  • I got 3 tables in first table I got Persons and second are cars. Third table is associated table when I join cars to persons (1 person can have many cars)

    I retrive from associated table persons who are not in associated table by left join:

     

    SELECT p.* FROM tb_persons p left join tb_persons_cars pa on p.persons_id=pa.FK_person_id where pa.FK_person_id is null
    

     

    But for now I want to retrive those persons who are in associated table, this is the same query + "is not null"

    But in tb_persons_cars I got a lot of same FK_persons_id's. My question is how to limit each to 1

    For example:

    10

    10

    10

    10

    11

    11

    12

     

    I want the output:

     

    10

    11

    12

    Monday, November 28, 2011 12:40 PM

Answers

  • try using distinct keyword
    • Proposed as answer by Bob Wu-MT Tuesday, November 29, 2011 10:01 AM
    • Marked as answer by Bob Wu-MT Wednesday, December 7, 2011 8:44 AM
    Monday, November 28, 2011 12:45 PM

All replies

  • try using distinct keyword
    • Proposed as answer by Bob Wu-MT Tuesday, November 29, 2011 10:01 AM
    • Marked as answer by Bob Wu-MT Wednesday, December 7, 2011 8:44 AM
    Monday, November 28, 2011 12:45 PM
  • Distinct is your solution as already stated by Prahalnathan.  I just stopped by here to ask you:  Why do you have a separate table to relate cars to people?  That is only done when you have to create a many-to-many relationship, and you described a one-to-many relationship.  You might want to simply add the FK_Person_ID field to the cars table.  This is the correct normal way for one-to-many.
    Jose R. MCP
    Monday, November 28, 2011 2:01 PM