Using keyes stored in one table to extract data from others

[aktar]

I have two related tables:

1) personal_detail s
2) company_details

both having "ID" as a primary key

I also have another table called mail. The mail table contains:

MailID primary key
SenderID, realted to the primary key of the above two tables
RecepientID, also related to the primary key of the above two tables

ONLY MailID, the primary key of mail, is known. And the aim of the game is to extract two rows of information from personal_detail s and company_details matching the SenderID and RecepientID contained in the mail table


PS. This is not a school project. I've been pulling out my hair for the last 3 months trying to find a solution to this little problem

[ronverdonk]

Two rows can only happen when the recepient_id and sender_id in each row of the two tables is identical. Because if not, you'll probably get 4 records.

I'd like to see the table design on these tables.

Ronald

[aktar]

Two rows can only happen when the recepient_id and sender_id in each row of the two tables is identical. Because if not, you'll probably get 4 records.

I'd like to see the table design on these tables.

Ronald

what i meant was [PHP]select ...
FROM personal_detail s, mail
LEFT JOIN company_details ON (SenderID from the mail table = ID or the RecepientID from the mail table = ID)
WHERE mail.MailID = the known variable
[/PHP]
So the expected result is two rows, one containing the senders details and the other containing the recepients details

Here is the actual design

This is the "mail".
The ID and RecepientID are both relational to the ID of the later two tables
[code=mysql]
CREATE TABLE `mail_threads` (
`ThreadID` int(11) NOT NULL auto_increment,
`ID` int(10) NOT NULL,
`RecepientID` int(10) NOT NULL,
`Nature_of_cont act` varchar(40) default NULL,
`Read` set('Yes','No') NOT NULL default 'No',
PRIMARY KEY (`ThreadID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT= 14 ;
[/code]
This is the "company_detail s"
[code=mysql]
CREATE TABLE `member_contact ` (
`ID` int(11) NOT NULL,
`Company_name` varchar(50) NOT NULL,
`First_line` varchar(30) NOT NULL default '',
`Second_line` varchar(30) NOT NULL default '',
`Town` varchar(30) default NULL,
`City` varchar(30) default NULL,
`County` varchar(30) NOT NULL default '',
`Postcode` varchar(10) NOT NULL default '',
`Country` int(3) NOT NULL,
`Phone` varchar(20) default NULL,
`Fax` varchar(20) default NULL,
`Mobile` varchar(20) default NULL,
`Email` varchar(50) NOT NULL default '',
`Website` varchar(40) default NULL,
`Other_details` varchar(255) default NULL COMMENT 'Other details can include things like your operation hours, alternative locations etc',
PRIMARY KEY (`ID`),
UNIQUE KEY `Email` (`Email`),
FULLTEXT KEY `Company_name` (`Company_name` )
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Compan ys contact details';[/code]This one is the "personal_detai ls"
[code=mysql]
CREATE TABLE `registrar` (
`ID` int(10) NOT NULL auto_increment,
`Date` datetime default NULL,
`Title` set('Mr','Miss' ,'Ms','Mrs','Dr ','Prof','Md',' Sir','Lord') NOT NULL default 'Mr',
`First_name` varchar(20) NOT NULL,
`Surname` varchar(20) NOT NULL,
`Email` varchar(40) NOT NULL,
`Position` varchar(40) default NULL,
`Password` varchar(50) NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `Email` (`Email`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Detail s of teh person who registers the company' AUTO_INCREMENT= 145 ;[/code]

[logicsachin]

query the mail table using the known threadID and select recepientID and senderID into two variables x and y

use these $x and $y as runtime variables in the select query to extract rows from personal and company tables

makes sense?

I could even write a stored procedure to do this and just call the procedure with the "threadID" as the IN parameter.

[logicsachin]

Two rows can only happen when the recepient_id and sender_id in each row of the two tables is identical. Because if not, you'll probably get 4 records.

I'd like to see the table design on these tables.

Ronald

Hi,
As per my understanding, for the recepient ID there will be one row (containing fields from both personal table and comp table)
and for the senderID also there will be one row.
-sachin

[aktar]

Hi,
As per my understanding, for the recepient ID there will be one row (containing fields from both personal table and comp table)
and for the senderID also there will be one row.
-sachin

exactly what I'm looking for

[ronverdonk]

Next time you are around enclose your posted code in [code] tags (See How to Ask a Question).

This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.

Please use [code] tags in future.

MODERATOR

[aktar]

Hi Guys,

I have found a solution to the problem, but I'm still not sure if this is the most efficient way of writing my query.
Here it is:


[CODE=mysql]SELECT * FROM registrar, member_contact, mail_threads
WHERE registrar.ID = member_contact. ID

AND

( mail_threads.ID = registrar.ID OR mail_threads.Re cepientID = registrar.ID )

AND

mail_threads.Th readID = 1[/CODE]