MYSQL multiple tables in one query

Question

There are two tables named Customers and Payments. They both have the "CustomerNumber"

Here's what i am trying to do.

Select checkNumber, amount, CustomerNumber, CustomerName
FROM Payments, Customers

And i get an error saying : Unknown column....` in 'field list'

I also tried doing this query

Select checkNumber, amount, Payments.CustomerNumber, CustomerName
FROM Payments, Customers

It didn't work T_T

i tried this one

Select checkNumber, amount, customerNumber, customerName FROM payments, customers

I get this error "Column 'customerNumber' in field list is ambiguous"

Please add the real code and real table names. As is I'm tempted to close as too vague. — Michael Durrant
– Michael Durrant, Commented Oct 12, 2012 at 16:28
There, i just edited it and put the real table names. Sorry about that — LinearXCon
– LinearXCon, Commented Oct 12, 2012 at 16:53

Ta Duy Anh · Accepted Answer · 2012-10-12 17:16:43Z

1

This error happends when there're 2 columns with the same name in 2 tables, so you have to specify the same column in which table, i.e :

Select checkNumber, amount, Customers.CustomerNumber, CustomerName
FROM Payments, Customers

or try to make all of your table name and column quoted in ` like this:

Select `checkNumber`, `amount`, `Payments.CustomerNumber`, `CustomerName`
FROM `Payments`, `Customers`

edited Oct 12, 2012 at 17:16

answered Oct 12, 2012 at 16:58

Ta Duy Anh

1,4889 silver badges16 bronze badges

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

1 Comment

Joysn Over a year ago

be aware that MySQL is case sensitive with table names when the MySQL server being used is hosted on a Linux machine. `SELECT checkNumber, amount, Customers.CustomerNumber, CustomerName FROM Payments, Customers WHERE Customers.CustomerNumber = Payments.CustomerNumber' should definitely work. The WHERE clause, or the JOIN clause as posted earlier is highly recommended when u want to read the correct payments for the customers :)

Barranka · Accepted Answer · 2012-10-12 16:54:27Z

0

Are your tables named 1 and 2?

If they are, then mysql is probably not recognizing 1 and 2 as table names, but as numbers. Try enclosing the table names with backticks:

select `1`.CustomerName, lastName, street, state
from `1`, `2`

By the way, this will give you every possible combination of rows... be careful (or use a join)

UPDATE

Given the new data in your comment:

Check the field names... field names must be written exactly as the names in the table(s). Notice that you're writing payments.customerNumber in the select section and payments.customersNumber in the from section

edited Oct 12, 2012 at 16:54

answered Oct 12, 2012 at 16:26

Barranka

21.1k14 gold badges72 silver badges89 bronze badges

2 Comments

LinearXCon Over a year ago

SELECT checkNumber, amount, payments.customerNumber, customers.customerName FROM payments INNER JOIN Customers ON payments.customersNumber=customers.customersNumber ----------This is what im doing right now and im still getting an error and no they arent named 1 and 2. Those are just examples.

LinearXCon Over a year ago

that didn't work. I just checked the spelling again. like you said it was wrong. But that didn't solve anything.

Collectives™ on Stack Overflow

MYSQL multiple tables in one query

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related