three way inner join

[BEFOR]

I can't seem to get this three table inner join to process.

Here are the tables/col's along with the statement:

EmpContacts - has backround info, contact info for companies.
EmpContacts.EmpID - is primary to this table

JobPosts - contains multiple postings for each of the EmpContacts.
JobPosts.JPID - is primary to this table

JCont - has contact info for each of the individual JobPosts
JCont.JID - is primary to this table
JCont.JP_ID - contains primary of JobPosts
JCont.E_ID -contains the primary of EmpContacts. This same id is used within the JobPosts table as foreign key, to link each job listing with ea. respective EmpContacts.

SELECT *
FROM EmpContacts INNER JOIN (JobPosts INNER JOIN JCont ON
JobPosts.JPID=JCont.JP_ID) ON EmpContacts.EmpID = JCont.E_ID;

---

Can anybody help? error message saying this isn't valid.

[tsinka]

Hi,

try this query instead:

SELECT *
FROM EmpContacts AS ec, JCont AS jc, JobPosts AS jp
WHERE ec.Emp_ID=jc.E_ID AND jc.JP_ID=jp.JPID

This is the short form of an inner join.

Thomas

[BEFOR]

Thank you tsinka. Works great. Currently with:

SELECT *
FROM EmpContacts AS ec, JCont AS jc, JobPosts AS jp

WHERE ec.EmpID=jc.E_ID AND jc.JP_ID AND jp.JPID = colname

Here are table/field names:

EmpContacts - has backround info, contact info for companies.
EmpContacts.EmpID - is primary to this table

JobPosts - contains multiple postings for each EmpContacts row.
JobPosts.JPID - is primary to this table
JobPosts.FK_ID - links to the primary EmpContacts.EmpID

JCont - has contact info for each of the individual JobPosts
JCont.JID - is primary to this table
JCont.JP_ID - contains the primary of JobPosts

JCont.E_ID -contains the primary of EmpContactsEmpID. This same id is used within jp.FK_ID, to link each job listing with ea. respective EmpContacts.

My question is about a way to use a SELECT statement based on a different value rather tha JobPosts.JPID = colname

please note: 'id-name' value, right below does equal jp.FK_ID, which does equal jc.E_ID which does equal ec.EmpID

I am trying to use along with the parameters:

colname -1 $HTTP_SESSION_VARS['id-name']

--Hope I've made clearer.

[tsinka]

The column names in your last post differ from the column names in your first post.
Please post again the current table/field names and describe a little bit more what exactly you want to do. I didn't get it completely.

Thomas

[BEFOR]

sorry, entered as an edit. please see right above

[tsinka]

What does colname -1 mean ?

[BEFOR]

colname -1 is for:

$colname_joinname = "-1";
if (isset($HTTP_SESSION_VARS['id-name'])) {
$colname_joinname = (get_magic_quotes_gpc()) ? $HTTP_SESSION_VARS['id-name'] : addslashes($HTTP_SESSION_VARS['id-name'])

[tsinka]

Ok 🙂

Try this:

SELECT * 
FROM EmpContacts AS ec, JCont AS jc, JobPosts AS jp 
WHERE ec.Emp_ID=jc.E_ID AND jc.JP_ID=jp.JPID AND jp.FK_ID='$colname_joinname'

SELECT * 
FROM EmpContacts AS ec, JCont AS jc, JobPosts AS jp 
WHERE ec.Emp_ID=jc.E_ID AND jc.JP_ID=jp.JPID AND jp.$colname_joinname=ec.Emp_ID

Edit: Sorry, won't work, I just now got what you want to do ... I'm working on it.

Edit2: If I got you right you store the column name in the session not the value. Try the second query.

Thomas

[tsinka]

Another point:

Make sure that you only execute the query if $colname_joinname contains a valid column name.

Thomas

[BEFOR]

'Edit2:'

Sure looks like a winner.

Much appreciate these efforts. Sorry I'll have to look at later in the day.

[BEFOR]

'tried Edit2'

Getting error 1054 'unknown column. in WHERE clause.

[tsinka]

Ok,

please post the code that does the query (including the query itself) and add a line like:

echo $colname_joinname;

to see what the value of this variable is.

Thomas

[BEFOR]

It won't even let me post it. (Dreamweaver MX)

[tsinka]

Post the code so I can see what might be the problem.

Thomas

[BEFOR]

<?php require_once('../Connections/teph.php'); ?>
<?php
$colname_njoins = "-1";
if (isset($HTTP_SESSION_VARS['id-name'])) {
  $colname_njoins = (get_magic_quotes_gpc()) ? $HTTP_SESSION_VARS['id-name'] : addslashes($HTTP_SESSION_VARS['id-name']);
}
mysql_select_db($database_teph, $teph);
$query_njoins = sprintf("SELECT * FROM EmpContacts AS ec, JConts AS jc, JobPosts AS jp WHERE ec.Emp_ID=jc.E_ID AND jc.JP_ID=jp.JPID=%s", $colname_njoins);
$njoins = mysql_query($query_njoins, $teph) or die(mysql_error());
$row_njoins = mysql_fetch_assoc($njoins);
$totalRows_njoins = mysql_num_rows($njoins);
?>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
</body>
</html>
<?php
mysql_free_result($njoins);
?>

[BEFOR]

Reader-coders might want to disregard this, its a slight adjust:

If we could back up to where I said "works great" No exactly -- you see its working for jp.JPID but not connecting with jc.JP_ID

SELECT *
FROM EmpContacts AS ec, JCont AS jc, JobPosts AS jp
WHERE ec.EmpID=jc.E_ID AND jc.JP_ID AND jp.JPID = colname

It was getting lost by the two "AND"s so I gave minor adjust of... "WHERE ec.EmpID=jc.E_ID AND jc.JP_ID = colname AND jp.JPID=colname

-That's all.