Hi,
How do I ensure that DISTINCT values of r.GPositionID are returned from the below?? - SELECT CommentImage AS ViewComment,r.GPositionID,GCustodian,GCustodianAccount,GAssetType
-
FROM @GResults r
-
LEFT OUTER JOIN
-
ReconComments cm
-
ON cm.GPositionID = r.GPositionID
-
WHERE r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)
-
ORDER BY GCustodian, GCustodianAccount, GAssetType;
Thanks.
9 2190
Hi,
How do I ensure that DISTINCT values of r.GPositionID are returned from the below?? - SELECT CommentImage AS ViewComment,r.GPositionID,GCustodian,GCustodianAccount,GAssetType
-
FROM @GResults r
-
LEFT OUTER JOIN
-
ReconComments cm
-
ON cm.GPositionID = r.GPositionID
-
WHERE r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)
-
ORDER BY GCustodian, GCustodianAccount, GAssetType;
Thanks.
What do you want the other fields be? First/Last/Any/Other? I mean that if you return the r.GPositionID, some values need to get associated with CommentImage, GCustodian, GCustodianAccount, and GAssetType as well.
What do you want the other fields be? First/Last/Any/Other? I mean that if you return the r.GPositionID, some values need to get associated with CommentImage, GCustodian, GCustodianAccount, and GAssetType as well.
r.GPositionID,GCustodian,GCustodianAcc ount,GAssetType are all from the one table.
So I want the distinct values of this table returned.
thanks.
r.GPositionID,GCustodian,GCustodianAcc ount,GAssetType are all from the one table.
So I want the distinct values of this table returned.
thanks.
If you want all distinct combinations put the DISTINCT keyword after SELECT, i.e.: SELECT DISTINCT CommentImage AS ViewComment,r.GPositionID,GCustodian,GCustodianAcc ount,GAssetType
...
If it's not what you want, give us examples, pls.
If you want all distinct combinations put the DISTINCT keyword after SELECT, i.e.: SELECT DISTINCT CommentImage AS ViewComment,r.GPositionID,GCustodian,GCustodianAcc ount,GAssetType
...
If it's not what you want, give us examples, pls.
Yes I want to do the above query but SQL Server gives this error...
The text, ntext, or image data type cannot be selected as DISTINCT
CommentImage field has datatype of image.
Thanks.
Yes I want to do the above query but SQL Server gives this error...
The text, ntext, or image data type cannot be selected as DISTINCT
CommentImage field has datatype of image.
Thanks.
That's bad but understandable (on SQL's side): what do you mean by distinct images? If you do mean they have to be different you'll have to find a workaround. If you don't, put all of your select but the image one into an "inner" select in the FROM clause, join the "inner" select with the table that contains the image and in an "outer" select select (all) fields from the "inner" select and the image from the other one.
Hope it helps. Without knowing the table structure and your exact specs it's hard to be more specific.
Tried the below query but returns duplicate rows... - SELECT cm.CommentImage AS ViewComment, r.GPositionID,r.GCustodian,r.GCustodianAccount,r.GAssetType
-
FROM @GResults r
-
LEFT OUTER JOIN
-
RComments cm
-
ON cm.GPositionID = r.GPositionID
-
AND cm.GPositionID = (SELECT min(GPositionID)
-
FROM RComments cm
-
WHERE GPositionID = r.GPositionID)
-
order by r.GPositionID
Here's the tables structure:
RComments Tbl:
RCommentsID int PK,
CommentImage image,
GPositionID int FK
@GResults Tbl:
GPositionID int PK,
GCustodian varchar(250),
GCustodianAccount varchar(250),
GAssetType varchar(250)
Tried the below query but returns duplicate rows... - SELECT cm.CommentImage AS ViewComment, r.GPositionID,r.GCustodian,r.GCustodianAccount,r.GAssetType
-
FROM @GResults r
-
LEFT OUTER JOIN
-
RComments cm
-
ON cm.GPositionID = r.GPositionID
-
AND cm.GPositionID = (SELECT min(GPositionID)
-
FROM RComments cm
-
WHERE GPositionID = r.GPositionID)
-
order by r.GPositionID
Here's the tables structure:
RComments Tbl:
RCommentsID int PK,
CommentImage image,
GPositionID int FK
@GResults Tbl:
GPositionID int PK,
GCustodian varchar(250),
GCustodianAccount varchar(250),
GAssetType varchar(250)
My suggestion: -
SELECT CommentImage AS ViewComment,
-
r.GPositionID,GCustodian,
-
GCustodianAccount,GAssetType
-
FROM (
-
SELECT DISTINCT
-
GPositionID,GCustodian,
-
GCustodianAccount,GAssetType
-
FROM @GResults
-
) r
-
LEFT OUTER JOIN ReconComments cm
-
ON cm.GPositionID = r.GPositionID
-
WHERE r.GPositionID NOT IN
-
(SELECT g.GPositionID FROM ReconGCrossReference g)
-
ORDER BY GCustodian, GCustodianAccount, GAssetType;
-
Returned rows from RComments table of the same GPositionID.
GPositionID and GCustodian are in the one table.
CommentImage is from a related table.
There is a M:M relation.
Here's the tables structure:
RComments Tbl:
RCommentsID int PK,
CommentImage image,
GPositionID int FK
@GResults Tbl:
GPositionID int PK,
GCustodian varchar(250),
GCustodianAccount varchar(250),
GAssetType varchar(250)
Thanks.
Returned rows from RComments table of the same GPositionID.
GPositionID and GCustodian are in the one table.
CommentImage is from a related table.
There is a M:M relation.
Here's the tables structure:
RComments Tbl:
RCommentsID int PK,
CommentImage image,
GPositionID int FK
@GResults Tbl:
GPositionID int PK,
GCustodian varchar(250),
GCustodianAccount varchar(250),
GAssetType varchar(250)
Thanks.
Especially if it's an M:M relation (technically the same as N:M), my problem is the same as SQL Server's: we'd need to compare and distinguish images... (It seems that you want DISTINCT images -- that's what the core of the problem is, isn't it?)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Simon |
last post by:
Hi,
I am having a little problem with my PHP - MySQl code, I have two tables
(shown below) and I am trying populate a template page with data from both.
<disclaimer>Now I would like to say my...
|
by: Florian |
last post by:
Hi,
I have a table that contains log data, usually around a million records. The
table has about 10 columns with various attributes of the logged data,
nothing special. We're using SQL Server...
|
by: Rich |
last post by:
My table looks like this:
char(150) HTTP_REF,
char(250) HTTP_USER,
char(150) REMOTE_ADDR,
char(150) REMOTE_HOST,
char(150) URL,
smalldatetime TIME_STAMP
There are no indexes on this table...
|
by: Fred Zuckerman |
last post by:
Can someone explain the difference between these 2 queries?
"Select Distinct id, account, lastname, firstname from table1"
and
"Select DistinctRow id, account, lastname, firstname from table1"
...
|
by: Johnathan Doe |
last post by:
I can google search to find the range of values that can be represented
in a float by reading up on the IEEE std, but is that the same as how
many distinct values that can go in a float type?
...
|
by: Daniel Wetzler |
last post by:
Dear MSSQL experts,
I use MSSQL 2000 and encountered a strange problem wqhile I tried to
use a select into statement .
If I perform the command command below I get only one dataset which has...
|
by: pbd22 |
last post by:
Hi.
How does one return a range of rows.
I know that "Top 5" will return rows 0 - 5
but, how do I get 6 - 10?
thanks
|
by: newnewbie |
last post by:
Desperately need help in creating a query to count unique values in a table. I am a Business analyst with limited knowledge of Access….My boss got me ODBC connection to the underlying tables for our...
|
by: lenygold via DBMonster.com |
last post by:
I have the following trigger:
--#SET TERMINATOR !
CREATE TRIGGER CROSS_REFF_TRIG
AFTER INSERT ON NEW_CATALOG
REFERENCING NEW AS nnn
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DECLARE reason...
|
by: Davy |
last post by:
Hi all,
I am writing a function, which return the pointer of the int. But it
seems to be wrong. Any suggestion?
int * get_p_t(int t) {
return &t;
}
int main()
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |