0

I need data for rawvalue>0 while the attributeid = 5 and the attribute = 197 I can get it if it is attributeid=5 or attributeid=197 but then i get 2 driveid and if i group by drive id i miss out on information

Example:

SELECT rawvalue, driveid, attributeid 
from labtech.smartattributes 
where rawvalue>0 and attributeid = 197 or rawvalue>0 and attributeid= 198

# rawvalue, driveid, attributeid
'1', '186', '197'
'8', '488', '198'
'8', '489', '198'
'8', '919', '197'
'8', '919', '198'
'1', '1000', '197'
'3', '1129', '197'
'3', '1129', '198'
'1', '1335', '197'
'1', '1335', '198'

as you can see I have redundant info on the driveid side

so if i group by i get:

# rawvalue, driveid, attributeid
'1', '186', '197'
'8', '919', '197'
'1', '1000', '197'
'3', '1129', '197'
'1', '1335', '197'
'37', '1389', '197'
'1', '1545', '197'
'6', '2228', '197'
'8', '2390', '197'
'8', '2391', '197'
'8', '2392', '197'
'1', '3068', '197'
'1', '3069', '197'
'39', '3402', '197'
'100663551', '3441', '19'
'1', '3665', '197'

however, then i can no longer see if both attributes are there

End goal: I need to only display if both attributes are there (5 and 197) however, i do not want redundant driveid values displayed Is this possible?

link to DB: https://www.db-fiddle.com/f/nd7jrmTb7MbypE43zrfBKj/0#&togetherjs=4s61q2QptF

I found what I was looking for:

** Potential SOLUTION:**

SELECT GROUP_CONCAT(rawvalue), driveid, GROUP_CONCAT(AttributeID) from labtech.smartattributes where rawvalue>0 and attributeid in ('5', '198') or rawvalue>0 and attributeid in ('5', '187') or rawvalue>0 and attributeid in ('5', '188') or rawvalue>0 and attributeid in ('5', '197') or rawvalue>0 and attributeid in ('187', '188') or rawvalue>0 and attributeid in ('187', '197') or rawvalue>0 and attributeid in ('187', '198') or rawvalue>0 and attributeid in ('188', '197') or rawvalue>0 and attributeid in ('188', '198') or rawvalue>0 and attributeid in ('197', '198') GROUP BY driveID HAVING COUNT(*) >=3

****I added more as I wanted a certain amount of attributes included

OUTPUT:

GROUP_CONCAT(rawvalue), driveid, GROUP_CONCAT(AttributeID)

10,7,7, 4583, 188,197,198

10,8,8, 6101, 188,197,198

100,1,8, 11311, 187,188,197

100,1,8, 11312, 187,188,197

12,9,11, 12976, 5,187,198

131072,2162716,8, 2390, 187,188,197

131072,2162716,8, 2391, 187,188,197

131072,2162716,8, 2392, 187,188,197

131078,851978,3, 6549, 5,187,188

131078,9175040,2, 6471, 5,187,188

176,3,8, 488, 5,188,198

176,3,8, 489, 5,188,198

179,71,8,8, 5134, 187,188,197,198

21368000,2162776,62062592,24, 4733, 5,187,188,197

2809,239,3,3, 1129, 5,188,197,198

31195657,27459700,2031641,16, 6473, 5,187,188,197

314,804,11, 23905, 5,197,198

32505856,1966569,6, 21160, 187,188,197

32505856,1966569,6, 21161, 187,188,197

327685,27,145179, 23641, 5,187,188

327685,27,145179, 23642, 5,187,188

3316,1,1, 23604, 187,197,198

3322,1,1, 1335, 187,197,198

4,165,22, 3274, 5,187,188

4,165,22, 3275, 5,187,188

4,165,22, 3281, 5,187,188

45,231,1, 17217, 5,187,198

49676288,1966628,6, 21159, 187,188,197

49676288,1966628,6, 21188, 187,188,197

49676288,1966628,6, 21189, 187,188,197

636,1,1703976,19,19, 23781, 5,187,188,197,198

65544,196608,27, 3104, 5,187,188

65544,196608,27, 3105, 5,187,188

65544,196608,27, 3106, 5,187,188

65552,29491200,786432,128, 19260, 5,187,188,197

65552,29491200,786432,128, 19262, 5,187,188,197

65552,29491200,786432,128, 19264, 5,187,188,197

72,1,8, 23583, 187,188,197

774,169,1,1, 3998, 187,188,197,198

786448,236,240, 12743, 5,197,198

8,34608,327685,8,8, 919, 5,187,188,197,198

8,374,1, 12266, 5,187,198

LINK TO RELATED POST: SELECTING with multiple WHERE conditions on same column

WYSPR
  • 1
  • 1
  • would you be so kind to provide a [DB-Fiddle](https://www.db-fiddle.com/)? – SirPilan Jul 18 '18 at 15:05
  • Unrelated, but: combining `and` and `or` without using parentheses is almost always an error –  Jul 18 '18 at 15:06
  • 1
    Which [DBMS product](https://en.wikipedia.org/wiki/DBMS) are you using? "SQL" is just a query language, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using `postgresql`, `oracle`, `sql-server`, `db2`, ... –  Jul 18 '18 at 15:08
  • I edited tag; I am working on the DB-fiddle - getting lots of latency; – WYSPR Jul 18 '18 at 15:10
  • not sure if i did this right here is link: https://www.db-fiddle.com/f/nd7jrmTb7MbypE43zrfBKj/0#&togetherjs=4s61q2QptF – WYSPR Jul 18 '18 at 15:12
  • You have solution there and I am not sure if that query really works (if is working would be a bug in mysql). – Cetin Basoz Jul 18 '18 at 19:03
  • This solution did work for me along with GROUP_CONCAT(ROW) to get all the raw values; however, if this is not a good way to do it I would gladly use the correct way. – WYSPR Jul 18 '18 at 21:07

0 Answers0