Click here to Skip to main content
14,335,747 members
   

Database

 
GeneralRe: User Access Database - Best practise Pin
Jörgen Andersson9-Nov-18 0:59
protectorJörgen Andersson9-Nov-18 0:59 
GeneralRe: User Access Database - Best practise Pin
Simon_Whale9-Nov-18 1:39
professionalSimon_Whale9-Nov-18 1:39 
GeneralRe: User Access Database - Best practise Pin
Eddy Vluggen9-Nov-18 5:49
mveEddy Vluggen9-Nov-18 5:49 
AnswerRe: User Access Database - Best practise Pin
Jörgen Andersson9-Nov-18 0:47
protectorJörgen Andersson9-Nov-18 0:47 
GeneralRe: User Access Database - Best practise Pin
Simon_Whale9-Nov-18 1:38
professionalSimon_Whale9-Nov-18 1:38 
GeneralRe: User Access Database - Best practise Pin
Jörgen Andersson9-Nov-18 1:46
protectorJörgen Andersson9-Nov-18 1:46 
GeneralRe: User Access Database - Best practise Pin
Simon_Whale9-Nov-18 1:50
professionalSimon_Whale9-Nov-18 1:50 
QuestionConditional statements in ON clause of joins Pin
simpledeveloper7-Nov-18 12:49
membersimpledeveloper7-Nov-18 12:49 
Hi all,

I have a joins select statement, what I want is, if I am having same tables in two left joins and with exclusive Data then in another table when we are joining Data in ON clause I want to be able to Join one or the other table to get the Data, maybe like a case statement but joining one table in condition over the other.

Here is the example:
<pre>DECLARE @AdminAddressTypeId int
SET @AdminAddressTypeId = (select top 1 AddType.PKAddressTypeLKPId from AddressTypeLKP AddType where AddType.Description = 'Administrative')
	     SELECT LE.LegalEntityName
	            ,LE.PKLegalEntityId
		       ,LE.FederalTaxId
		       ,LE.LegalEntityNbr
			  -- ,LE.LegalEntityBgnDt
			 --  ,LE.LegalEntityEndDt
			   -- ,LE.CreatedDate
			  -- ,LE.CreatedBy
			  -- ,LE.ModifiedDate
			 --  ,LE.ModifiedBy
			   -- ,OW.Description as OwnershipType
               --,OW.Code +'-'+ OW.Description as OwnershipType
				--,LE.FKOwnershipTypeLKPId
			   --,MGMT.Description as ManagementType
			   --,LE.FKManagementTypeLKPId
			   ,PRG.Description as ProgramType
			   ,LE.FKProgramTypeLKPId
			   --,C.County_Name as CountyName
			   ,C.PK_Geographic_Location_Code + '-' + C.County_Name as CountyName
			   ,LE.FKCountyLKPId
			   ,AA.StreetAddress1
			   ,AA.City
			   ,Admin_S.State_Name
			   ,AA.Zip1
			  -- ,LE.IsCompleted
			  --,LE.Comments
			   --,LET.Description as LegalEntityType		
	    FROM [dbo].[LegalEntity] LE
		--  left outer join [dbo].[OwnershipTypeLKP] OW ON LE.FKOwnershipTypeLKPId = OW.PKOwnershipTypeLKPId
		-- left outer join [dbo].[ManagementTypeLKP] MGMT ON LE.FKManagementTypeLKPId = MGMT.PKManagementTypeLKPId
		inner join  [dbo].[ProgramTypeLKP] PRG on LE.FKProgramTypeLKPId = PRG.PKProgramTypeLKPId and PRG.Description = 'Mental Health'
		left outer Join [dbo].County C on LE.FKCountyLKPId = C.PKCountyId
		--left outer join [dbo].[LegalEntityTypeLKP] LET on LE.FKLegalEntityTypeLKPId = LET.PKLegalEntityTypeLKPId
		Left outer join LegalEntityAddress LEA_Admin on LE.PKLegalEntityId = LEA_Admin.FKLegalEntityId and LEA_Admin.FKAddressTypeLKPId = @AdminAddressTypeId and LEA_Admin.IsValid = 1 and ( LEA_Admin.EffectiveDateTo is null or LEA_Admin.EffectiveDateTo > getdate())
		--(case when @AAEffectiveDateTo ='' then 0  else @AAEffectiveDateTo end)
        left Outer JOIN  Address AA ON LEA_Admin.FKAddressId = AA.PKAddressId
	    Left outer JOIN County Admin_CY on Admin_CY.PKCountyId = AA.FK_County
		Left outer join State Admin_S on Admin_S.PKStateId = AA.FK_State
		where LE.IsCompleted = 1	



What I want is I am checking for only LegalEntityAddress that are Admins, but if Admin Address is not available for that Legal Entity I want to display any address that has the latest closing Date, if it doesn't find any data for these two conditions then I want any first date that's available or the one with highest PK value.
If I can achieve this by having one time join of the table that will be nice if I can't get it by using one same table join or need same table to be joined multiple times, please help me how can I achieve it. If I need to have LegalEntityAddress multiple joins with different conditions then how can I handle that situation in further down tables which are using this LegalEntityAddress reference table to join.
Any help would be very helpful, thanks in advance.
AnswerRe: Conditional statements in ON clause of joins Pin
Mycroft Holmes7-Nov-18 20:25
memberMycroft Holmes7-Nov-18 20:25 
GeneralRe: Conditional statements in ON clause of joins Pin
simpledeveloper9-Nov-18 13:00
membersimpledeveloper9-Nov-18 13:00 
GeneralRe: Conditional statements in ON clause of joins Pin
Mycroft Holmes9-Nov-18 17:02
memberMycroft Holmes9-Nov-18 17:02 
GeneralRe: Conditional statements in ON clause of joins Pin
simpledeveloper11-Nov-18 17:28
membersimpledeveloper11-Nov-18 17:28 
QuestionLooking for a lightweight database to use for a vb win app Pin
jkirkerx7-Nov-18 12:01
professionaljkirkerx7-Nov-18 12:01 
AnswerRe: Looking for a lightweight database to use for a vb win app Pin
Nathan Minier8-Nov-18 1:08
professionalNathan Minier8-Nov-18 1:08 
GeneralRe: Looking for a lightweight database to use for a vb win app Pin
jkirkerx8-Nov-18 10:25
professionaljkirkerx8-Nov-18 10:25 
AnswerRe: Looking for a lightweight database to use for a vb win app Pin
--ZiM-- .11-Nov-18 8:36
member--ZiM-- .11-Nov-18 8:36 
QuestionBackup file is not restoring into Database even after giving message as restored successfully Pin
simpledeveloper7-Nov-18 7:40
membersimpledeveloper7-Nov-18 7:40 
QuestionIndex on nvarchar column doesnot affect performance (sql server) Pin
Heba Kamel23-Oct-18 1:29
memberHeba Kamel23-Oct-18 1:29 
AnswerRe: Index on nvarchar column doesnot affect performance (sql server) Pin
Richard Deeming23-Oct-18 1:58
mveRichard Deeming23-Oct-18 1:58 
GeneralRe: Index on nvarchar column doesnot affect performance (sql server) Pin
Heba Kamel23-Oct-18 2:03
memberHeba Kamel23-Oct-18 2:03 
GeneralRe: Index on nvarchar column doesnot affect performance (sql server) Pin
Richard Deeming23-Oct-18 2:04
mveRichard Deeming23-Oct-18 2:04 
Questionhow do i scan a image from a flat bed scanner using python and Microsoft access Pin
Member 1401815918-Oct-18 3:19
memberMember 1401815918-Oct-18 3:19 
Rant[REPOST] how do i scan a image from a flat bed scanner using python and Microsoft access Pin
Richard Deeming18-Oct-18 3:28
mveRichard Deeming18-Oct-18 3:28 
AnswerRe: how do i scan a image from a flat bed scanner using python and Microsoft access Pin
CHill6018-Oct-18 4:50
protectorCHill6018-Oct-18 4:50 
Generalthank you for your reply how you suggest i do it? Pin
Member 1401815918-Oct-18 20:52
memberMember 1401815918-Oct-18 20:52 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.