List only entries with multiple fields

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)

List only entries with multiple fields

Author

Topic

krixham
Starting Member

3 Posts

Posted - 2014-05-28 : 14:36:03

I've been struggling with this for awhile now, and I just can't figure it out. I'm fairly new to MS SQL, but I'm learning. This time I need some help.

I have this for my code so far:


SELECT     CARDHOLDERS.EMPLOYEE_ID, CARDHOLDERS.LAST_NAME, CARDHOLDERS.FIRST_NAME, CARD.CARD_ID, 
                      CARD_LAST_ACCESS.DT_LAST_ACCESS, CARD.CARD_DISABLED
FROM         CARDHOLDERS INNER JOIN
                      CARD_LAST_ACCESS ON CARDHOLDERS.EMPLOYEE_ID = CARD_LAST_ACCESS.EMPLOYEE_ID INNER JOIN
                      CARD ON CARDHOLDERS.EMPLOYEE_ID = CARD.EMPLOYEE_ID RIGHT OUTER JOIN
                          (SELECT DISTINCT CARD_ID
                            FROM          CARD AS CARD_1) AS TR ON TR.CARD_ID = CARD.CARD_ID
WHERE     (CARD.CARD_DISABLED = 0) AND (CARD.CARD_ID > 1)
ORDER BY CARDHOLDERS.LAST_NAME

I only want to list those employees that have multiple rows containing different card_id's. Thus I don't want all employees, just those with more then one card ID. From what I've seen on the internet searches I have done, I think I need to use COUNT(*) but so far no luck.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-05-28 : 14:54:12

See if this is what you are looking for. This gives as many rows as there are card_id's. Is that what you want to get, or do you want to list just one record for each of those multiple cards that an employee has?

SELECT * FROM 
(
SELECT     CARDHOLDERS.EMPLOYEE_ID, CARDHOLDERS.LAST_NAME, CARDHOLDERS.FIRST_NAME, CARD.CARD_ID, 
                      CARD_LAST_ACCESS.DT_LAST_ACCESS, CARD.CARD_DISABLED
FROM         CARDHOLDERS INNER JOIN
                      CARD_LAST_ACCESS ON CARDHOLDERS.EMPLOYEE_ID = CARD_LAST_ACCESS.EMPLOYEE_ID INNER JOIN
                      CARD ON CARDHOLDERS.EMPLOYEE_ID = CARD.EMPLOYEE_ID RIGHT OUTER JOIN
                          (SELECT DISTINCT CARD_ID
                            FROM          CARD AS CARD_1) AS TR ON TR.CARD_ID = CARD.CARD_ID
WHERE     (CARD.CARD_DISABLED = 0) AND (CARD.CARD_ID > 1)
) s
WHERE CardCount > 1
ORDER BY CARDHOLDERS.LAST_NAME

krixham
Starting Member

3 Posts

Posted - 2014-05-28 : 15:05:30

Thanks, James K, for the quick response. I mentioned that I am fairly new, so this question may seem kind of dumb...

[b]Msg 207, Level 16, State 1, Line 12
Invalid column name 'CardCount'.[\b]

I can see where it is not defined, but I'm not real sure where to define that column.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-05-28 : 15:59:22

Ugh! My bad!!

SELECT * FROM 
(
SELECT     CARDHOLDERS.EMPLOYEE_ID, CARDHOLDERS.LAST_NAME, CARDHOLDERS.FIRST_NAME, CARD.CARD_ID, 
                      CARD_LAST_ACCESS.DT_LAST_ACCESS, CARD.CARD_DISABLED
                      , COUNT(*) OVER (PARTITION BY CARDHOLDERS.EMPLOYEE_ID) AS CardCount
FROM         CARDHOLDERS INNER JOIN
                      CARD_LAST_ACCESS ON CARDHOLDERS.EMPLOYEE_ID = CARD_LAST_ACCESS.EMPLOYEE_ID INNER JOIN
                      CARD ON CARDHOLDERS.EMPLOYEE_ID = CARD.EMPLOYEE_ID RIGHT OUTER JOIN
                          (SELECT DISTINCT CARD_ID
                            FROM          CARD AS CARD_1) AS TR ON TR.CARD_ID = CARD.CARD_ID
WHERE     (CARD.CARD_DISABLED = 0) AND (CARD.CARD_ID > 1)
) s
WHERE CardCount > 1
ORDER BY CARDHOLDERS.LAST_NAME

I had it in my mind, but didn't translate it to the posting! Sorry about that.

krixham
Starting Member

3 Posts

Posted - 2014-05-29 : 08:46:29

James K - you ROCK!

That worked perfectly. And I learned a few new tricks.

Thanks!

notmyrealname

98 Posts

Posted - 2014-06-04 : 13:28:14

I think you could also use a derived table that already returns only the cardholders that have multiple cards.

SELECT     CARDHOLDERS.EMPLOYEE_ID, CARDHOLDERS.LAST_NAME, CARDHOLDERS.FIRST_NAME
FROM         (SELECT     EMPLOYEE_ID
                       FROM          CARD
                       GROUP BY EMPLOYEE_ID
                       HAVING      (COUNT(*) > 1)) AS MutipleCards INNER JOIN
                      CARDHOLDERS ON MutipleCards.EMPLOYEE_ID = CARDHOLDERS.EMPLOYEE_ID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources