Mysql search using where clause in same column

Question

Hello I'm having a problem.

I want to search values in the name column using the where clause. Example table is this with values stored.

table1
--------------------------
id  | name   | anotherid
--------------------------
1   | name1  | 1
2   | name2  | 1
2   | name2  | 2

I have textboxes that I can change how many they appear. For example I have two textboxes. The value on textbox one is 'name1' and the second is 'name2'

My query must find those two values in table1 and, when they're found, their anotherid column must be the same also.

From the first table, All I want to get is this:

table1
--------------------------
id  | name   | anotherid
--------------------------
1   | name1  | 1
2   | name2  | 1

However when a situation like this appears and I still have two textboxes with the same values:

table1
--------------------------
id  | name   | anotherid
--------------------------
1   | name1  | 1
2   | name2  | 1
2   | name2  | 2
3   | name3  | 1

The result must be empty.

Now back to the first table. I used this code.

SELECT * FROM `table1` WHERE name = "name1" and name = "name2"
and
SELECT * FROM `table1` WHERE name = "name1" and name = "name2" group by anotherid

but all I get are empty results.

How do you do this?

Raging Bull · Accepted Answer · 2014-03-19 10:20:55Z

1

The problem is in you WHERE clause,

WHERE name = "name1" and name = "name2" will always return false. Use OR instead.

Try any of these:

1. Selecting with name1,name2 and anotherid=1

SELECT * 
FROM table1 
WHERE (name="name1" OR name="name2") 
AND anotherid=1

The result will be:

id   name    anotherid  
1    name1   1
2    name2   1

2. Grouping by anotherid:

SELECT * FROM table1 
GROUP BY anotherid

Result:

ID  NAME    ANOTHERID
1   name1   1
2   name2   2

3. Grouping by name:

SELECT * FROM table1
GROUP BY name

Result:

ID  NAME    ANOTHERID
1   name1   1
2   name2   1

edited Mar 19, 2014 at 10:20

answered Mar 19, 2014 at 10:09

Raging Bull

18.8k13 gold badges52 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user3436844 Over a year ago

Sorry. I edited my question. you cannot have anotherid be an input.

Raging Bull Over a year ago

Then what you mean by GROUP BY anotherid, my friend?

user3436844 Over a year ago

I was confused when the first line didn't work so I tried many things. I'm kind of new in this.

Raging Bull Over a year ago

@user3436844: Edited my answer. Pick what kind of result you are looking for.

user3436844 Over a year ago

#1 is good but anotherid isn't an input #2 - #3 I edited my question again to give more information

|

Amit · Accepted Answer · 2014-03-19 10:13:33Z

0

 SELECT * FROM `table1` WHERE name = "name1" OR name = "name2"  group by anotherid

That should do.

answered Mar 19, 2014 at 10:13

Amit

3,3093 gold badges23 silver badges31 bronze badges

1 Comment

user3436844 Over a year ago

Sorry. I edited my question. you cannot have anotherid be an input.

Sadikhasan · Accepted Answer · 2014-03-19 10:16:26Z

0

Try this

SELECT * FROM `table1` group by name

answered Mar 19, 2014 at 10:16

Sadikhasan

18.6k23 gold badges85 silver badges126 bronze badges

Collectives™ on Stack Overflow

Mysql search using where clause in same column

3 Answers 3

7 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related