MS SQL 2014 TSQL pulling 3 records

Question

I am trying to pick the 3 rows that have the 3 unique GUID's below:

  SELECT 
    number                                  AS 'ID #', 
    line                                    AS 'Line #', 
    network                                 AS Network,
    FORMAT(SUM(totalCost), N'c', 'en-US')   AS Total 
  FROM 
    theLine 
  WHERE 
    theGuid = '32e1319d-8842-4b98-9a66-c1e694417528' 
  AND 
    theGuid = '61e819a8-a37a-4105-aab0-e34df31d992d' 
  AND
    theGuid = '6675811d-9a32-4b62-a003-87344af03d6b' 
  GROUP BY 
    number, line, network

However, when I run this query I get no records back. If I just do 1 WHERE clause then I get that record related to that GUID but any more renders no results.

But what I need it to do is the following:

------------------------------------
|ID #  |Line # |Network |Total     |
------------------------------------
|1     |1      |USASV   |$5,000.00 |
|2     |1      |FJESC   |$4,920.00 |
|3     |1      |RTDEW   |$598.00   |

So, what am I doing wrong?

That is because there is no row where theGuid is simultaneously all three values. You would need to use IN for this type of thing. — Sean Lange, Jul 12 '17 at 21:08

score 5 · Accepted Answer · edited Jul 12 '17 at 21:13

You can't have 3 different values all in the same row. Either change your AND to OR or use the IN operator.

  SELECT 
    number                                  AS 'ID #', 
    line                                    AS 'Line #', 
    network                                 AS Network,
    FORMAT(SUM(totalCost), N'c', 'en-US')   AS Total 
  FROM 
    theLine 
  WHERE 
    theGuid = '32e1319d-8842-4b98-9a66-c1e694417528' 
  OR
    theGuid = '61e819a8-a37a-4105-aab0-e34df31d992d' 
  OR
    theGuid = '6675811d-9a32-4b62-a003-87344af03d6b' 
  GROUP BY 
    number, line, network

Or:

  SELECT 
    number                                  AS 'ID #', 
    line                                    AS 'Line #', 
    network                                 AS Network,
    FORMAT(SUM(totalCost), N'c', 'en-US')   AS Total 
  FROM 
    theLine 
  WHERE 
    theGuid IN (
                '32e1319d-8842-4b98-9a66-c1e694417528',
                '61e819a8-a37a-4105-aab0-e34df31d992d',
                '6675811d-9a32-4b62-a003-87344af03d6b' 
               )
  GROUP BY 
    number, line, network

score 1 · Answer 2 · answered Jul 12 '17 at 21:07

1

use OR instead of AND in your WHERE clause

answered Jul 12 '17 at 21:07

Ryan

3,475
2
16
12

1

Thanks for providing an answer Ryan – StealthRT Jul 12 '17 at 21:13

score 1 · Answer 3 · answered Jul 12 '17 at 21:09

1

This is a good example use case for IN though i'm not sure why you have a GROUP BY

SELECT 
    number                                  AS 'ID #', 
    line                                    AS 'Line #', 
    network                                 AS Network,
    FORMAT(SUM(totalCost), N'c', 'en-US')   AS Total 
 FROM 
    theLine 
 where theGuid in ('32e1319d-8842-4b98-9a66-c1e694417528','61e819a8-a37a-4105-aab0-e34df31d992d','6675811d-9a32-4b62-a003-87344af03d6b' )
GROUP BY 
number, line, network

answered Jul 12 '17 at 21:09

S3S

24,809
5
26
45

Thanks for providing an answer scsimon – StealthRT Jul 12 '17 at 21:13
No sweat brother. – S3S Jul 12 '17 at 21:13

MS SQL 2014 TSQL pulling 3 records

3 Answers3