Query - Change - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Query - Change

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-02-07 : 11:14:28

Hi,

I have the following query which gives the results

SELECT     dbo.aspnet_cprdContacts.Surname, dbo.aspnet_InterestGroupA.InterestGroupADesc, dbo.aspnet_InterestGroupBListA.InterestGroupBListADesc
FROM         dbo.aspnet_cprdContacts INNER JOIN
                      dbo.aspnet_AreaofInterest ON dbo.aspnet_cprdContacts.ContactCode = dbo.aspnet_AreaofInterest.ContactCode INNER JOIN
                      dbo.aspnet_InterestGroupA ON dbo.aspnet_AreaofInterest.InterestCode = dbo.aspnet_InterestGroupA.InterestGroupAID INNER JOIN
                      dbo.aspnet_ObservationalResearch ON dbo.aspnet_cprdContacts.ContactCode = dbo.aspnet_ObservationalResearch.ContactCode INNER JOIN
                      dbo.aspnet_InterestGroupBListA ON 
                      dbo.aspnet_ObservationalResearch.ObsResearchCode = dbo.aspnet_InterestGroupBListA.InterestGroupBListAID
WHERE     (dbo.aspnet_cprdContacts.Surname = 'Mascrier')

Output Result

Mascrier Biologics Data Linkage
Mascrier Biologics Device Related Research
Mascrier Biologics Drug Compliance
Mascrier Devices Data Linkage
Mascrier Devices Device Related Research
Mascrier Devices Drug Compliance

Now, I want the result to be;

Mascrier Biologics Data Linkage
Mascrier Devices Device Related Research
Mascrier NULL Drug Compliance

Thank you

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-07 : 12:00:00

I'm assuming you wan the "First" one

Do you have an identity column or a default datetime column set to GetDate()

And HOW do you get the third row?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-02-07 : 12:06:48

OK - Chaned it slightly to ;

SELECT     dbo.aspnet_InterestGroupA.InterestGroupADesc, dbo.aspnet_InterestGroupBListA.InterestGroupBListADesc, 
                      dbo.aspnet_cprdContacts.ContactCode
FROM         dbo.aspnet_cprdContacts INNER JOIN
                      dbo.aspnet_AreaofInterest ON dbo.aspnet_cprdContacts.ContactCode = dbo.aspnet_AreaofInterest.ContactCode INNER JOIN
                      dbo.aspnet_InterestGroupA ON dbo.aspnet_AreaofInterest.InterestCode = dbo.aspnet_InterestGroupA.InterestGroupAID INNER JOIN
                      dbo.aspnet_ObservationalResearch ON dbo.aspnet_cprdContacts.ContactCode = dbo.aspnet_ObservationalResearch.ContactCode INNER JOIN
                      dbo.aspnet_InterestGroupBListA ON 
                      dbo.aspnet_ObservationalResearch.ObsResearchCode = dbo.aspnet_InterestGroupBListA.InterestGroupBListAID
WHERE     (dbo.aspnet_cprdContacts.Surname = 'Mascrier')

The other two tables link with the contactcode. I dont also have the datetime value.

results now;

2 Biologics Data Linkage
2 Biologics Device Related Research
2 Biologics Drug Compliance
2 Devices Data Linkage
2 Devices Device Related Research
2 Devices Drug Compliance

Expected result

2 Biologics Data Linkage
2 Devices Device Related Research
2 NULL Drug Compliance

You assistance will be highly appreciated..

Regards

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-07 : 12:18:18

Please help us understand...how do you want to derive the final results?

What's the logic?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources