Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3

Thread: Please Help...

  1. #1
    New Coder
    Join Date
    Jul 2002
    Location
    Cape Town, S Africa
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Please Help...

    Hey All,

    Is it possible to select a random record of a table where the record has no related records in an inner joined table?

    ie...

    [Table 1. - Main Table (members)]

    ------------------------------
    ID | Firstname | Lastname
    ------------------------------
    01 | James | Bond
    02 | Paul | McMahon
    03 | Sean | Connor
    ------------------------------

    [Table 2. - Related Table (Orders for example)]

    ------------------------------
    ID | Order # | Product
    ------------------------------
    02 | ON-1457 | Cheese
    02 | ON-1457 | Bread
    05 | ON-1590 | Milk
    ------------------------------

    So we know Paul has made an order (for Cheese & Bread), now how can we select (randomly) "James" or "Sean" as they have no orders?

    Does this make any sense?

    TIA.

  • #2
    Super Moderator
    Join Date
    May 2002
    Location
    Perth Australia
    Posts
    4,040
    Thanks
    10
    Thanked 92 Times in 90 Posts
    ok

    have not a clue how succsessfull this would be as RAND() can do strange things but anyway - assuming that orders.ID is NOT NULL

    SELECT members.*,orders.* FROM members LEFT JOIN orders ON orders.ID=members.ID where members.ID is NULL ORDER BY RAND();

    which should select all members whose ID does not appear in orders, but no promises as I have not actually tried it!
    resistance is...

    MVC is the current buzz in web application architectures. It comes from event-driven desktop application design and doesn't fit into web application design very well. But luckily nobody really knows what MVC means, so we can call our presentation layer separation mechanism MVC and move on. (Rasmus Lerdorf)

  • #3
    New Coder
    Join Date
    Jul 2002
    Location
    Cape Town, S Africa
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks firepages!

    That was almost right, I changed one or two things and it's working now. Excellent.

    Regards, Dodger.


  •  

    Posting Permissions

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