doubt with select - 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

doubt with select

Author

Topic

divyaram
Posting Yak Master

180 Posts

Posted - 2010-03-16 : 05:49:26

Hi all,

My table structure is like this . i hae copied only sample data over here like this data
i have more than 500. here i have to select entrytype_id which will repeat for article_type_id, i am totally cofused
how to select the datas from this table with this condition
table structure is like this....

Article_Pdf_ID Article_Type_ID EntryType_ID IsDeleted IsActivated Modified by
4 24 2 1 0 387
5 24 15 0 1 387
6 24 4 1 0 387
7 24 5 1 0 387
8 24 6 1 0 387
9 24 7 0 1 387
12 24 8 0 1 387
13 43 9 1 0 387
14 35 10 1 0 387
15 25 1 1 0 387
16 25 2 1 0 387
17 25 3 1 0 387
18 25 4 1 0 387
19 25 5 1 0 387
20 29 1 1 0 387

Regards,
Divya

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-03-16 : 06:06:32

can u post required sample output

divyaram
Posting Yak Master

180 Posts

Posted - 2010-03-16 : 06:28:31

quote:
Originally posted by bklr

can u post required sample output

This sample data for where entry type is 15, like this there are many entrytpe_id
is there which will repeat i want to display ArticleType_id and Entrytype_id for
Entrytype_id which will repeat..

i think scenario is clear now.. please let me know if any query .....

Article_Type_ID EntryType
24 15
26 15
25 15
27 15
28 15
29 15
30 15
31 15
32 15

Regards,
Divya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 06:43:36

do you mean this?


SELECT t.Article_Type_ID, EntryType_ID
FROM Table t
INNER JOIN (SELECT EntryType_ID,COUNT(*) AS Occurance
            FROM Table
            GROUP BY EntryType_ID)t1
ON t.EntryType_ID=t1.EntryType_ID
AND t1.Occurance > 1

[/code]

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

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-03-16 : 06:44:28

select distinct Article_Type_ID ,EntryType from tablename

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 07:01:55

quote:
Originally posted by bklr

select distinct Article_Type_ID ,EntryType from tablename

how do you think it will cover following reqmnt?

there are many entrytpe_id
is there which will repeat i want to display ArticleType_id and Entrytype_id for
Entrytype_id which will repeat..

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

divyaram
Posting Yak Master

180 Posts

Posted - 2010-03-16 : 07:24:11

quote:
Originally posted by visakh16

quote:
Originally posted by bklr

select distinct Article_Type_ID ,EntryType from tablename

how do you think it will cover following reqmnt?

there are many entrytpe_id
is there which will repeat i want to display ArticleType_id and Entrytype_id for
Entrytype_id which will repeat..

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

Thank u!!That is working fine.....

One more condition is needed, there are more than 30 Article_Type_ID in the table
for eg if EntryType_ID 15 will there for every Article_Type_ID i have to find the
all EntryType_ID which will be for these whole Article_Type_ID's

i have done code like this

SELECT distinct(EntryType_ID) FROM FIS_ArticleType_ProvisionalPdf
group by Article_Type_ID,EntryType_ID
having COUNT(EntryType_ID)<29

here i know the count of distinct(Article_Type_ID)is 29 ie is y i have given like this, how i can find the distinct count over there

Regards,
Divya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 07:29:59

you want to find the distinct count of associated Article_Type_ID for each of repeating Entry_Level_ID?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 07:35:22

if yes


SELECT EntryType_ID,COUNT(*) AS Occurance
FROM Table
GROUP BY EntryType_ID
HAVING COUNT(DISTINCT Article_Type_ID)<(SELECT COUNT(DISTINCT  Article_Type_ID) FROM Table)

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

divyaram
Posting Yak Master

180 Posts

Posted - 2010-03-16 : 07:37:29

quote:
Originally posted by visakh16

you want to find the distinct count of associated Article_Type_ID for each of repeating Entry_Level_ID?

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

I have to display all EntryType_ID which will be there in all Article_Type_ID
other i don't want to display....

Regards,
Divya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 07:46:50

ok then it should be


SELECT EntryType_ID
FROM Table
GROUP BY EntryType_ID
HAVING COUNT(DISTINCT Article_Type_ID)=(SELECT COUNT(DISTINCT  Article_Type_ID) FROM Table)

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

divyaram
Posting Yak Master

180 Posts

Posted - 2010-03-16 : 08:01:49

quote:
Originally posted by visakh16

ok then it should be

SELECT EntryType_ID
FROM Table
GROUP BY EntryType_ID
HAVING COUNT(DISTINCT Article_Type_ID)=(SELECT COUNT(DISTINCT Article_Type_ID) FROM Table)

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

wow!!!! it worked thank u visakh!!!!!!

Regards,
Divya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 08:10:15

welcome (as always)

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources