SELECT s.SiteID, SiteName, a.StateID, PostCode, Phone1, '-' AS '', StateName,
 CASE
   WHEN (SELECT t.SiteID FROM tbl_trial_site t WHERE t.SiteID = s.SiteID) 
             IS NOT NULL then 1
   WHEN (SELECT t.SiteID FROM tbl_user_site t WHERE t.SiteID = s.SiteID)
             IS NOT NULL then 1
   ELSE 0
 END AS canDelete
FROM tbl_site s JOIN tbl_state a
 
/* result
SiteID  SiteName  StateID  PostCode  Phone1     StateName   canDelete
-----------------------------------------------------------------------------------
1          Site1        1      2134  124 123 -  ADC                1
2          Site2        3      2323  124 234 -  QTY                0
3          Site3        5      7666  124 645 -  LDG                0
4          Site4        2      2888  124 894 -  WA                 1
*/

SELECT s.SiteID, SiteName, s.StateID, PostCode, Phone1, StateName, 
1 AS canDelete 
FROM tbl_site s, tbl_state a, tbl_user_site u, tbl_trial_site t
WHERE s.StateID = a.StateID 
AND (s.SiteID = u.SiteID OR s.SiteID = t.SiteID)
UNION
SELECT s.SiteID, SiteName, s.StateID, PostCode, Phone1, StateName, 
0 AS canDelete 
FROM tbl_site s, tbl_state a, tbl_user_site u, tbl_trial_site t
WHERE s.StateID = a.StateID 
AND (NOT(s.SiteID = u.SiteID OR s.SiteID = t.SiteID))
ORDER BY 1  -- ordered by s.siteID
-- order clause of union requires that columns be specified by their 
-- positions from in the result set.

SELECT s.SiteID, SiteName, s.StateID, PostCode, Phone1, StateName, 
1 AS canDelete 
FROM tbl_site s, tbl_state a, tbl_user_site u, tbl_trial_site t
WHERE s.StateID = a.StateID 
AND (s.SiteID = u.SiteID OR s.SiteID = t.SiteID)