Select query from multiple tables

[thara]

I have 6 tables and need to retrieve some data at once from those tables. can any body tell me, can I select my values from one query.

 

These values I needed.

 

institute_id

institute_name

city_name

options (relate to institute_id)

mediums (relate to institute_Id)

 

These are from my tables

 

CREATE TABLE institutes (
    institute_id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
    login_id INT(4) UNSIGNED NOT NULL,
    address_id INT(4) NOT NULL,
    contact_id INT(4) NOT NULL,
    institute_code INT(4) NOT NULL, 
    institute_name VARCHAR(80) NOT NULL, 
    institute_details VARCHAR(80) NOT NULL, 
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE address (
    address_id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
    address_one VARCHAR(40) NOT NULL, 
    address_two VARCHAR(40) DEFAULT NULL, 
    city_id INT(4) UNSIGNED NOT NULL,
    PRIMARY KEY (address_id),
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE city(
    city_id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
    city_name VARCHAR(30) NOT NULL, 
    state_id INT(2) UNSIGNED NOT NULL,
    PRIMARY KEY (city_id),
) ENGINE=MyISAM;

CREATE TABLE medium(
    medium_id INT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
    medium_name VARCHAR(30) NOT NULL, 
    PRIMARY KEY (medium_id),
) ENGINE=MyISAM;


CREATE TABLE options(
    option_id INT(2) UNSIGNED NOT NULL AUTO_INCREMENT,
    option_name VARCHAR(60) NOT NULL, 
    PRIMARY KEY (option_id),
) ENGINE=MyISAM;


CREATE TABLE institute_medium(
    im_id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
    medium_id INT(2) UNSIGNED NOT NULL,
    institute_id INT(4) UNSIGNED NOT NULL,
    PRIMARY KEY (im_id)
) ENGINE=MyISAM;

CREATE TABLE institute_option(
io_id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
option_id INT(2) UNSIGNED NOT NULL,
institute_id INT(4) UNSIGNED NOT NULL,
PRIMARY KEY (io_id)
) ENGINE=MyISAM;

 

those are my tables

 

I tried like this but Im confuse when Im try to get options and mediums.. there are always more than one option and medium in instutite_medium to one institute...

 

	  $q = "SELECT institutes.institute_id, institute_name, city_name, 
		DATE_FORMAT(registration_date, '%M %e, %Y') AS date,
		GROUP_CONCAT( medium_name SEPARATOR ', ') AS mediums
		FROM institutes
		INNER JOIN address ON institutes.address_id = address.address_id
		INNER JOIN city ON address.city_id = city.city_id
		inner join institute_medium on institute_medium.medium_id = medium.medium_id
		right join institutes on institutes.institute_id = institute_medium.institute_id
		ORDER BY registration_date DESC"; 	

 

but I cant get values...

 

any comments are greatly appreciated.

 

thank you...

 

[Barand]

... FROM institutes ... RIGHT JOIN institutes ...  ???

[thara]

thanks for response.. Actually I have any Idea about join query. I just use right join for my experiments... can u explain how its work...

 

any comments are greatly appreciated.

 

Thank you

[fenway]

Show us what output you're expecting.

[thara]

I want to display like this

 

"institute name" - "city name"

you have add you profile under "option1, option2, option3, and so forth" and teaching "medium1, medium2, etc" medium classes.

 

"registration date"

 

 

I need to display every registered institute on my home page like that.......

 

thans you..

[fenway]

Well, if you want to be lazy, you can use GROUP_CONCATS.