mysql select all rows with same value in two columns

Question

I have two tables in MySQL:

Table1:
cname
a
b
f

Table2:
character | account
a | 1
b | 2
c | 3
d | 4
e | 5
f | 6
g | 7

I want to select all accounts from two tables which cname = character

I've tried this: SELECT account FROM table1,table2 WHERE cname = character

But it returns empty. I'm sure I'm missing something simple...

Any help would be appreciated.

Now something really strange. I've tested it on SQL Fiddle, it works perfectly, but not in my real database. Additionally, SELECT * FROM table1 WHERE cname = 'a' returns an empty set... — l3utterfly
– l3utterfly, Commented Aug 26, 2013 at 6:38
Perhaps there are blank characters in one or other of the columns. — Strawberry
– Strawberry, Commented Aug 26, 2013 at 11:54
Note also that both you and Dipesh are wrong. character is a reserved word in MySQL. So this query won't work at all, hence why no results are returned. — Strawberry
– Strawberry, Commented Aug 26, 2013 at 11:59

Dipesh Parmar · Accepted Answer · 2013-08-26 04:35:55Z

3

You need to specify your table name into query.

SELECT account FROM table1,table2 WHERE table1.cname = table2.character

OR

Use JOINS

SELECT table2.account FROM table1
LEFT JOIN table2 ON table1.cname = table2.character

answered Aug 26, 2013 at 4:35

Dipesh Parmar

27.3k8 gold badges65 silver badges92 bronze badges

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

7 Comments

l3utterfly Over a year ago

It still returns an empty set.

Dipesh Parmar Over a year ago

@l3utterfly can you please create fiddle at SQL Fiddle so i can have better idea.

l3utterfly Over a year ago

Ok, building it now. FYI, using the second query you suggested returns everything from table2

Dipesh Parmar Over a year ago

@l3utterfly so does not it that you wanted.?

l3utterfly Over a year ago

Sorry, the question was wrong. I've edited it. I've built the database in SQL Fiddle and your script works perfectly... but why doesn't it work in my real database?

|

DB_learner · Accepted Answer · 2013-08-26 11:52:04Z

0

Probably your data may have whitespace or any other special character appended or prepended to it. Make sure your data is fine. Better try using trim.

answered Aug 26, 2013 at 11:52

DB_learner

1,0369 silver badges15 bronze badges

Collectives™ on Stack Overflow

mysql select all rows with same value in two columns

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related