select from 2 tbl where feild is comman

[Jaunty Edward]

Hi,
I am confused with this query I have to write a query that will select
data from many tables having a username field where username is given.

I used something like,

select * from tbl1,tbl2
where username = 'user'

but it gives a result with all the fields of one to the second table.

what to do, if table1 has 5 records with the given user and table 2
has 10 then I want only 15 results but the script is giving me lots of
results.

Please currect my query, I have to deal in 8 tables and I am having
problem in 2.

regards
Jaunty Edward

[Aggro]

Jaunty Edward wrote:

Hi,
I am confused with this query I have to write a query that will select
data from many tables having a username field where username is given.

I used something like,

select * from tbl1,tbl2
where username = 'user'

Normally you have something like this:

------------
| tbl1 |
------------
| id |
| username |
------------

------------
| tbl2 |
------------
| id |
| tbl1_id |
| some |
| other |
| fields |
------------

tbl1.id is linked to tbl2.id, so you want to select one row from tbl1
and all rows from tbl2 that match to that:

select * from tbl1,tbl2
where tbl1.username = 'user'
and tbl1.id = tbl2.tbl1_id;

[Jaunty Edward]

Thanks Aggro,
but I don't have any feild such as tbl1_id in the table2. Please look
at my DB structure for a better picture.

CREATE TABLE `dating` (
`id` int(5) unsigned NOT NULL auto_increment,
`cat_id` tinyint(3) unsigned NOT NULL default '0',
`cat_name` varchar(60) NOT NULL default '',
`subcat_id` tinyint(3) unsigned NOT NULL default '0',
`sub_cat` varchar(60) NOT NULL default '',
`username` varchar(30) default NULL,
`sex` varchar(15) default NULL,
`age` tinyint(3) unsigned default NULL,
`email` varchar(30) default NULL,
`title` varchar(255) default NULL,
`message` text,
`date` date NOT NULL default '0000-00-00',
`time` time NOT NULL default '00:00:00',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=5 ;
CREATE TABLE `real_estate` (
`id` int(5) unsigned NOT NULL auto_increment,
`cat_id` tinyint(3) unsigned NOT NULL default '0',
`cat_name` varchar(60) NOT NULL default '',
`subcat_id` tinyint(3) unsigned NOT NULL default '0',
`sub_cat` varchar(60) NOT NULL default '',
`rent` varchar(30) default NULL,
`bhk` varchar(10) default NULL,
`username` varchar(30) default NULL,
`sex` varchar(15) default NULL,
`email` varchar(30) default NULL,
`msgheading` varchar(255) default NULL,
`message` text,
`price` varchar(10) NOT NULL default '0',
`address` varchar(60) NOT NULL default '',
`pincode` varchar(6) NOT NULL default '0',
`near_rlstation` varchar(30) NOT NULL default '',
`date` date NOT NULL default '0000-00-00',
`time` time NOT NULL default '00:00:00',
`sqfeet` int(10) unsigned default NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=21 ;

these are the 2 tables that have username and email comman.

Please help as I have to extract data from 7 such tables in one query.

Regards
Jaunty Edward

[Lüpher Cypher]

Jaunty Edward wrote:

Thanks Aggro,
but I don't have any feild such as tbl1_id in the table2. Please look
at my DB structure for a better picture.

CREATE TABLE `dating` (
`id` int(5) unsigned NOT NULL auto_increment,
`cat_id` tinyint(3) unsigned NOT NULL default '0',
`cat_name` varchar(60) NOT NULL default '',
`subcat_id` tinyint(3) unsigned NOT NULL default '0',
`sub_cat` varchar(60) NOT NULL default '',
`username` varchar(30) default NULL,
`sex` varchar(15) default NULL,
`age` tinyint(3) unsigned default NULL,
`email` varchar(30) default NULL,
`title` varchar(255) default NULL,
`message` text,
`date` date NOT NULL default '0000-00-00',
`time` time NOT NULL default '00:00:00',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=5 ;
CREATE TABLE `real_estate` (
`id` int(5) unsigned NOT NULL auto_increment,
`cat_id` tinyint(3) unsigned NOT NULL default '0',
`cat_name` varchar(60) NOT NULL default '',
`subcat_id` tinyint(3) unsigned NOT NULL default '0',
`sub_cat` varchar(60) NOT NULL default '',
`rent` varchar(30) default NULL,
`bhk` varchar(10) default NULL,
`username` varchar(30) default NULL,
`sex` varchar(15) default NULL,
`email` varchar(30) default NULL,
`msgheading` varchar(255) default NULL,
`message` text,
`price` varchar(10) NOT NULL default '0',
`address` varchar(60) NOT NULL default '',
`pincode` varchar(6) NOT NULL default '0',
`near_rlstation` varchar(30) NOT NULL default '',
`date` date NOT NULL default '0000-00-00',
`time` time NOT NULL default '00:00:00',
`sqfeet` int(10) unsigned default NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=21 ;

these are the 2 tables that have username and email comman.

Please help as I have to extract data from 7 such tables in one query.

Regards
Jaunty Edward

Just do it as

select dating.some_field,real_estate.some_field,... from
dating,real_estate,... where dating.username = "some_name" and
dating.username = real_estate.username and real_estate.username = ...

Basically, select all the fields you need from thier tables, match one
table's username to a given string, and then make sure the username
field in all tables is the same.