Results 1 to 2 of 2

Thread: Fetching rows based on another row from the same table

  1. #1

    Thread Starter
    Freelancer akhileshbc's Avatar
    Join Date
    Jun 2008
    Location
    Trivandrum, Kerala, India
    Posts
    7,650

    Question Fetching rows based on another row from the same table

    Hi,

    I don't know how to explain in words, but I believe it's best to explain my doubt using examples.

    Say, my table name is "tblSettings" and some dummy rows are like this:

    Code:
    rec_id	| settings_name	| settings_value	| reference_id
    ---------------------------------------------------------------
    1		| country		| India				| 32
    2		| state			| Kerala			| 32
    3		| country		| India				| 40
    4		| state			| Tamil Nadu		| 40
    5		| country		| America			| 61
    6		| state			| Los Angles		| 61
    Consider this as my scenario. Btw, discard the table design. It's not designed by me.
    So, I want to fetch all DISTINCT "state" values that belongs to the "country" named "India"

    In the table above, there are more than 65000 number of rows in total.

    Any idea on how I could I implement the query? I can't think straight now.


    If my post was helpful to you, then express your gratitude using Rate this Post.
    And if your problem is SOLVED, then please Mark the Thread as RESOLVED (see it in action - video)
    My system: AMD FX 6100, Gigabyte Motherboard, 8 GB Crossair Vengance, Cooler Master 450W Thunder PSU, 1.4 TB HDD, 18.5" TFT(Wide), Antec V1 Cabinet

    Social Group: VBForums - Developers from India


    Skills: PHP, MySQL, jQuery, VB.Net, Photoshop, CodeIgniter, Bootstrap,...

  2. #2

    Thread Starter
    Freelancer akhileshbc's Avatar
    Join Date
    Jun 2008
    Location
    Trivandrum, Kerala, India
    Posts
    7,650

    Re: Fetching rows based on another row from the same table

    I think I got it:

    Code:
    SELECT DISTINCT(`settings_value`) AS `values` FROM `tblSettings` WHERE `settings_name` = 'state' AND `reference_id` IN( SELECT `reference_id` FROM `tblSettings` WHERE `settings_name` = 'country' AND `settings_value` = 'India' )

    If my post was helpful to you, then express your gratitude using Rate this Post.
    And if your problem is SOLVED, then please Mark the Thread as RESOLVED (see it in action - video)
    My system: AMD FX 6100, Gigabyte Motherboard, 8 GB Crossair Vengance, Cooler Master 450W Thunder PSU, 1.4 TB HDD, 18.5" TFT(Wide), Antec V1 Cabinet

    Social Group: VBForums - Developers from India


    Skills: PHP, MySQL, jQuery, VB.Net, Photoshop, CodeIgniter, Bootstrap,...

Posting Permissions

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



Click Here to Expand Forum to Full Width