Joining Tables Problem

[saisankar_p]

Hi all,

I have a problem achieving the result. I want data as below which is based on 4 tables

Expected rasult

SiteID SiteName State 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

And the tables are as below

tbl_site

SiteID SiteName State PostCode Phone1
-------------------------------------------------------
1 Site1 1 2134 124 123
2 Site2 3 2323 124 234
3 Site3 5 7666 124 645
4 Site4 2 2888 124 894

tbl_state

StateID StateName
------------------------
1 ADC
2 WA
3 QTY
4 RKH
5 LDG

tbl_trial_site

TrialID SiteID
---------------------
1 1

tbl_user_site

UserID SiteID
---------------------
1 4

And the senario is the CanDelete column should be 1 if SiteID has references in any of the 2 tables (tbl_trial_site, tbl_user_site) it should be zero if non of the 2 tables has a reference to SiteID

Can any one please help me as i'm new to SQL and in greate need for help.

Thank you for your valuable time.

[tesuji]

Hello

If your tables are properly designed simple inner joins will solve your problem:

select * tbl_site join tbl_state join tbl_trial_site join tbl_user_site.

Ok, this result set will contain duplicate columns. To discard them you must replace the asterisk
by the list of column names you want to get.

Unfortunately, almost all tables aren't properly designed (because usually amateurs do table design never thinking of how to create functioning sql select statements) therefore some join constraints must be added to the above select statement.

btw, why did you put tbl_ to each object name? Isn't it kind of pleonasm 'cause everybody knows such objects can only be tables.

krs,
tesu

[tesuji]

Hello saisankar_p

Because of not having completely read your perfectly written post from begin to end, I presumed canDelete would already be part of one of your tables. I have to apologize to you for that bad mistake. Finally I have understand that you will compute canDelete depending whether tables tbl_trial_site or tbl_user_site have entries for the associate SiteID.

I have got this solution (it is easy to find, if one considers your complete question :) :

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
*/

This select statement works well on Sybase database what is the "first mother" of MS sql server.
Pls tell me whether it also works on MS sql server (There is a further solution dealing with it-then-else clause.)

tesu

[tesuji]

Hello saisankar_p

Just another solution:

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.

If you only want to know which rows could be deleted, then use first part only:

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)

tesu

[saisankar_p]

Hi tesuji,

Thanks for the reply. It is working perfectly as i needed. Sorry for the delay in reply, i was busy with some other project.

Regards, sankar