Full Outer Joins - How to accomplish with my Query

Question

I have the following query:

SELECT
<output name="s.No" filter="y" title="School EMIS" data="text" width="50" />,
<output name="s.Name" title="School Name" filter="y" data="text" width="130" />,
<output name="s.Area" title="District" width="80" />,
<output name="m.month"  title="Month" width="60"  />,
<output name="t.yearid"  title="Year" width="40" />,
<output name="y.status"  title="Database Status" width="75" />,
<output name="j.name"  title="Updated By" width="60" />,
<output name="t.dateupdated"  title="Date Updated" width="60" hidden="y" />,
<output name="s.statusid"  title="School Submission Status" width="60"  hidden="y" />
FROM name s LEFT JOIN submissions t ON s.No = t.No
LEFT JOIN month m ON t.monthid = m.monthid
LEFT JOIN status y ON y.statusid = t.statusid
LEFT JOIN jos_users j ON t.user = j.id
WHERE statusid = <input title="Select Status" name="statusid"   
type="query" required="y"width="250" query="SELECT DISTINCT  CONCAT(status),statusid 
FROM status   
WHERE statusid = 1 ORDER BY status"  />
AND month = <input title="Select Month" name="monthid" type="query" required="y" 
width="250" query="SELECT DISTINCT CONCAT(month),monthid FROM month ORDER BY monthid" 
/>
AND year = <input title="Select Year" name="yearid" type="query" required="y" 
width="250" 
query="SELECT year FROM year ORDER BY Yearid" />
GROUP BY s.No
ORDER BY max(t.dateupdated) DESC

The query works fine. It lists all the details as per 3 SELECTS. What i need it to is too list all data from the 'name' and the 'submission' tables, listing the selected data from the 'submissions' table, but showing all other records where no data exists from the 'name' table

Does anybody know how i can achieve this? Please assist.

TABLE STRUCTURES BELOW:

 `Name` table:
 no  - int 11 (Primary Key)
 name - Varchar 255
 area - Varchar 255

 `Submissions` table:

 userid - int (11)
 statusid - int (11)  
 no - int (11) (Primary Key)     
 name - varchar (255)    
 area - varchar (255)    
 month - int (11) 
 year - int (11)    
 dateupdated - datetime (Primary Key)

In the Interim, the query below does show all the records, but then the SELECTS (Filters) do not work

I have the following query:

SELECT
<output name="s.No" filter="y" title="School EMIS" data="text" width="50" />,
<output name="s.Name" title="School Name" filter="y" data="text" width="130" />,
<output name="s.Area" title="District" width="80" />,
<output name="m.month"  title="Month" width="60"  />,
<output name="t.yearid"  title="Year" width="40" />,
<output name="y.status"  title="Database Status" width="75" />,
<output name="j.name"  title="Updated By" width="60" />,
<output name="t.dateupdated"  title="Date Updated" width="60" hidden="y" />
FROM name s LEFT JOIN submissions t ON s.No = t.No
LEFT JOIN month m ON t.monthid = m.monthid
LEFT JOIN status y ON y.statusid = t.statusid
LEFT JOIN jos_users j ON t.user = j.id
WHERE s.no NOT IN (
SELECT status
FROM status
WHERE statusid = <input title="Select Status" name="statusid"   
type="query" required="y"width="250" query="SELECT DISTINCT  CONCAT(status),statusid 
FROM status   
WHERE statusid = 1"  />
)
AND s.no NOT IN (
SELECT month
FROM month
WHERE month = <input title="Select Month" name="monthid" type="query" required="y" 
width="250" query="SELECT DISTINCT CONCAT(month),monthid FROM month ORDER BY monthid" 
/>
)
AND s.no NOT IN (
SELECT year
FROM year
WHERE year = <input title="Select Year" name="yearid" type="query" required="y" 
width="250" 
query="SELECT year FROM year ORDER BY Yearid" />
)
GROUP BY s.No
ORDER BY max(t.dateupdated) DESC

I have partially resolved the issue. I am now able to see fields. I used the following query, I created extra NULL columns in the 'name' table to achieve this:

SELECT
<output name="submissions.user" title="User" width="80" />,
<output name="submissions.statusid" title="Status" width="80" />,
<output name="submissions.no"title="no" width="60"  />,
<output name="submissions.Name"  title="School Name" width="40" />,
<output name="submissions.area"  title="District" width="75" />,
<output name="submissions.monthid"  title="Month" width="60" />,
<output name="submissions.yearid"  title="Year" width="60" />,
<output name="submissions.dateupdated"  title="Date Updated" width="60" />
FROM submissions 
RIGHT OUTER JOIN name ON submissions.no = name.no
WHERE submissions.statusid = <input title="Select Status" name="submissions.statusid" 
type="query" required="y" width="250" query="SELECT DISTINCT CONCAT(status),statusid
FROM status WHERE statusid = 1 ORDER BY status"  />
AND submissions.monthid = <input title="Select Month" name="submissions.monthid"      
type="query" required="y" width="250" query="SELECT DISTINCT CONCAT(month),monthid
FROM month ORDER BY monthid" />
AND submissions.yearid = <input title="Select Year" name="submissions.yearid" 
type="query" required="y" width="250" query="SELECT year FROM year ORDER BY Yearid" />

UNION 

SELECT
<output name="name.user" title="User" width="80" />,
<output name="name.statusid" title="Status" width="80" />,
<output name="name.no" filter="y" title="no" data="text" filtertype="grid" width="50"
/>,
<output name="name.Name" title="School Name" filter="y" data="text" width="130" />,
<output name="name.area" title="District" width="80" />,
<output name="name.monthid" title="Month" width="80" />,
<output name="name.yearid" title="Year" width="80" />,
<output name="name.dateupdated" title="Date Updated" width="80" />
FROM name
LEFT OUTER JOIN submissions ON name.no = submissions.no

The only thing i am now battling to do is to GROUP and ORDER BY

GROUP BY name.No
ORDER BY max(name.dateupdated) DESC

How would i include this in my query?

Answer 1

I tried to decipher your query as best as possible and it is very ugly to look at. However, let me try to summarize what it APPEARS you might be looking for (starting with the first original query). Since you are doing a group by a school name, you want the latest activity submission date record info. From what appeared to be some XML formatted querying constructs, it appears you only wanted submitted status of 1. Your other "input / query" were essentially joining between the tables to get the lookup descriptions from the other tables, such as month (via MonthID), year (YearID), status (statusID), etc. I have simplified these into more normal query recognition that anyone else would be accustomed to seeing. I changed the aliases to better correlate with the original tables "n" for the (School)Name table, "s" for the Submission table, "m" for month, "y" for Year, "j" for JOS_users (which could have been u-users).

I left them as LEFT-JOIN as you stated you wanted all names REGARDLESS of any submissions on file for the school.

Now, the prequery. Here, I ran the query for exclusively grouped by the school "No" and the max "dateUpdated", but only where the status was 1. This gets rid of duplications across the board per school... One record MAX per any individual school.

From this PreQuery alias, I use that to join to the actual submission table on the school "No" to continue getting the rest of the details associated to the submission based on that maximum date.

SELECT
      n.No SchoolEMIS,
      n.Name SchoolName,
      n.Area District,
      m.month,
      y.Year,
      s.statusID SchoolSubmissionStatus,
      st.status DatabaseStatus,
      j.name UpdatedBy,
      PreQuery.MaxUserDate dateUpdated
   from
      name n
         left join ( select s1.No,
                            MAX( s1.dateupdated ) as MaxUserDate
                         from
                            submissions s1
                         where
                            s1.statusID = 1
                         group by
                            s1.No ) preQuery
            ON n.No = PreQuery.No
            left join submissions s
               ON PreQuery.No = s.No
               AND PreQuery.MaxUserDate = s.dateupdated
               left join month m
                  ON s.monthid on m.monthid
               left join year y
                  ON s.yearid = y.yearid
               left join status st
                  ON s.statusid = st.statusid
               left join jos_users j
                  ON s.userid = j.id
   order by
       PreQuery.MaxUserDate DESC

One possible modifier I see though. If there are multiple submission records for a given school with a status of 1, and you want to see ALL entries for the school of status 1, then just change the

AND PreQuery.MaxUserDate = s.DateUpdated

to

AND s.status = 1

The school number will already be associated for their respective open submitted entries.

As for the column output types as hidden=y, filter=y, etc, once you have the data, your output mechanism should apply (show or not) the data.

Hopefully this is a descent assessment of your original query context.