#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    1
    Rep Power
    0

    SELECT with INNER JOIN to display only one record


    I was wander whether someone could help me with a query problem I am having

    I have the following tables with data

    cart_session
    q_id userid itemid quanity datetime_cart
    126 1 10 1 2012-01-16 14:55:26
    125 1 23 1 2012-01-16 14:56:26

    shop_details
    s_id sessid firstname surname email address ordertype orderstatus postcode datetime
    20 1 Bob Smith bob@smith 145 Address Pay started xx21 x1 2012-01-16 14:56:26

    When I execute the below query I get the following resuls

    SELECT cart_session.Q_id, cart_session.userid, cart_session.itemid, cart_session.quanity, cart_session.datetime_cart ,shop_detail.s_id, shop_detail.sessid, shop_detail.firstname, shop_detail.surname, shop_detail.email, shop_detail.address, shop_detail.ordertype, shop_detail.orderstatus, shop_detail.postcode ,shop_detail.datetime FROM shop_detail INNER JOIN cart_session ON cart_session.userid=shop_detail.sessid
    Result
    q_id userid itemid quanity datetime_cart s_id sessid firstname surname email address ordertype postcode datetime
    126 1 10 1 2012-01-16 14:55:26 20 1 Bob Smith bob@smith 145 Address Pay started xx21 x1 2012-01-16 14:56:26
    125 1 23 1 2012-01-16 14:56:26 20 1 Bob Smith bob@smith 145 Address Pay started xx21 x1 2012-01-16 14:56:26

    But I only would like to get one top record. Do you think you would be able to help me with my problem.

    Kind Regards;

    Arek
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,450
    Rep Power
    1751
    Without an ORDER BY clause the concept of 'top' doesn't really mean a great deal as the order the data is returned is not guaranteed. What column would you use to define which is the top row in your example? Add an ORDER BY clause (probably DESC) on that column and a LIMIT 1 to only get the single row.
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc

IMN logo majestic logo threadwatch logo seochat tools logo