Help with using distinct - 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

Help with using distinct

Author

Topic

aharvestofhealth
Yak Posting Veteran

52 Posts

Posted - 2011-07-29 : 20:44:23

When I run the following statement:

select vend_no, item_no, usr_item_stat
from po_vend_item
where item_no = '899584002049'

I get the following results:

VEND_NO    ITEM_NO    USR_ITEM_STAT
1268          899584002049       N
1359          899584002049       Y

However, I'm trying to create a select statement to look for where the field "usr_item_stat" is ONLY set to 'Y'. Above there is both an N and a Y in it, so I would like the statement to return no results in that case. If the USR_ITEM_STAT field would be set to Y in both rows, then it should return a single result. I tried creating the statement below, but it doesn't work.

select ITEM_NO
from PO_VEND_ITEM
where USR_ITEM_STAT = (select distinct USR_ITEM_STAT from PO_VEND_ITEM where USR_ITEM_STAT = 'Y')

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-29 : 21:06:47

quote:
Originally posted by aharvestofhealth

When I run the following statement:
select vend_no, item_no, usr_item_stat
from po_vend_item
where item_no = '899584002049'

I get the following results:
PO_VEND_ITEM ITEM_NO USR_ITEM_STAT
1268 899584002049 N
1359 899584002049 Y

However, I'm trying to create a select statement to look for where the field "usr_item_stat" is ONLY set to 'Y'. Above there is both an N and a Y in it, so I would like the statement to return no results in that case. If the USR_ITEM_STAT field would be set to Y in both rows, then it should return a single result. I tried creating the statement below, but it doesn't work.
select ITEM_NO
from PO_VEND_ITEM
where USR_ITEM_STAT = (select distinct USR_ITEM_STAT from PO_VEND_ITEM where USR_ITEM_STAT = 'Y')

One way to do this is to use the "NOT EXISTS" construct like this:

SELECT vend_no,
       item_no,
       usr_item_stat
FROM   po_vend_item p1
WHERE  item_no = '899584002049'
       AND NOT EXISTS (
          SELECT * 
          FROM po_vend_item p2 
          WHERE p2.ITEM_NO=p1.ITEM_NO AND p2.USR_ITEM_STAT = 'N'
       );

If both rows had USER_ITEM_STAT = 'Y', that would return both rows. If you wanted only one row, you have to decided which value of VEND_NO you want to pick. Let us say you want to pick the maximum value of VEND_NO. Then,

SELECT max(vend_no),
       item_no,
       usr_item_stat
FROM   po_vend_item p1
WHERE  item_no = '899584002049'
       AND NOT EXISTS (SELECT * FROM po_vend_item p2 WHERE p2.ITEM_NO=p1.ITEM_NO AND p2.USR_ITEM_STAT = 'N')
GROUP BY
       item_no,
       usr_item_stat

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-30 : 01:27:04

another way is to use join like below:-


SELECT t.*
FROM YourTable t
JOIN (SELECT ITEM_NO
      FROM YourTable
      GROUP BY ITEM_NO
      HAVING MAX(USR_ITEM_STAT)= MIN(USR_ITEM_STAT)
      AND  MAX(USR_ITEM_STAT)='Y')t1
On t1.ITEM_NO = t.ITEM_NO
WHERE t.ITEM_NO = @YourItemNoValue

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

aharvestofhealth
Yak Posting Veteran

52 Posts

Posted - 2011-07-30 : 09:02:24

quote:
Originally posted by sunitabeck

One way to do this is to use the "NOT EXISTS" construct like this:
SELECT vend_no,
item_no,
usr_item_stat
FROM po_vend_item p1
WHERE item_no = '899584002049'
AND NOT EXISTS (
SELECT *
FROM po_vend_item p2
WHERE p2.ITEM_NO=p1.ITEM_NO AND p2.USR_ITEM_STAT = 'N'
);
If both rows had USER_ITEM_STAT = 'Y', that would return both rows.

Thanks, I ended up using this one. Can you explain the p1. and p2.? I understand using the as an alias (p1 as item_no), but how do they work otherwise?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-30 : 09:41:01

p1 and p2 are aliases for the table (in some ways, similar to aliasing a column).


SELECT vend_no,
       item_no,
       usr_item_stat
FROM   po_vend_item AS p1
WHERE  item_no = '899584002049'
       AND NOT EXISTS (
          SELECT * 
          FROM po_vend_item AS p2 
          WHERE p2.ITEM_NO=p1.ITEM_NO AND p2.USR_ITEM_STAT = 'N'

In this case we HAVE to alias the table because we are using columns from the same table in the subquery (p2.ITEM_NO=p1.ITEM_NO etc.). If that were not the case, you wouldn't need to. Yet, more often than not people do use aliases, because it saves some typing when you want to refer to the tables.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-07-30 : 10:44:32

Every SQL forum or newsgroup expects that you will post DDL, sample data and clear specifications which might might include output. This is explained in the FAQ section. We can not read minds; what would we need to know to do your job for you?

Tables must have keys and should have DRI, constraints, and all the basic features of a schema. You should know use ISO-8601 Standards for temporal data, avoid needless dialect, basic data modeling and use ISO-11179 Standards for data element names. Please tell us if you can change the DDL.

Your narrative has obvious flaws:

1) Tables are sets so their names are plurals or collective nouns.
2) SQL is not Assembly language, so we do not use bit flags
3) You don't know the difference between a field and column. That is serious and explains why you are still writing assembler language in SQL.

>> However, I'm trying to create a SELECT statement to look for where the field [sic] "usr_item_stat" is ONLY set to 'Y'. <<
SELECT item_no
FROM po_vend_item
GROUP BY item_no
HAVING MAX (usr_item_stat) = 'Y'
AND MIN (usr_item_stat) = 'Y';

A status is a state of being which has a duration; this is basic data modeling. So asking “What is your marital_status [marital_status IN ('married', 'divorced', single', ..)]? has an implicit (start_date, end_date) dimension. Do you just answer “Yes”, the way I fill in the “sex” free-form field on paper forms?

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

aharvestofhealth
Yak Posting Veteran

52 Posts

Posted - 2011-08-01 : 09:02:41

Thanks for the explanation Sunitabeck. That's very helpful. So basically, when using the "Exists", you always need to have "where table.column = table.column".

So I can also bring in a column from a different table to do the same, right?

SELECT po_vend_item.vend_no, 
       po_vend_item.item_no,
       po_vend_item.usr_item_stat
FROM im_item, po_vend_item
WHERE NOT EXISTS (
          SELECT * 
          FROM po_vend_item 
          WHERE po_vend_item.item_no = im_item.item_no AND po_vend_item.usr_item_stat = 'N'
) 
AND po_vend_item.item_no = im_item.item_no

When I replace yours with the one above, the results are the same - both return a query of 1308 rows. I can tell that yours is more efficient though.

I would also like to make it an update statement as well, to update a column in the im_item table. The im_item.item_no and the po_vend_item.item_no are the primary keys.

UPDATE im_item 
SET im_item.stat = 'V'
FROM im_item, po_vend_item
WHERE NOT EXISTS ( 
          SELECT * 
          FROM po_vend_item 
          WHERE po_vend_item.item_no = im_item.item_no AND po_vend_item.usr_item_stat = 'N'
)
AND po_vend_item.item_no = im_item.item_no

For some reason however, when I run this, it only updates 1257 rows instead of the 1308. Any ideas why?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-01 : 09:53:13

as i see you've an extra condition im_item.stat <> 'A' which might be the reason for this.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

aharvestofhealth
Yak Posting Veteran

52 Posts

Posted - 2011-08-01 : 10:09:53

Sorry, that wasn't supposed to be there. I corrected it, and it still updates only 1257 rows.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-01 : 12:43:00

is relationship between im_item and po_vend_item one to one?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

aharvestofhealth
Yak Posting Veteran

52 Posts

Posted - 2011-08-01 : 13:01:47

Every item number in im_item.item_no is also found in po_vend_item.item_no. The po_vend_item.item_no field will have doubles and triples of the the same number but there are not any extra numbers. So I believe the answer is yes.

If I do a select all from, then I get two different query totals:

"select * item_no from im_item" returns 79125 results
"select * item_no from po_vend_item" returns 116431 results

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-01 : 13:17:48

that explains it i guess. when you select you get multiples because of many to many. but when you updates its updating only each record of im_item only once that was linked to multiple records in po_vend_item and hence count difference. take a distinct item_no from join query and see how many it returns

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

aharvestofhealth
Yak Posting Veteran

52 Posts

Posted - 2011-08-01 : 16:20:07

Ok, I understand know. That makes complete sense. Thanks everyone for your help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-02 : 00:56:38

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources