joining according to criterias

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

joining according to criterias

Author

Topic

dovesdale
Starting Member

11 Posts

Posted - 2007-05-02 : 06:16:04

In the below querry i joined a table called HA_HospitalGenFeatureList
but my condition is like if the above said table doest have value then u need to join another table called "IHA_hospitalsynopsisList".Which is not in the join given below

How can i do it.i think i cannot write CASE WHEN inside join statemts
or shall i use dynamic SQL?Plz give me a solution

SELECT DISTINCT MHD_CountryList.CountryID, MHD_CountryList.CountryName
FROM PA_ReqHospitalList INNER JOIN
HA_ReqPartnerList ON PA_ReqHospitalList.PartnerID = HA_ReqPartnerList.PartnerID AND
PA_ReqHospitalList.HospitalID = HA_ReqPartnerList.HospitalID INNER JOIN
IHA_HospitalList ON PA_ReqHospitalList.HospitalID = IHA_HospitalList.HospitalID INNER JOIN
MHD_CountryList ON IHA_HospitalList.CountryID = MHD_CountryList.CountryID INNER JOIN
IHA_LocationList ON IHA_HospitalList.LocationID = IHA_LocationList.LocationID AND MHD_CountryList.CountryID = IHA_LocationList.CountryID AND
IHA_HospitalList.CountryID = IHA_LocationList.CountryID INNER JOIN
HA_HospitalGenFeatureList ON PA_ReqHospitalList.HospitalID = HA_HospitalGenFeatureList.HospitalID
WHERE (PA_ReqHospitalList.IsActive = 1) AND (HA_ReqPartnerList.IsActive = 1) AND (PA_ReqHospitalList.IsAccepted = 1) AND
(HA_ReqPartnerList.IsAccepted = 1) AND (PA_ReqHospitalList.IsRejected = 0) AND (HA_ReqPartnerList.IsRejected = 0) AND
(IHA_HospitalList.IsHospitalActive = 1) AND (PA_ReqHospitalList.PartnerID = 1)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-02 : 06:26:58

I think you are facing such weird situation because your database design is not correct.

You can use IF EXISTS() to check whether record exists in HA_HospitalGenFeatureList and then based on the result, write queries for each scenario.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

dovesdale
Starting Member

11 Posts

Posted - 2007-05-02 : 06:34:26

but i dont pass the id to storeprocedure to check whther data is in the table
i use it only whle joining

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-02 : 06:41:12

"but i dont pass the id to storeprocedure to check whther data is in the table"

You don't need to!

You can just check whether any records materializes out of the join from two tables. If not, then join to second table.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

dovesdale
Starting Member

11 Posts

Posted - 2007-05-02 : 07:09:52

I think i got the answer
thanks formakingme think
Your advise was great but my scenario is different

I think i can get the reult by joining the two seperate queries joining seperate two tables

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-05-02 : 11:22:21

I'm not sure I fully understand what you are trying to achomplish, but it seems that you might be able to do a LEFT OUTER JOIN to the "IHA_hospitalsynopsisList" table and make use of COALESCE to get the correct data...? Just a thought.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources