displaying data with relational database tables

I have a page that is displaying data from 3 tables in a database using sql joins. I have a 'members' table, a 'properties' table and a 's 'enquiry' table. I am using the 'members' table primary key as a Membership number for the user. It is also a foreign key in the 'enquiries' table. incidentally the 'properties table's primary key (property_id) is a foreign key in the 'members' table.

The problem I have is that the 'members_id' binding only displays when there is an enquiry made by the user, which will in hand display on this page. But if the user in session has not made any enquiries, the 'members_id' binding does not show.

I kind of understand that this is because of my query ( recordset ) but I am not sure where or how to adjust this.

Attached is the current page.

it is because of the join type you are using.

seer this page for details on the different join types and how the function:
http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

Thanks Jason!

you're welcome.

Hi Jason! I still can't grasp what to do here because I have three tables to join. The blog is helpful but It only covers using joins with two tables!

joining three tables is not much different than joining 2 tables

SELECT * from table1
<join type> table2 ON <relation columns from 2 tables>
<join type>table3 ON <relation columns from 2 tables>

the blog i linked to describes the different join types and how the effect the results. the join type yo are using in the query is causing the member ID to not return, try experimenting with the different join types.

Thanks Jason. I think whats confusing the matter is the where clause which gets the members id from the session, if that makes sense. I get the impression you would rather I figure this out for myself! if I was to ask you for the answer to my problem would you be able to give me the answer?

in the recordset, you are using INNER JOIN and LEFT JOIN as the join types.

Change the join types to be more inclusive


from the page i linked to:

"Inner join produces only the set of records that match in both Table A and Table B."

meaning that if there is no match on both sides of the join, a result is not returned

"Full outer join produces the set of all records in Table A and Table B, with matching records from both sides where available. If there is no match, the missing side will contain null."

meaning that a result is returned whether there is a match or not.

Jason,

I was first trying to use FULL OUTER JOIN only to discover you can't use these with MySQL and have to use UNION and UNION ALL with LEFT and RIGHT JOIN. I have tried this and I can not seem to get the results I am looking for as it does not seem to filter the results properly for the user in SESSION.

Here is the query I have tried.

$getID_rsSummary = "-1";
if (isset($_SESSION['members_id'])) {
$getID_rsSummary = $_SESSION['members_id'];
}
mysql_select_db($database_LA, $LA);
$query_rsSummary = sprintf("SELECT * FROM members LEFT JOIN properties ON members.property_id = properties.property_id LEFT JOIN enquiries ON members.members_id = enquiries.members_id UNION ALL SELECT * FROM members RIGHT JOIN properties ON members.property_id = properties.property_id LEFT JOIN enquiries ON members.members_id = enquiries.members_id UNION ALL SELECT * FROM members RIGHT JOIN properties ON members.property_id = properties.property_id RIGHT JOIN enquiries ON members.members_id = enquiries.members_id WHERE members.members_id = %s ", GetSQLValueString($getID_rsSummary, "int"));
$rsSummary = mysql_query($query_rsSummary, $LA) or die(mysql_error());
$row_rsSummary = mysql_fetch_assoc($rsSummary);
$totalRows_rsSummary = mysql_num_rows($rsSummary);

Any pointers? ;-)

union merges the results of 2 queries, you should not be using union, it is not the same as a join.

go back to the original query i will need to troubleshoot directly, see the private message section.