CODEWITHSUNDEEP
phpmysql
Louis Stephens
Louis Stephens

Reputation: 790

select from multiple table with mysql

I had my query set up the other day as so 

$query = "SELECT card_id,title,description,meta_description,seo_keywords,price 
FROM cards,card_cheapest order by card_id";

As you can see, I was selecting card_id,title,description,meta_description,seo_keywords from the table cards, and price was coming from cheapest_card. They both have the card_id in common (in both tables). However, I ran into a bit of an issue. When I run the query in navicat lite, I receive an error "card_id is ambiguous". Was I doing something wrong?

Upvotes: 2

Views: 6387

Answers (3)

Jacob
Jacob

Reputation: 43219

When 2 or more tables have a column that is named the same, you have to qualify the table you want the column to be from.

i.e.:

$query = "SELECT cards.card_id,title,description,meta_description,seo_keywords,price
FROM cards,card_cheapest order by card_id";

Furthermore, do you really want to run the query this way, without a WHERE/JOIN-clause to define how to JOIN the two tables?

$query = "SELECT cards.card_id,title,description,meta_description,seo_keywords,price 
FROM cards,card_cheapest WHERE cards.card_id = card_cheapest.card_id 
ORDER BY card_id";

Upvotes: 4

Ares
Ares

Reputation: 5903

When you run queries that get information from multiple tables with shared field names you need to specify from which table you want to extract what field. You do this by specifying the table name before the field name.

In your case you have two options:

cards.card_id or card_cheapest.card_id.

Also I agree with @cularis, you are probably better of doing a join, but still you will need to specify which card_id you want to select: the one from cards or card_cheapest.

Upvotes: 0

Darkzaelus
Darkzaelus

Reputation: 2085

When you have the same column name in two tables you're selecting from, you have to prefix the part in the SELECT with one of the table names (it doesn't matter which if it's the same data)

such as SELECT cards.card_id, ...

EDIT: However, cularis's answer is much more explanatory than mine, and take note about joining the two card_id columns if you want to get correct results.

Upvotes: 1

Related Questions