All,

First Seasons Greetings to all at this group.

I have a problem that I'm sure is due to my lack of knowledge about advanced SQL queries and not really directly to MySQL.

I'm attempting to perform a Left Join on a set of table in a MySQL database for use on displaying a Web page. Note that this has nothing to do with the PHP or Web page creation. In other words the SQL query does not provide the desired results from running under a mysql shell.

Here are the table details.

Table 1 = Hosttable ID Integer(11)(Primary) DestHost char(50) (Index) GroupName char(50) (Index) HostType Integer(11) Username char(50) Password char(50)

This table contains unique entries by 'DestHost' for external FTP addresses. Actually the UNIQUE entry is from the combination of 'DestHost' + 'GroupName'

Table 2 = HostTypes HostType Integer (Primary)(Match 'HostType' column in HostTable) HostTypeDescription char(255)

This table contains a simple description of what type of host we are working with. Examples are; HostType = 1; HostTypeDescription = 'External Host' HostType = 2; HostTypeDescription = 'Internal Host'

Table 3 = SecurityHostnameAccounts AccountName char(50) (Index) Hostname char(50) (Index)(Match on 'DestHost' from HostTable MaintLevel Integer(11)

This table can an should contain non-unique entries for accounts and what 'HostTable' entries they will be allowed to edit and use. The column 'MaintLevel' defines this. If set to '1'= Use Only, 2= Edit and Use.

Basically I have a pass-thru FTP server that is situated in the DMZ of our network. We do not allow direct FTP to the Internet. In my process of learning PHP I discovered the ability of FTP from an application WebFTP. After speaking to the security folks we agreed this would be a great tool since we have so many request to FTP to remote systems. What was needed was so way to grant access to specific hostnames available in the 'HostTable' by a general user. This action would be performed by an Admin level account. The Admin is first presented with a drop-down on the Web page that contains the entire list of managed accounts. He/She selects and account and a query is formulated to build a result web page that should contain ALL 'HostTable' entries. Beside each line is a check-box. The check-box allows the admin to select or de-select specific host entries the Account can use etc. The Admin then submits the form and the table 'SecurityHostnameAccounts' is populated with the row for that account and each of its host entries. This table will contain multiple entries per account for each Hostname entry allowed.

So a little example, Let's say the 'HostTable' contains the entries ID DestHost GroupName HostType Username Password 1 Host1 Group1 1 none none 2 Host2 Group1 1 none none 3 Host3 Group1 1 none none 4 Host4 Group1 1 none none 5 Host5 Group1 1 none none 6 Host6 Group1 1 none none 7 Host7 Group1 1 none none 8 Host8 Group1 1 none none 9 Host9 Group1 1 none none 10 Host10 Group1 1 none none

'HostTypes' table contains HostType HostTypeDescription 1 "External Host entry"

'SecurityHostnameAccounts' is currently EMPTY

I run this query

SELECT H.ID, H.DestHost, HT.HostTypeDescription, SHA.AccountName, SHA.MaintLevel FROM HostTable AS H LEFT JOIN HostTypes AS HT ON H.HostType=HT.HostType LEFT JOIN SecurityHostnameAccounts AS SHA ON H.DestHost=SHA.Hostname WHERE H.GroupName='Group1' AND (SHA.AccountName='Account1' OR SHA.AccountName IS NULL)

If the 'SecurityHostnameAccounts' is EMPTY then the query returns the expected results of ALL of the 'HostTable' rows which is expected.

Let's say for 'Account1' we select 'Host1' as an allowed use. We then populate in 'SecurityHostnameAccounts' a row of

SecurityHostnameAccounts AccountName Hostname MaintLevel 'Account1' 'Host1' 1

Now the admin selects 'Account2' from the drop-down. The query is now

SELECT H.ID, H.DestHost, HT.HostTypeDescription, SHA.AccountName, SHA.MaintLevel FROM HostTable AS H LEFT JOIN HostTypes AS HT ON H.HostType=HT.HostType LEFT JOIN SecurityHostnameAccounts AS SHA ON H.DestHost=SHA.Hostname WHERE H.GroupName='Group1' AND (SHA.AccountName='Account2' OR SHA.AccountName IS NULL)

What is returned from the query is all of the rows except for a row for 'Host1'. Since each account will contains their own reference to a 'HostTable' entry in the Security database I need every entry returned. So you may ask why I don't simply SELECT all from the 'HostTable'? Because I want to display to the Admin via the Web page what entries are already defined for this account. So as a part of the PHP processing on the query result if the 'SHA.AccountName' returned is 'NULL' from the LEFT JOIN then I assume no entry was found and the check-box on the Web page is unchecked. If the returned value of 'SHA.AccountName' is not NULL then the check-box is set.

I have tried this same query minus the WHERE clause 'AND (SHA.AccountName='Account2' OR SHA.AccountName IS NULL)' this will result in the return of all rows in the 'HostTable' and match or NULL rows in the SHA Security table. I could filter through each returned row to check the AccountName. Problem is if I have 10 Account entries in my 'AccountTable' (Not shown in this email) and each is allowed to use all 10 entries in the 'HostTable' then the Security table contains 100 rows. More realisticly I have 300 accounts and over 600 'HostTable' entries. I don't want to filter through 180000 rows. I want to find a way SQL can do this for me.

Any ideas on what is incorrect with my SQL statement?

FPM