I have 2 tables. tbl_names
and tbl_section
which has both the id
field in them. How do I go about selecting the id
field, because I always get this error:
1052: Column 'id' in field list is ambiguous
Here's my query:
SELECT id, name, section
FROM tbl_names, tbl_section
WHERE tbl_names.id = tbl_section.id
I could just select all the fields and avoid the error. But that would be a waste in performance. What should I do?
This question is related to
mysql
sql
database
join
mysql-error-1052
SELECT tbl_names.id, tbl_names.name, tbl_names.section
FROM tbl_names, tbl_section
WHERE tbl_names.id = tbl_section.id
What you are probably really wanting to do here is use the union operator like this:
(select ID from Logo where AccountID = 1 and Rendered = 'True')
union
(select ID from Design where AccountID = 1 and Rendered = 'True')
order by ID limit 0, 51
Here's the docs for it https://dev.mysql.com/doc/refman/5.0/en/union.html
If the format of the id's in the two table varies then you want to join them, as such you can select to use an id from one-main table, say if you have table_customes
and table_orders
, and tha id for orders is like "101","102"..."110", just use one for customers
select customers.id, name, amount, date from customers.orders;
In your SELECT
statement you need to preface your id with the table you want to choose it from.
SELECT tbl_names.id, name, section
FROM tbl_names
INNER JOIN tbl_section
ON tbl_names.id = tbl_section.id
OR
SELECT tbl_section.id, name, section
FROM tbl_names
INNER JOIN tbl_section
ON tbl_names.id = tbl_section.id
You would do that by providing a fully qualified name, e.g.:
SELECT tbl_names.id as id, name, section FROM tbl_names, tbl_section WHERE tbl_names.id = tbl_section.id
Which would give you the id of tbl_names
The simplest solution is a join with USING
instead of ON
. That way, the database "knows" that both id
columns are actually the same, and won't nitpick on that:
SELECT id, name, section
FROM tbl_names
JOIN tbl_section USING (id)
If id
is the only common column name in tbl_names
and tbl_section
, you can even use a NATURAL JOIN
:
SELECT id, name, section
FROM tbl_names
NATURAL JOIN tbl_section
Already there are lots of answers to your question, You can do it like this also. You can give your table an alias name and use that in the select query like this:
SELECT a.id, b.id, name, section
FROM tbl_names as a
LEFT JOIN tbl_section as b ON a.id = b.id;
Source: Stackoverflow.com