Outer Join issues - Database Discussion Boards

I think you can't retrieve client IP's trying to connect to your DB.You could query net_address from sys.sysprocesses which is unique identifier for the network adapter on the workstation of each user ,but how to refer it to user IP I don't know. Confused | :confused:

Hi all,

how to check the existance of user name from Sql server with case sensitivity.

You're not the first to ask such question[^] ...

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles]

The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Show formatted code inside PRE tags, and give clear symptoms when describing a problem.

I would like to change my default port 1433 for remote connection to some another port? What port number can give as another port? Is there any rule? How to change the default port?

Iam using sql server 2008.

Any Ideas and Guidences?

Thanks

From SQL Configuration Manager, Just need to change the tcp/ip port number as anyone new number.
Let it be useful to someone like me.

I Want to display more than one rows data in just one row.

My Data is:

Code Name
50141 abc
50141 pqr

I want Following Data in result.

Code name
50141 abc,pqr

Please Give me a solution.

Thanks & Regards,
Fazal

Create Function to return the all names(seperated by comma) for a given code.
Create FUNCTION [dbo].[GetNamesById]
(
@code int
)
RETURNS varchar(max)
AS
BEGIN
declare @result varchar(max)
select @result = COALESCE(@result + ',', '') + [name]
from tblCode
where code = @code

return @result
END
GO

now use following query:
select code,dbo.GetNamesById(code) from tblCode group by code

above query will give output as:
Code name
50141 abc,pqr

I have two tables that share two columns:

Table 1:

Name: EMR.mmPatientAdmissionSymptoms
Columns: PatientID, AdmittanceID,SymptomID

Table 2:

Name: EMR.mmPatientAdmissionSymptomsOther
Columns: PatientID, AdmittanceID,Symptom

EMR.mmPatientAdmissionSymptoms.SymptomID is an integer whilst EMR.mmPatientAdmissionSymptomsOther.Symptom is nvarchar(MAX).

Now, I want to pull all the data in both tables, where the data in either table not existing being null. That should be a straight full outer join, right?

SELECT	
	pas.PatientID, pas.AdmittanceID, pas.SymptomID, paso.Symptom
FROM         EMR.mmPatientAdmissionSymptoms as pas full outer join
                      EMR.mmPatientAdmissionSymptomsOther as paso 
                      on pas.PatientID = paso.PatientID
                      AND pas.AdmittanceID = paso.AdmittanceID

But it doesn't; It seems to preform a cross join. Out of frustration I tried left, right, cross and inner joins just to see if I'm on the right track or not. They ALL give the same result

Scripts if you want to try this: (altered slightly so you don't have to remove the FK and Schemas)

-- Table 1
CREATE TABLE [mmPatientAdmissionSymptoms](
	[PatientID] [char](10) NOT NULL,
	[AdmittanceID] [int] NOT NULL,
	[SymptomID] [int] NOT NULL,
 CONSTRAINT [PK_mmPatientAdmissionSymptoms] PRIMARY KEY CLUSTERED 
(
	[PatientID] ASC,
	[AdmittanceID] ASC,
	[SymptomID] ASC
)

--Table 2
CREATE TABLE [EMR].[mmPatientAdmissionSymptomsOther](
	[PatientID] [char](10) NOT NULL,
	[AdmittanceID] [int] NOT NULL,
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Symptom] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_mmPatientAdmissionSymptomsOther] PRIMARY KEY CLUSTERED 
(
	[PatientID] ASC,
	[AdmittanceID] ASC,
	[ID] ASC
)
--ID is an Identity field so that I can differentiate between one entry and the other for each patient at each admittance

Results are returned as:

PatientID AdmittanceID SymptomID Symptom
============================================================
1234 1 1 This is a symptom
1234 1 3 This is a symptom

Table 1:

PatientID AdmittanceID SymptomID
==============================================
1234 1 1
1234 1 3

Table 2:

PatientID AdmittanceID ID Symptom
====================================================================
1234 1 1 This is a symptom

One final note, there are no FK relations between the two tables

If the post was helpful, please vote, eh!

Current activities:
Book: Devils by Fyodor Dostoyevsky
Project: Hospital Automation, final stage
Learning: Image analysis, LINQ

Now and forever, defiant to the end.
What is Multiple Sclerosis[^]?

Are you wanting to relate/compare the tables or are you in the end trying to merge the tables?

Back in the blog beatch!
http://CraptasticNation.blogspot.com/[^]

More like merge them but with the repeated columns removed (PatientID & AdmittanceID).

In a way, I'd want results of the query to be as such: (based on the tables given in the OP)

(Table1) (Table1) (Table1) (Table2)
PatientID AdmittanceID SymptomID Symptom
============================================================
1234 1 1 NULL
1234 1 3 NULL
1234 1 NULL This is a symptom

Makes sense?

[Edit] I just realized that the tables aren't very clear. Try this:

Table 1

PatientID	AdmittanceID	SymptomID
1234	1	1
1234	1	3

Table 2

PatientID	AdmittanceID	ID	Symptom
1234	1	1	This is a symptom

Wanted result

PatientID	AdmittanceID	SymptomID	Symptom
1234	1	1	NULL
1234	1	3	NULL
1234	1	NULL	This is a symptom

What I get:

PatientID	AdmittanceID	SymptomID	Symptom
1234	1	1	This is a symptom
1234	1	3	This is a symptom

If the post was helpful, please vote, eh!

Current activities:
Book: Devils by Fyodor Dostoyevsky
Project: Hospital Automation, final stage
Learning: Image analysis, LINQ

Now and forever, defiant to the end.
What is Multiple Sclerosis[^]?

Would a union not make more sense than a join for this scenario?

I was sorta pressed for time so I physically merged the two tables on the database but I'll be certain to try it again next time!
Thanks Smile | :)

If the post was helpful, please vote, eh!

Current activities:
Book: Devils by Fyodor Dostoyevsky
Project: Hospital Automation, final stage
Learning: Image analysis, LINQ

Now and forever, defiant to the end.
What is Multiple Sclerosis[^]?

You can't use an outer join in this scenario because outer joins return nulls where there is not a match in the other table; You have matches.

A union query would be more appropriate in this case.

I was sorta pressed for time so I physically merged the two tables on the database but I'll be certain to try it again next time!

Thanks for the explanation though. But I thought Outer joins will return nulls because there was no corresponding columns that had a value (of course, now that I type this I remember that all this is row based).

If the post was helpful, please vote, eh!

Current activities:
Book: Devils by Fyodor Dostoyevsky
Project: Hospital Automation, final stage
Learning: Image analysis, LINQ

Now and forever, defiant to the end.
What is Multiple Sclerosis[^]?

I agree about the union. This is a link I had bookmarked that might help.

http://weblogs.sqlteam.com/jeffs/archive/2007/04/19/Full-Outer-Joins.aspx[^]

Back in the blog beatch!
http://CraptasticNation.blogspot.com/[^]

hi i have a table with following schema

MsgID    SystemID    Msg    Date(YYYY-MM-DD)
 1          1        ""     2009/01/02
 2          1        ""     2009/04/03
 3          1        ""     2009/05/02
 4          2        ""     2009/01/01

I want to identify the SystemIDs which has messages (column : Msg) generated with a Time difference greater than 3 months
In the given table its systemID "1" . ( logged first message on 2009/01/02 and after 3 months 2009/04/03).

please help to get a proper query

My small attempt...

And what have you tried so far, or are you expecting someone to write the query for you. Some hints.

Select top #n
datetime
dateadd
datediff
row_number() partition over

Never underestimate the power of human stupidity
RAH

don't understand.
plz send codez ASAP to lazy@yahoo.in

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles]

The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Show formatted code inside PRE tags, and give clear symptoms when describing a problem.

In one of the project they used System.Data.OleDb for connecting the SQL Server 2000 database. How does it differ from using System.Data.SqlClient.

Regards,
John.L.Ponratnam

Hi,

System.Data.SqlClient can only be used for SQL server. System.Data.OleDb is built to access a wide variety of databases as it is build on top of ODBC (for details look here: http://en.wikipedia.org/wiki/OLE_DB[^]).

I would suggest using the more common approach (ODBC or OleDB) as it gives you the opportunity to change your database provider without too much effects on your code.

Regards
Sebastian

It's not a bug, it's a feature!

Check out my CodeProject article Permission-by-aspect.

Me in Softwareland.

hi guys ,
actually i replaced my ldf and mdf files accidently and
now my database has changed , but i want it to restore to previous
ldf and mdf files , what to do ? i replaced files from the path

Tauseef A Khan
MCP Dotnet framework 2.0.

Hi,

Do you have any backup files of your database? Don't know if your recycle bin contains a copy of the old file (does this work when replacing files?).

Regards
Sebastian

It's not a bug, it's a feature!

Check out my CodeProject article Permission-by-aspect.

Me in Softwareland.

Hi Guys,

I have created a program that exports data from our old system to a .sql file that drops/creates and inserts, which works fine, the only problem I have is automating.

How do you execute a SQL file from a DTS package?

Obviuosly the file changes on a daily basis, but the filename can remain the same.

Please help and thanks in advance,

Paul.

Paul Hayman wrote:
How do you execute a SQL file from a DTS package?

that's a big ask in a forum message, you may find some tutorial and articles on Google [^]though

Never underestimate the power of human stupidity
RAH

Actually the solution was fairly easy, just a one liner sorted it using sqlcmd.

Thanks for your response though it's great that people do actually try and help.

Paul.