1:M query - 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

SQL Server 2005 Forums

Transact-SQL (2005)

1:M query

Author

Topic

u2envy1
Yak Posting Veteran

77 Posts

Posted - 2008-06-25 : 07:50:45

I have a table with a one to many relatioship.
If i pull a record from the one table then the matching many query must return the recent record sorted by date.

One
Table 1
ComplaintID = 1
Complaint = Why

Many
Table 2
CommentID = 1
ComplaintID = 1
DateInserted = 25/06/2008

Return 1,why,1,1,25/06/2008

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 08:11:22

[code]SELECT *
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY t1.ComplaintID ORDER BY t2.DateInserted DESC) AS RowNo,*
FROM Table1 t1
INNER JOIN Table2 t2
ON t2.ComplaintID=t1.ComplaintID)t
WHERE t.RowNo=1[/code]

u2envy1
Yak Posting Veteran

77 Posts

Posted - 2008-06-25 : 08:58:40

Thx, much appreciated !!

u2envy1
Yak Posting Veteran

77 Posts

Posted - 2008-06-30 : 02:58:00

I get the following error....... Why ?

Msg 8156, Level 16, State 1, Line 1
The column 'ComplaintID' was specified multiple times for 't'.

quote:

Originally posted by visakh16

SELECT *
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY t1.ComplaintID ORDER BY t2.DateInserted DESC) AS RowNo,*
FROM Table1 t1
INNER JOIN Table2 t2
ON t2.ComplaintID=t1.ComplaintID)t
WHERE t.RowNo=1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-30 : 03:08:51

quote:
Originally posted by u2envy1

I get the following error....... Why ?

Msg 8156, Level 16, State 1, Line 1
The column 'ComplaintID' was specified multiple times for 't'.

quote:
Originally posted by visakh16

SELECT *
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY t1.ComplaintID ORDER BY t2.DateInserted DESC) AS RowNo,*
FROM Table1 t1
INNER JOIN Table2 t2
ON t2.ComplaintID=t1.ComplaintID)t
WHERE t.RowNo=1

replace * with actual column names in subquery. also remember to give alias names if you've same column appearing multiple times.

u2envy1
Yak Posting Veteran

77 Posts

Posted - 2008-06-30 : 07:55:36

Thanks I got it working........
How do I add this working query to this bigger query ?
I would just want to add the t2.UserName column to the beigger query.

Working Query :

SELECT *
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY t1.ComplaintID ORDER BY t2.InceptionDate DESC) AS RowNo,t2.CommentID, t2.ComplaintID, t2.Comment, t2.InceptionDate, t2.UserName
FROM Complaint_Details t1
INNER JOIN Comments t2
ON t2.ComplaintID=t1.ComplaintID)t
WHERE t.RowNo=1 AND ComplaintID = 10

Bigger Query

SELECT a.ComplaintID, a.IsMember, a.MemberNumber, a.Title, a.FirstName, a.LastName, a.Email, a.Tel, a.ComplaintDetails, a.InceptionDate, a.StatusDate, a.UserName, d.Status, e.Department, c.ComplaintOrigin, b.ComplaintType, a.ComplaintType AS ComplaintTypeID, a.ComplaintOrigin AS ComplaintOriginID, a.Status AS StatusID, a.Department AS DepartmentID, a.TelC, f.CategoryName
FROM Complaint_Details AS a INNER JOIN
Status AS d ON a.Status = d.StatusID INNER JOIN
Department_Details AS e ON a.Department = e.DepartmentID INNER JOIN
Complaint_Origin AS c ON a.ComplaintOrigin = c.ComplaintOriginID INNER JOIN
Complaint_Type AS b ON a.ComplaintType = b.ComplaintTypeID INNER JOIN
Complaint_Category AS f ON a.Category = f.CategoryID
WHERE (InceptionDate between @inceptionfromdate AND @inceptiontodate) AND (a.ComplaintType IN (@complainttype)) AND (a.Department IN (@departmentid)) AND (a.ComplaintOrigin IN (@complaintorigin)) AND (a.Status IN (@status)) AND (a.Category IN (@CategoryID))

quote:
Originally posted by visakh16

quote:
Originally posted by u2envy1

I get the following error....... Why ?

Msg 8156, Level 16, State 1, Line 1
The column 'ComplaintID' was specified multiple times for 't'.

quote:
Originally posted by visakh16

SELECT *
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY t1.ComplaintID ORDER BY t2.DateInserted DESC) AS RowNo,*
FROM Table1 t1
INNER JOIN Table2 t2
ON t2.ComplaintID=t1.ComplaintID)t
WHERE t.RowNo=1

replace * with actual column names in subquery. also remember to give alias names if you've same column appearing multiple times.

u2envy1
Yak Posting Veteran

77 Posts

Posted - 2008-07-02 : 02:32:41

Any help please .......

quote:
Originally posted by visakh16

quote:
Originally posted by u2envy1

I get the following error....... Why ?

Msg 8156, Level 16, State 1, Line 1
The column 'ComplaintID' was specified multiple times for 't'.

quote:
Originally posted by visakh16

SELECT *
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY t1.ComplaintID ORDER BY t2.DateInserted DESC) AS RowNo,*
FROM Table1 t1
INNER JOIN Table2 t2
ON t2.ComplaintID=t1.ComplaintID)t
WHERE t.RowNo=1

replace * with actual column names in subquery. also remember to give alias names if you've same column appearing multiple times.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-02 : 03:35:09

[code]SELECT * FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY a.ComplaintID ORDER BY a.InceptionDate DESC) AS RowNo,a.ComplaintID, a.IsMember, a.MemberNumber, a.Title, a.FirstName, a.LastName, a.Email, a.Tel, a.ComplaintDetails, a.InceptionDate, a.StatusDate, a.UserName, d.Status, e.Department, c.ComplaintOrigin, b.ComplaintType, a.ComplaintType AS ComplaintTypeID, a.ComplaintOrigin AS ComplaintOriginID, a.Status AS StatusID, a.Department AS DepartmentID, a.TelC, f.CategoryName
FROM Complaint_Details AS a INNER JOIN
Status AS d ON a.Status = d.StatusID INNER JOIN
Department_Details AS e ON a.Department = e.DepartmentID INNER JOIN
Complaint_Origin AS c ON a.ComplaintOrigin = c.ComplaintOriginID INNER JOIN
Complaint_Type AS b ON a.ComplaintType = b.ComplaintTypeID INNER JOIN
Complaint_Category AS f ON a.Category = f.CategoryID
WHERE (InceptionDate between @inceptionfromdate AND @inceptiontodate) AND (a.ComplaintType IN (@complainttype)) AND (a.Department IN (@departmentid)) AND (a.ComplaintOrigin IN (@complaintorigin)) AND (a.Status IN (@status)) AND (a.Category IN (@CategoryID)))t
WHERE t.RowNo=1[/code]

u2envy1
Yak Posting Veteran

77 Posts

Posted - 2008-07-02 : 08:43:35

Thx allot !!

quote:

Originally posted by visakh16

SELECT * FROM
(
SELECT  ROW_NUMBER() OVER(PARTITION BY a.ComplaintID ORDER BY a.InceptionDate DESC) AS RowNo,a.ComplaintID, a.IsMember, a.MemberNumber, a.Title, a.FirstName, a.LastName, a.Email, a.Tel, a.ComplaintDetails, a.InceptionDate, a.StatusDate, a.UserName, d.Status, e.Department, c.ComplaintOrigin, b.ComplaintType, a.ComplaintType AS ComplaintTypeID, a.ComplaintOrigin AS ComplaintOriginID, a.Status AS StatusID, a.Department AS DepartmentID, a.TelC, f.CategoryName
FROM Complaint_Details AS a INNER JOIN
Status AS d ON a.Status = d.StatusID INNER JOIN
Department_Details AS e ON a.Department = e.DepartmentID INNER JOIN
Complaint_Origin AS c ON a.ComplaintOrigin = c.ComplaintOriginID INNER JOIN
Complaint_Type AS b ON a.ComplaintType = b.ComplaintTypeID INNER JOIN
Complaint_Category AS f ON a.Category = f.CategoryID
WHERE (InceptionDate between @inceptionfromdate AND @inceptiontodate) AND (a.ComplaintType IN (@complainttype)) AND (a.Department IN (@departmentid)) AND (a.ComplaintOrigin IN (@complaintorigin)) AND (a.Status IN (@status)) AND (a.Category IN (@CategoryID)))t
WHERE t.RowNo=1

u2envy1
Yak Posting Veteran

77 Posts

Posted - 2008-07-02 : 09:13:01

Sorry but this is neva ending....Why do I get this ?
The column 'UserName' was specified multiple times for 't'. MSQL Error: 8156

SELECT * FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY a.ComplaintID ORDER BY a.InceptionDate DESC) AS RowNo,a.ComplaintID, a.IsMember, a.MemberNumber, a.Title, a.FirstName, a.LastName, a.Email, a.Tel, a.ComplaintDetails, a.InceptionDate, a.StatusDate, a.UserName, d.Status, e.Department, c.ComplaintOrigin, b.ComplaintType, a.ComplaintType AS ComplaintTypeID, a.ComplaintOrigin AS ComplaintOriginID, a.Status AS StatusID, a.Department AS DepartmentID, a.TelC, f.CategoryName, g.UserName AS UserName
FROM Complaint_Details AS a INNER JOIN
Status AS d ON a.Status = d.StatusID INNER JOIN
Department_Details AS e ON a.Department = e.DepartmentID INNER JOIN
Complaint_Origin AS c ON a.ComplaintOrigin = c.ComplaintOriginID INNER JOIN
Complaint_Type AS b ON a.ComplaintType = b.ComplaintTypeID INNER JOIN
Complaint_Category AS f ON a.Category = f.CategoryID INNER JOIN
Comments AS g ON a.ComplaintID = g.ComplaintID
WHERE (a.InceptionDate between @inceptionfromdate AND @inceptiontodate) AND (a.ComplaintType IN (@complainttype)) AND (a.Department IN (@departmentid)) AND (a.ComplaintOrigin IN (@complaintorigin)) AND (a.Status IN (@status)) AND (a.Category IN (@CategoryID)))t
WHERE t.RowNo=1

quote:

Originally posted by visakh16

SELECT * FROM
(
SELECT  ROW_NUMBER() OVER(PARTITION BY a.ComplaintID ORDER BY a.InceptionDate DESC) AS RowNo,a.ComplaintID, a.IsMember, a.MemberNumber, a.Title, a.FirstName, a.LastName, a.Email, a.Tel, a.ComplaintDetails, a.InceptionDate, a.StatusDate, a.UserName, d.Status, e.Department, c.ComplaintOrigin, b.ComplaintType, a.ComplaintType AS ComplaintTypeID, a.ComplaintOrigin AS ComplaintOriginID, a.Status AS StatusID, a.Department AS DepartmentID, a.TelC, f.CategoryName
FROM Complaint_Details AS a INNER JOIN
Status AS d ON a.Status = d.StatusID INNER JOIN
Department_Details AS e ON a.Department = e.DepartmentID INNER JOIN
Complaint_Origin AS c ON a.ComplaintOrigin = c.ComplaintOriginID INNER JOIN
Complaint_Type AS b ON a.ComplaintType = b.ComplaintTypeID INNER JOIN
Complaint_Category AS f ON a.Category = f.CategoryID
WHERE (InceptionDate between @inceptionfromdate AND @inceptiontodate) AND (a.ComplaintType IN (@complainttype)) AND (a.Department IN (@departmentid)) AND (a.ComplaintOrigin IN (@complaintorigin)) AND (a.Status IN (@status)) AND (a.Category IN (@CategoryID)))t
WHERE t.RowNo=1

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-02 : 09:25:08

quote:
Originally posted by u2envy1

Sorry but this is neva ending....Why do I get this ?
The column 'UserName' was specified multiple times for 't'. MSQL Error: 8156

SELECT * FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY a.ComplaintID ORDER BY a.InceptionDate DESC) AS RowNo,a.ComplaintID, a.IsMember, a.MemberNumber, a.Title, a.FirstName, a.LastName, a.Email, a.Tel, a.ComplaintDetails, a.InceptionDate, a.StatusDate, a.UserName, d.Status, e.Department, c.ComplaintOrigin, b.ComplaintType, a.ComplaintType AS ComplaintTypeID, a.ComplaintOrigin AS ComplaintOriginID, a.Status AS StatusID, a.Department AS DepartmentID, a.TelC, f.CategoryName, g.UserName AS UserName
FROM Complaint_Details AS a INNER JOIN
Status AS d ON a.Status = d.StatusID INNER JOIN
Department_Details AS e ON a.Department = e.DepartmentID INNER JOIN
Complaint_Origin AS c ON a.ComplaintOrigin = c.ComplaintOriginID INNER JOIN
Complaint_Type AS b ON a.ComplaintType = b.ComplaintTypeID INNER JOIN
Complaint_Category AS f ON a.Category = f.CategoryID INNER JOIN
Comments AS g ON a.ComplaintID = g.ComplaintID
WHERE (a.InceptionDate between @inceptionfromdate AND @inceptiontodate) AND (a.ComplaintType IN (@complainttype)) AND (a.Department IN (@departmentid)) AND (a.ComplaintOrigin IN (@complaintorigin)) AND (a.Status IN (@status)) AND (a.Category IN (@CategoryID)))t
WHERE t.RowNo=1

quote:
Originally posted by visakh16

SELECT * FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY a.ComplaintID ORDER BY a.InceptionDate DESC) AS RowNo,a.ComplaintID, a.IsMember, a.MemberNumber, a.Title, a.FirstName, a.LastName, a.Email, a.Tel, a.ComplaintDetails, a.InceptionDate, a.StatusDate, a.UserName, d.Status, e.Department, c.ComplaintOrigin, b.ComplaintType, a.ComplaintType AS ComplaintTypeID, a.ComplaintOrigin AS ComplaintOriginID, a.Status AS StatusID, a.Department AS DepartmentID, a.TelC, f.CategoryName
FROM Complaint_Details AS a INNER JOIN
Status AS d ON a.Status = d.StatusID INNER JOIN
Department_Details AS e ON a.Department = e.DepartmentID INNER JOIN
Complaint_Origin AS c ON a.ComplaintOrigin = c.ComplaintOriginID INNER JOIN
Complaint_Type AS b ON a.ComplaintType = b.ComplaintTypeID INNER JOIN
Complaint_Category AS f ON a.Category = f.CategoryID
WHERE (InceptionDate between @inceptionfromdate AND @inceptiontodate) AND (a.ComplaintType IN (@complainttype)) AND (a.Department IN (@departmentid)) AND (a.ComplaintOrigin IN (@complaintorigin)) AND (a.Status IN (@status)) AND (a.Category IN (@CategoryID)))t
WHERE t.RowNo=1

because you literally are selecting it multiple times (see above in red). just call one of them something else

Em

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-07-02 : 10:31:37

quote:
Originally posted by u2envy1

Sorry but this is neva ending....Why do I get this ?
The column 'UserName' was specified multiple times for 't'. MSQL Error: 8156

To repeat:

quote:

replace * with actual column names in subquery. also remember to give alias names if you've same column appearing multiple times.

- Jeff
http://weblogs.sqlteam.com/JeffS

u2envy1
Yak Posting Veteran

77 Posts

Posted - 2008-07-08 : 09:13:24

Thanks all is working.......
If g.UserName is null that record is not returned. How do I return that record ?

SELECT * FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY g.ComplaintID ORDER BY g.InceptionDate ASC) AS RowNo,a.ComplaintID, a.IsMember, a.MemberNumber, a.Title, a.FirstName, a.LastName, a.Email, a.Tel, a.ComplaintDetails, a.InceptionDate, a.StatusDate, a.UserName, d.Status, e.Department, c.ComplaintOrigin, b.ComplaintType As ComplaintTypeID , a.ComplaintType AS ComplaintType, a.ComplaintOrigin AS ComplaintOriginID, a.Status AS StatusID, a.Department AS DepartmentID, a.TelC, f.CategoryName, g.UserName AS [User_Name]
FROM Complaint_Details AS a INNER JOIN
Status AS d ON a.Status = d.StatusID INNER JOIN
Department_Details AS e ON a.Department = e.DepartmentID INNER JOIN
Complaint_Origin AS c ON a.ComplaintOrigin = c.ComplaintOriginID INNER JOIN
Complaint_Type AS b ON a.ComplaintType = b.ComplaintTypeID INNER JOIN
Complaint_Category AS f ON a.Category = f.CategoryID RIGHT JOIN
Comments AS g ON a.ComplaintID = g.ComplaintID
WHERE (a.InceptionDate between @inceptionfromdate AND @inceptiontodate) AND (a.ComplaintType IN (@complainttype)) AND (a.Department IN (@departmentid)) AND (a.ComplaintOrigin IN (@complaintorigin)) AND (a.Status IN (@status)) AND (a.Category IN (@CategoryID) ))t
WHERE t.RowNo=1

quote:
Originally posted by elancaster

quote:
Originally posted by u2envy1

Sorry but this is neva ending....Why do I get this ?
The column 'UserName' was specified multiple times for 't'. MSQL Error: 8156

SELECT * FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY a.ComplaintID ORDER BY a.InceptionDate DESC) AS RowNo,a.ComplaintID, a.IsMember, a.MemberNumber, a.Title, a.FirstName, a.LastName, a.Email, a.Tel, a.ComplaintDetails, a.InceptionDate, a.StatusDate, a.UserName, d.Status, e.Department, c.ComplaintOrigin, b.ComplaintType, a.ComplaintType AS ComplaintTypeID, a.ComplaintOrigin AS ComplaintOriginID, a.Status AS StatusID, a.Department AS DepartmentID, a.TelC, f.CategoryName, g.UserName AS UserName
FROM Complaint_Details AS a INNER JOIN
Status AS d ON a.Status = d.StatusID INNER JOIN
Department_Details AS e ON a.Department = e.DepartmentID INNER JOIN
Complaint_Origin AS c ON a.ComplaintOrigin = c.ComplaintOriginID INNER JOIN
Complaint_Type AS b ON a.ComplaintType = b.ComplaintTypeID INNER JOIN
Complaint_Category AS f ON a.Category = f.CategoryID INNER JOIN
Comments AS g ON a.ComplaintID = g.ComplaintID
WHERE (a.InceptionDate between @inceptionfromdate AND @inceptiontodate) AND (a.ComplaintType IN (@complainttype)) AND (a.Department IN (@departmentid)) AND (a.ComplaintOrigin IN (@complaintorigin)) AND (a.Status IN (@status)) AND (a.Category IN (@CategoryID)))t
WHERE t.RowNo=1

quote:
Originally posted by visakh16

SELECT * FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY a.ComplaintID ORDER BY a.InceptionDate DESC) AS RowNo,a.ComplaintID, a.IsMember, a.MemberNumber, a.Title, a.FirstName, a.LastName, a.Email, a.Tel, a.ComplaintDetails, a.InceptionDate, a.StatusDate, a.UserName, d.Status, e.Department, c.ComplaintOrigin, b.ComplaintType, a.ComplaintType AS ComplaintTypeID, a.ComplaintOrigin AS ComplaintOriginID, a.Status AS StatusID, a.Department AS DepartmentID, a.TelC, f.CategoryName
FROM Complaint_Details AS a INNER JOIN
Status AS d ON a.Status = d.StatusID INNER JOIN
Department_Details AS e ON a.Department = e.DepartmentID INNER JOIN
Complaint_Origin AS c ON a.ComplaintOrigin = c.ComplaintOriginID INNER JOIN
Complaint_Type AS b ON a.ComplaintType = b.ComplaintTypeID INNER JOIN
Complaint_Category AS f ON a.Category = f.CategoryID
WHERE (InceptionDate between @inceptionfromdate AND @inceptiontodate) AND (a.ComplaintType IN (@complainttype)) AND (a.Department IN (@departmentid)) AND (a.ComplaintOrigin IN (@complaintorigin)) AND (a.Status IN (@status)) AND (a.Category IN (@CategoryID)))t
WHERE t.RowNo=1

because you literally are selecting it multiple times (see above in red). just call one of them something else

Em

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-08 : 13:53:51

thats because you're taking RIGHT JOIN with comments. Either change it to INNER JOIN if you can (depends on your requirement) or do as below:-



SELECT * FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY g.ComplaintID ORDER BY g.InceptionDate ASC) AS RowNo,a.ComplaintID, a.IsMember, a.MemberNumber, a.Title, a.FirstName, a.LastName, a.Email, a.Tel, a.ComplaintDetails, a.InceptionDate, a.StatusDate, a.UserName, d.Status, e.Department, c.ComplaintOrigin, b.ComplaintType As ComplaintTypeID , a.ComplaintType AS ComplaintType, a.ComplaintOrigin AS ComplaintOriginID, a.Status AS StatusID, a.Department AS DepartmentID, a.TelC, f.CategoryName, g.UserName AS [User_Name]
FROM Complaint_Details AS a INNER JOIN
Status AS d ON a.Status = d.StatusID INNER JOIN
Department_Details AS e ON a.Department = e.DepartmentID INNER JOIN
Complaint_Origin AS c ON a.ComplaintOrigin = c.ComplaintOriginID INNER JOIN
Complaint_Type AS b ON a.ComplaintType = b.ComplaintTypeID INNER JOIN
Complaint_Category AS f ON a.Category = f.CategoryID RIGHT JOIN
Comments AS g ON a.ComplaintID = g.ComplaintID
WHERE 
((a.InceptionDate between @inceptionfromdate AND @inceptiontodate) 
OR a.InceptionDate IS NULL)
AND ((a.ComplaintType IN (@complainttype)) OR a.ComplaintType IS NULL)
AND ((a.Department IN (@departmentid)) OR a.Department IS NULL)
AND ((a.ComplaintOrigin IN (@complaintorigin)) OR  a.ComplaintOrigin IS NULL)
AND ((a.Status IN (@status)) OR a.Status IS NULL)
AND ((a.Category IN (@CategoryID) ) OR a.Category IS NULL))t
WHERE t.RowNo=1

u2envy1
Yak Posting Veteran

77 Posts

Posted - 2008-07-09 : 01:22:56

Sorry my bad. For testing purposes I added a Right join.
With an inner join I still dont get the nulls for that column.
Is it possible to add OR g.ComplaintID IS NULL some where for it to work ?
Thanks for you time & patients with me.

quote:

Originally posted by visakh16

thats because you're taking RIGHT JOIN with comments. Either change it to INNER JOIN if you can (depends on your requirement) or do as below:-



SELECT * FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY g.ComplaintID ORDER BY g.InceptionDate ASC) AS RowNo,a.ComplaintID, a.IsMember, a.MemberNumber, a.Title, a.FirstName, a.LastName, a.Email, a.Tel, a.ComplaintDetails, a.InceptionDate, a.StatusDate, a.UserName, d.Status, e.Department, c.ComplaintOrigin, b.ComplaintType As ComplaintTypeID , a.ComplaintType AS ComplaintType, a.ComplaintOrigin AS ComplaintOriginID, a.Status AS StatusID, a.Department AS DepartmentID, a.TelC, f.CategoryName, g.UserName AS [User_Name]
FROM Complaint_Details AS a INNER JOIN
Status AS d ON a.Status = d.StatusID INNER JOIN
Department_Details AS e ON a.Department = e.DepartmentID INNER JOIN
Complaint_Origin AS c ON a.ComplaintOrigin = c.ComplaintOriginID INNER JOIN
Complaint_Type AS b ON a.ComplaintType = b.ComplaintTypeID INNER JOIN
Complaint_Category AS f ON a.Category = f.CategoryID RIGHT JOIN
Comments AS g ON a.ComplaintID = g.ComplaintID
WHERE 
((a.InceptionDate between @inceptionfromdate AND @inceptiontodate) 
OR a.InceptionDate IS NULL)
AND ((a.ComplaintType IN (@complainttype)) OR a.ComplaintType IS NULL)
AND ((a.Department IN (@departmentid)) OR a.Department IS NULL)
AND ((a.ComplaintOrigin IN (@complaintorigin)) OR  a.ComplaintOrigin IS NULL)
AND ((a.Status IN (@status)) OR a.Status IS NULL)
AND ((a.Category IN (@CategoryID) ) OR a.Category IS NULL))t
WHERE t.RowNo=1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-09 : 01:37:59

quote:

Originally posted by u2envy1

Sorry my bad. For testing purposes I added a Right join.
With an inner join I still dont get the nulls for that column.
Is it possible to add OR g.ComplaintID IS NULL some where for it to work ?
Thanks for you time & patients with me.

quote:

Originally posted by visakh16

thats because you're taking RIGHT JOIN with comments. Either change it to INNER JOIN if you can (depends on your requirement) or do as below:-



SELECT * FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY g.ComplaintID ORDER BY g.InceptionDate ASC) AS RowNo,a.ComplaintID, a.IsMember, a.MemberNumber, a.Title, a.FirstName, a.LastName, a.Email, a.Tel, a.ComplaintDetails, a.InceptionDate, a.StatusDate, a.UserName, d.Status, e.Department, c.ComplaintOrigin, b.ComplaintType As ComplaintTypeID , a.ComplaintType AS ComplaintType, a.ComplaintOrigin AS ComplaintOriginID, a.Status AS StatusID, a.Department AS DepartmentID, a.TelC, f.CategoryName, g.UserName AS [User_Name]
FROM Complaint_Details AS a INNER JOIN
Status AS d ON a.Status = d.StatusID INNER JOIN
Department_Details AS e ON a.Department = e.DepartmentID INNER JOIN
Complaint_Origin AS c ON a.ComplaintOrigin = c.ComplaintOriginID INNER JOIN
Complaint_Type AS b ON a.ComplaintType = b.ComplaintTypeID INNER JOIN
Complaint_Category AS f ON a.Category = f.CategoryID RIGHT JOIN
Comments AS g ON a.ComplaintID = g.ComplaintID
WHERE 
((a.InceptionDate between @inceptionfromdate AND @inceptiontodate) 
OR a.InceptionDate IS NULL)
AND ((a.ComplaintType IN (@complainttype)) OR a.ComplaintType IS NULL)
AND ((a.Department IN (@departmentid)) OR a.Department IS NULL)
AND ((a.ComplaintOrigin IN (@complaintorigin)) OR  a.ComplaintOrigin IS NULL)
AND ((a.Status IN (@status)) OR a.Status IS NULL)
AND ((a.Category IN (@CategoryID) ) OR a.Category IS NULL))t
WHERE t.RowNo=1

Just add an OR condition in INNER JOIN step

INNER JOIN Comments AS g 
ON (a.ComplaintID = g.ComplaintID OR g.ComplaintID  IS NULL)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources