Ufdjrw

How do you justify more code being written by following clean code practices?

Are hand made posters acceptable in Academia?

Why didn't Voldemort know what Grindelwald looked like?

Relations between homogeneous polynomials

What is it called when someone votes for an option that's not their first choice?

Do people actually use the word "kaputt" in conversation?

Turning a hard to access nut?

Are all namekians brothers?

Why doesn't Gödel's incompleteness theorem apply to false statements?

What is the purpose of using a decision tree?

Has the laser at Magurele, Romania reached a tenth of the Sun's power?

Sort with assumptions

Would a primitive species be able to learn English from reading books alone?

Is there a POSIX way to shutdown a UNIX machine?

Reason why a kingside attack is not justified

Why is "la Gestapo" feminine?

Air travel with refrigerated insulin

Is this saw blade faulty?

categorizing a variable turns it from insignificant to significant

Reasons for having MCU pin-states default to pull-up/down out of reset

What is this high flying aircraft over Pennsylvania?

Not hide and seek

Can a Knock spell open the door to Mordenkainen's Magnificent Mansion?

New Order #2: Turn My Way

how to fetch data based on and condition

How can I prevent SQL injection in PHP?How to return only the Date from a SQL Server DateTime datatypeHow to check if a column exists in a SQL Server table?How to concatenate text from multiple rows into a single text string in SQL server?Can I concatenate multiple MySQL rows into one field?Should I use the datetime or timestamp data type in MySQL?Insert results of a stored procedure into a temporary tableHow do I create a unique constraint that also allows nulls?How do I UPDATE from a SELECT in SQL Server?Reference - What does this error mean in PHP?

0

I am trying to get all the ID's where the value is equal to hello & world.
Since I have this data structure, how can i get this data?

id value
1 hello
1 world
2 hai
3 hello
3 world

Expected Output

id
1
3

mysql sql-server

share|improve this question

edited Mar 7 at 0:12

terry

asked Mar 7 at 0:01

terryterry

508

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Can you edit the question and add the output you want?
  
  – zedfoxus
  Mar 7 at 0:11
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  mysql or sql-server?
  
  – Dale Burrell
  Mar 7 at 5:41
  

  
  
  
  

add a comment | 

0

I am trying to get all the ID's where the value is equal to hello & world.
Since I have this data structure, how can i get this data?

id value
1 hello
1 world
2 hai
3 hello
3 world

Expected Output

id
1
3

mysql sql-server

share|improve this question

edited Mar 7 at 0:12

terry

asked Mar 7 at 0:01

terryterry

508

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Can you edit the question and add the output you want?
  
  – zedfoxus
  Mar 7 at 0:11
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  mysql or sql-server?
  
  – Dale Burrell
  Mar 7 at 5:41
  

  
  
  
  

add a comment | 

I am trying to get all the ID's where the value is equal to hello & world.
Since I have this data structure, how can i get this data?

id value
1 hello
1 world
2 hai
3 hello
3 world

Expected Output

id
1
3

mysql sql-server

share|improve this question

edited Mar 7 at 0:12

terry

asked Mar 7 at 0:01

terryterry

508

id value
1 hello
1 world
2 hai
3 hello
3 world

id
1
3

mysql sql-server

mysql sql-server

share|improve this question

edited Mar 7 at 0:12

terry

asked Mar 7 at 0:01

terryterry

508

share|improve this question

edited Mar 7 at 0:12

terry

asked Mar 7 at 0:01

terryterry

508

asked Mar 7 at 0:01

terryterry

508

asked Mar 7 at 0:01

terryterry

508

2 Answers
2

active

oldest

votes

1

You need a self join on id to check if records with same id have hello and world in value column.

Following query will give you desired output.

select t1.id 
from @table t1 
 inner join @table t2 
 on t1.id = t2.id 
where t1.value = 'hello' 
 and t2.value = 'world' 

Online Demo

Output

+----+
| id |
+----+
| 1 |
+----+
| 3 |
+----+

share|improve this answer

edited Mar 7 at 4:50

answered Mar 7 at 4:37

PSKPSK

12.8k31834

add a comment | 

0

SELECT id FROM tablename WHERE value = 'hello' OR value = 'world'

or Unique id's

SELECT id FROM tablename WHERE value = 'hello' OR value = 'world' GROUP BY id

Another option, but I'm sure this isn't very efficient.

SELECT * FROM (SELECT id,GROUP_CONCAT(VALUE) AS concatvalues FROM testtable GROUP BY id) mytable2 WHERE concatvalues LIKE '%hello%' AND concatvalues LIKE '%world%'

share|improve this answer

edited Mar 7 at 1:00

answered Mar 7 at 0:07

ChrisChris

1127

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I dont think this works! as the data is id : value format
  
  – terry
  Mar 7 at 0:40
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @terry I've changed my answer based on your expected output. You might want to add GROUP BY id
  
  – Chris
  Mar 7 at 0:47
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  How can it be OR condition? I need all the ID's with both hello and world values
  
  – terry
  Mar 7 at 0:55
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I've re-read your question and I think I've misunderstood what you meant.
  
  – Chris
  Mar 7 at 0:59
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55034074%2fhow-to-fetch-data-based-on-and-condition%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

You need a self join on id to check if records with same id have hello and world in value column.

Following query will give you desired output.

select t1.id 
from @table t1 
 inner join @table t2 
 on t1.id = t2.id 
where t1.value = 'hello' 
 and t2.value = 'world' 

Online Demo

Output

+----+
| id |
+----+
| 1 |
+----+
| 3 |
+----+

share|improve this answer

edited Mar 7 at 4:50

answered Mar 7 at 4:37

PSKPSK

12.8k31834

add a comment | 

1

You need a self join on id to check if records with same id have hello and world in value column.

Following query will give you desired output.

select t1.id 
from @table t1 
 inner join @table t2 
 on t1.id = t2.id 
where t1.value = 'hello' 
 and t2.value = 'world' 

Online Demo

Output

+----+
| id |
+----+
| 1 |
+----+
| 3 |
+----+

share|improve this answer

edited Mar 7 at 4:50

answered Mar 7 at 4:37

PSKPSK

12.8k31834

add a comment | 

You need a self join on id to check if records with same id have hello and world in value column.

Following query will give you desired output.

select t1.id 
from @table t1 
 inner join @table t2 
 on t1.id = t2.id 
where t1.value = 'hello' 
 and t2.value = 'world' 

Online Demo

Output

+----+
| id |
+----+
| 1 |
+----+
| 3 |
+----+

share|improve this answer

edited Mar 7 at 4:50

answered Mar 7 at 4:37

PSKPSK

12.8k31834

select t1.id 
from @table t1 
 inner join @table t2 
 on t1.id = t2.id 
where t1.value = 'hello' 
 and t2.value = 'world' 

+----+
| id |
+----+
| 1 |
+----+
| 3 |
+----+

share|improve this answer

edited Mar 7 at 4:50

answered Mar 7 at 4:37

PSKPSK

12.8k31834

answered Mar 7 at 4:37

PSKPSK

12.8k31834

answered Mar 7 at 4:37

PSKPSK

12.8k31834

0

SELECT id FROM tablename WHERE value = 'hello' OR value = 'world'

or Unique id's

SELECT id FROM tablename WHERE value = 'hello' OR value = 'world' GROUP BY id

Another option, but I'm sure this isn't very efficient.

SELECT * FROM (SELECT id,GROUP_CONCAT(VALUE) AS concatvalues FROM testtable GROUP BY id) mytable2 WHERE concatvalues LIKE '%hello%' AND concatvalues LIKE '%world%'

share|improve this answer

edited Mar 7 at 1:00

answered Mar 7 at 0:07

ChrisChris

1127

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I dont think this works! as the data is id : value format
  
  – terry
  Mar 7 at 0:40
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @terry I've changed my answer based on your expected output. You might want to add GROUP BY id
  
  – Chris
  Mar 7 at 0:47
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  How can it be OR condition? I need all the ID's with both hello and world values
  
  – terry
  Mar 7 at 0:55
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I've re-read your question and I think I've misunderstood what you meant.
  
  – Chris
  Mar 7 at 0:59
  

  
  
  
  

add a comment | 

0

SELECT id FROM tablename WHERE value = 'hello' OR value = 'world'

or Unique id's

SELECT id FROM tablename WHERE value = 'hello' OR value = 'world' GROUP BY id

Another option, but I'm sure this isn't very efficient.

SELECT * FROM (SELECT id,GROUP_CONCAT(VALUE) AS concatvalues FROM testtable GROUP BY id) mytable2 WHERE concatvalues LIKE '%hello%' AND concatvalues LIKE '%world%'

share|improve this answer

edited Mar 7 at 1:00

answered Mar 7 at 0:07

ChrisChris

1127

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I dont think this works! as the data is id : value format
  
  – terry
  Mar 7 at 0:40
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @terry I've changed my answer based on your expected output. You might want to add GROUP BY id
  
  – Chris
  Mar 7 at 0:47
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  How can it be OR condition? I need all the ID's with both hello and world values
  
  – terry
  Mar 7 at 0:55
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I've re-read your question and I think I've misunderstood what you meant.
  
  – Chris
  Mar 7 at 0:59
  

  
  
  
  

add a comment | 

SELECT id FROM tablename WHERE value = 'hello' OR value = 'world'

or Unique id's

SELECT id FROM tablename WHERE value = 'hello' OR value = 'world' GROUP BY id

Another option, but I'm sure this isn't very efficient.

SELECT * FROM (SELECT id,GROUP_CONCAT(VALUE) AS concatvalues FROM testtable GROUP BY id) mytable2 WHERE concatvalues LIKE '%hello%' AND concatvalues LIKE '%world%'

share|improve this answer

edited Mar 7 at 1:00

answered Mar 7 at 0:07

ChrisChris

1127

SELECT id FROM tablename WHERE value = 'hello' OR value = 'world'

SELECT id FROM tablename WHERE value = 'hello' OR value = 'world' GROUP BY id

SELECT * FROM (SELECT id,GROUP_CONCAT(VALUE) AS concatvalues FROM testtable GROUP BY id) mytable2 WHERE concatvalues LIKE '%hello%' AND concatvalues LIKE '%world%'

share|improve this answer

edited Mar 7 at 1:00

answered Mar 7 at 0:07

ChrisChris

1127

answered Mar 7 at 0:07

ChrisChris

1127

answered Mar 7 at 0:07

ChrisChris

1127