[Resolved] Subquery or Join? How to combine query results from multiple tables?

[sunshine66]

Hi,

I have multiple tables in a database and want to pull data from all of them to print info in an HTML table.

Print ALL dependents (and their profile info)
and related Employee info (name, health plan type) for dependent
and related Health Plan info (premium, Policy Number) for employee

For Example, print a table with the following 4 columns:
1. Dependent Name
2. Employee Name
3. Health Plan Provider Name, Premium & Policy Number
4. Dental Plan Provider Name, Premium & Policy Number

Here are the tables' structure:

---

dependentProfile

DependentID (auto-increment)
EmployeeID
CompanyID
DependentFirst
DependentLast

---

employeeProfile

EmployeeID (auto-increment)
CompanyID
EmployeeFirst
EmployeeLast
MaritalStatus
HealthPlan
HPlanType
DentalPlan
DPlanType

---

planProfile

PlanID (auto-increment)
CompanyID
ProviderName
PlanType
PolicyNumber
PremiumSingle
PremiumFamily
PremiumMarried
PremiumSingleParent

---

companyProfile

CompanyID (auto-increment)
CompanyName

---

userProfiles

ProfileID (auto-increment)
CompanyID

Initially I do a query that gets the CompanyID value based on who is logged into the system:

$query = "SELECT * FROM userProfiles,companyProfile WHERE userProfiles.CompanyID=companyProfile.CompanyID";
$result = mysql_query($query) or die("Error with query.");	
$row = mysql_fetch_array($result);
   //Get ID values
     if($row) {
	$CoID = $row["CompanyID"];
	$USERid = $row["ProfileID"];
      }

After that, I do not know what I'm doing, but it would be something like:

SELECT * FROM dependentProfile,employeeProfile,planProfile WHERE CompanyID=$CoID ORDER BY dependentProfile.DependentLast,dependentProfile.DependentFirst

I've already tried this, but it's not right. I think I need to do a subquery (IN statement?) or a JOIN, but honestly, I don't know the difference or how to do either one.
Suggestions are appreciated!

[tsinka]

Hi,

in which way isn't it right ? Your queries already use a JOIN ...

SELECT * FROM a,b WHERE is the short form of an INNER JOIN.

One thing to take care of is that this query only fetches records if there is data in all tables used by the query.

A LEFT JOIN might return more records containing NULL values for fields of tables that don't have matching data.

You second query should bail out with an MySQL error message because more than one table in the table list contains CompanyID. Besides that the WHERE clause seems to be a little bit too short. There are no parts dealing with the relation between the tables.

Thomas

[sunshine66]

Thanks for the JOIN/WHERE explanation.

Re: "There are no parts dealing with the relation between the tables. "
That is my point exactly... I don't know how to write the logic in one statement to get the results I want. Or how to structure the PRINT syntax

Let's start with the first two cases:

Case 1: Get ALL dependents (and their profile info) and print in a table - this is the easy part - I've got this to work

$query = "SELECT * FROM userProfiles,companyProfile WHERE userProfiles.CompanyID=companyProfile.CompanyID";
$result = mysql_query($query) or die("Query error: " . mysql_error() ); 
$row = mysql_fetch_array($result);
     if($row) {
	$CoID = $row["CompanyID"];
	}

$query2 = "SELECT DependentFirst,DependentLast 
FROM dependentProfile
WHERE CompanyID=$CoID ORDER BY DependentLast,DependentFirst";

$result2 = mysql_query($query2) or die("Error with query 2:" . mysql_error() );	

while($line = mysql_fetch_array($result2))
{
	$Dlast	 = $line["dependentProfile.DependentLast"];
	$Dfirst     = $line["dependentProfile.DependentFirst"];	
	$tableinfo .= "<tr>
	<td nowrap>$Dlast, $Dfirst</td>
	</tr>\n";
}

print "<table cellpadding=1 cellspacing=1 width=100% border=\"0\" bordercolor=\"#cccccc\">
       <tr><th align=\"left\" valign=\"bottom\">Name</th></tr>";
print "$tableinfo";
print "</table>";
print "<br>";

Now let's try the more difficult logic.

Case 2: Get ALL dependents (and their profile info just like above) .... and also get related Employee info (name, health plan type) for dependent... and print in a table by order of EmployeeLast, then EmployeeID (so that the employee and related dependents are grouped together in the table).

$query = "SELECT * FROM userProfiles,companyProfile WHERE userProfiles.CompanyID=companyProfile.CompanyID";
$result = mysql_query($query) or die("Query error: " . mysql_error() ); 
$row = mysql_fetch_array($result);
     if($row) {
	$CoID = $row["CompanyID"];
	}


$query2 = "$query2 = "    $query2 = "SELECT DependentFirst,DependentLast,DependentID,DependentType,EmployeeID,EmployeeFirst,EmployeeLast   

FROM dependentProfile,employeeProfile 
WHERE employeeProfile.EmployeeID=dependentProfile.EmployeeID AND 
(employeeProfile.CompanyID=$CoID OR dependentProfile.CompanyID=$CoID) 
ORDER BY dependentProfile.EmployeeID,employeeProfile.EmployeeID"; 

$result2 = mysql_query($query2) or die("Error with query2:" . mysql_error() );

while($line = mysql_fetch_array($result2))
{
	$eeID      = $line["employeeProfile.EmployeeID"];
	$Elast	 = $line["employeeProfile.EmployeeLast"];
	$Efirst     = $line["employeeProfile.EmployeeFirst"];	
	$dID      = $line["dependentProfile.DependentID"];
	$Dtype      = $line["dependentProfile.DependentType"];
	$Dlast	 = $line["dependentProfile.DependentLast"];
	$Dfirst     = $line["dependentProfile.DependentFirst"];

//if Dependent, print dependent info	
	if ( ($Dtype=="Spouse") || ($Dtype=="Minor") || ($Dtype=="Other"))
	 {  

	$tableinfo .= "<tr bgcolor=$color>
	<td nowrap>$Dtype: $dID</td>	
	<td nowrap>$Dlast, $Dfirst</td>
	</tr>\n";
	}

//Otherwise, if Employee, print employee info 
	else
	{
	$tableinfo .= "<tr bgcolor=$color>
	<td nowrap>Employee: $eeID</td>	
	<td nowrap>$Elast, $Efirst</td>
	</tr>\n";
	}
}

print "<table cellpadding=1 cellspacing=1 width=100% border=\"0\" bordercolor=\"#cccccc\">
       <tr>
	  <th align=\"left\" valign=\"bottom\">Type & ID </th>
	  <th align=\"left\" valign=\"bottom\">Name</th>
       </tr>";
print "$tableinfo";
print "</table>";
print "<br>";

Query 2 results in an error "'EmployeeID' in field list is ambiguous"
My ignorance prevents me from figuring out how to fix this. Can you help?

Also, For Case 2, I'm trying to figure out how to create a table that displays information in one of two ways:

Example 1: print a table with the following columns (this is what I'm attempting to in the code above):
1. Name (of employee or dependent)
2. Type : ID (of employee or dependent)

But also, how would print a table with the following columns (call this Example 2). How would the query and WHILE statements be different?
1. Dependent ID
2. Dependent Name
3. Employee ID
3. Employee Name

[tsinka]

Hi,

the field EmployeeID exists in both tables so MySQL doesn't know which one to choose.

You need to change the query to e.g.

SELECT ....,employeeProfile.EmployeeID,... FROM ...

In the WHERE clause you have

WHERE employeeProfile.EmployeeID=dependentProfile.EmployeeID 

Each record returned will have the same value in the EmployeeID field(s) so in this case a

ORDER BY dependentProfile.EmployeeID

is sufficient but reading your post again I suggest you might want

ORDER BY employeeProfile.EmployeeLast,employeeProfile.EmployeeID  

Post the table that is displayed by the modified code.

Thomas

[sunshine66]

Okay, I go the code to work for Case 2/Example 2 with a slight modification:
Print table with column data as follows:
Employee Name | Dependent Type | Dependent Name & ID

// Find dependent profiles for Company ID 
$query3 = "SELECT * FROM dependentProfile,employeeProfile 
	WHERE dependentProfile.CompanyID=$CoID AND employeeProfile.EmployeeID=dependentProfile.EmployeeID 
	ORDER BY dependentProfile.EmployeeID,dependentProfile.DependentFirst";
$result3 = mysql_query($query3) or die("Query 3 error : " . mysql_error() );  

$i = 0;
$c = 0;

while($row3 = mysql_fetch_array($result3))
{
	$Efirst   = $row3["EmployeeFirst"];	
	$Elast 	= $row3["EmployeeLast"];
	$eeID     = $row3["EmployeeID"];
	$dID      = $row3["DependentID"];
	$Dtype 	= $row3["DependentType"];
	$Dlast 	= $row3["DependentLast"];
	$Dfirst   = $row3["DependentFirst"];	

$tableinfo .= "<tr>
<td nowrap>$Efirst $Elast</td>
<td nowrap>$Dtype<br>$dID</td>	
<td nowrap>$Dlast, $Dfirst</td>
</tr>\n";
}

print "<table cellpadding=1 cellspacing=1 width=100% border=\"0\" bordercolor=\"#cccccc\">
       <tr>
	  <th align=\"left\" valign=\"bottom\">Employee</th>
	  <th align=\"left\" valign=\"bottom\">Dep.<br>Type</th>
	  <th align=\"left\" valign=\"bottom\">Dep. Name</th>
	  </tr>";

print "$tableinfo";
print "</table>";
print "<br>";

Now I need to figure out how to do a similar query but not print the Employee & Dependents in the same row, but different ones.
So the output would be something like:

Column 1 = Type ($dtype or 'Employee') | Column 2 = ID ($dID or $eeID) | Column 3=Name ($Efirst $Elast or $Dfirst $Dlast)

Employee | $eeID | $Efirst $Elast

$dtype | $dID | $Dfirst $Dlast

I know it has something to do with the $tableinfo variable
In the case of an Employee it would be:

	$tableinfo .= "<tr>
	<td nowrap>Employee</td>
	<td nowrap>$eeID</td>	
	<td nowrap>$Efirst $Elast</td>
 	</tr>\n";

And in the case of a Dependent it would be:

	$tableinfo .= "<tr>
	<td nowrap>$Dtype</td>
	<td nowrap>$dID</td>	
	<td nowrap>$Dlast, $Dfirst</td>
 	</tr>\n";

I'm at a loss as to how to approach the logic to combine the two to print in one table and group by Employee ID.

I'm sure this is a fairly easy solution for an experienced programmer!

[sunshine66]

I finally figured it out! Well, sort of...
This code works, but it only sorts dependents first, then employees, not both together. Posted sort problem separately (use SELECT list vs. table).
REF: http://www.phpbuilder.com/board/showthread.php?s=&threadid=10285128

$query2 = "SELECT * FROM dependentProfile WHERE CompanyID=$CoID AND COBRA='Yes'
	ORDER BY EmployeeID";
$result2 = mysql_query($query2) or die("Query 2 error : " . mysql_error() ); 

$query3 = "SELECT * FROM employeeProfile WHERE CompanyID=$CoID AND COBRA='Yes'
	ORDER BY EmployeeID";
$result3 = mysql_query($query3) or die("Query 3 error : " . mysql_error() );  

$i = 0;
$c = 0;

while($line = mysql_fetch_array($result2))
{
	$dID      = $line["DependentID"];
	$Dtype     = $line["DependentType"];
	$Dlast	 = $line["DependentLast"];
	$Dfirst     = $line["DependentFirst"];	

$tableinfo .= "<tr>
<td nowrap>$Dtype: $dID</td>	
<td nowrap>$Dlast, $Dfirst</td>
</tr>\n";
}


while($line = mysql_fetch_array($result3))
{
	$eeID      = $line["EmployeeID"];
	$Elast	 = $line["EmployeeLast"];
	$Efirst     = $line["EmployeeFirst"];	

$tableinfo .= "<tr>
<td nowrap>Employee: $eeID</td>	
<td nowrap>$Elast, $Efirst</td>
</tr>\n";
}

print "<table cellpadding=1 cellspacing=1 width=100% border=\"0\" bordercolor=\"#cccccc\">
       <tr>
	  <th align=\"left\" valign=\"bottom\">Type & ID </th>
	  <th align=\"left\" valign=\"bottom\">Name</th>
	  </tr>";

print "$tableinfo";
print "</table>";
print "<br>";

[dfr574]

I have the answer for you, but I'm having trouble following your database structure.

Here is a small database you can create and then the php code
that grabs the info in the form you like.

it should point you in the write direction.

db dump:

DROP TABLE IF EXISTS tbl1;
CREATE TABLE tbl1 (
user_first varchar(100) NOT NULL default '',
id int(11) NOT NULL default '0',
stat varchar(100) NOT NULL default '',
user_last varchar(100) NOT NULL default ''
) TYPE=MyISAM;

--

-- Dumping data for table tb1

/!40000 ALTER TABLE tb1 DISABLE KEYS /;
LOCK TABLES tbl1 WRITE;
INSERT INTO tbl1 VALUES ('David',1,'employee','Rogers'),('Bill',2,'employee','Helmick'),('Dan',3,'employee','Hines');
UNLOCK TABLES;
/!40000 ALTER TABLE tb1 ENABLE KEYS /;

--

-- Table structure for table tbl2

DROP TABLE IF EXISTS tbl2;
CREATE TABLE tbl2 (
dep_first varchar(100) NOT NULL default '',
id int(11) NOT NULL default '0',
stat varchar(100) NOT NULL default '',
dep_last varchar(100) NOT NULL default ''
) TYPE=MyISAM;

--

-- Dumping data for table tbl2

/!40000 ALTER TABLE tbl2 DISABLE KEYS /;
LOCK TABLES tbl2 WRITE;
INSERT INTO tbl2 VALUES ('Beth',1,'spouse','Rogers'),('Dirk',1,'dependent','Rogers'),('Gorgan',1,'dependent','Rogers'),('Alice',1,'dependent','Rogers'),('Steve',2,'dependent','Helmick'),('Susan',2,'spouse','Helmick'),('Jamie',3,'spouse','Hines'),('Jenifer',3,'dependent','Hines');

UNLOCK TABLES;

PHP code to retreive the info

$sql = "select concat(tbl1.user_last ,' ',  tbl1.user_first) as names1, tbl1.stat as empstat,  concat(tbl2.dep_last ,' ',  tbl2.dep_first) as names2, tbl2.stat as depstat "
 	."from tbl1, tbl2 "
	."where tbl1.id = tbl2.id "
	."order by tbl1.user_first";



$result = mysql_query($sql);


print "<table>\n";
$user3 = "XXXXX";
while($test = mysql_fetch_array($result)){

$user1 = $test['names1'];
$user2 = $test['names2'];
$stat1 = $test['empstat'];
$stat2 = $test['depstat'];

if($user3 != $user1){
	print "<tr>\n";
	print "<td>$user1</td><td>$stat1</td>\n";
	print "</tr>\n";
$user3 = $user1;
}
	print "<tr>\n";
	print "<td>$user2</td><td>$stat2</td>\n";
	print "</tr>\n";
}
print "</table>\n";