SELECT from different 3 tables with one SQL query

Question

I am trying to get a number of users with role - subscriber, number of two post types and a meta value from a post by using the SQL query:

SELECT 
SUM(CASE WHEN meta_key='wp_capabilities' AND meta_value LIKE '%subscriber%' THEN 1 ELSE 0 END) AS users,
SUM(CASE WHEN post_type='post' AND post_status='publish' THEN 1 ELSE 0 END) AS posts,
SUM(CASE WHEN post_type='updates' AND post_status='publish' THEN 1 ELSE 0 END) AS updates, meta_value AS version 
FROM wp_usermeta,wp_posts,wp_postmeta WHERE meta_key='content_version' AND post_id=1

There is no results and the error displayed "#1052 - Column 'meta_key' in field list is ambiguous"

Can anybody please help what I am doing wrong?

The results view I want to get should be like:

| users |  posts  |  updates  |  version  |
-------------------------------------------
|   2   |    5    |    2      |     1     |

Thank you

UPDATE:

When I updated the query with table aliases:

SELECT 
SUM(CASE WHEN a.meta_key='wp_capabilities' AND a.meta_value LIKE '%subscriber%' THEN 1 ELSE 0 END) AS users,
SUM(CASE WHEN b.post_type='post' AND b.post_status='publish' THEN 1 ELSE 0 END) AS posts,
SUM(CASE WHEN b.post_type='updates' AND b.post_status='publish' THEN 1 ELSE 0 END) AS updates, c.meta_value AS version 
FROM wp_usermeta AS a, wp_posts AS b, wp_postmeta AS c WHERE c.meta_key='content_version' AND post_id=1

It is bringing wrong results, seems SUM is calculating every record in table. Has any one had this issue before? I am using 5.5.44-MariaDB

ambiguous means that the column name appears in two tables. Add the table name to each reference to meta_key. For example WHERE wp_usermeta.meta_key='content..... — john elemans
– john elemans, Commented Nov 10, 2015 at 23:14
Hi @johnelemans it brings NULL to all results but there is definitely numbers — qqruza
– qqruza, Commented Nov 10, 2015 at 23:17
Build the query part by part. Do the first sum to make sure it works. Then add the second part. etc. At which part does it fail? — john elemans
– john elemans, Commented Nov 10, 2015 at 23:19
As soon is there: SUM(CASE WHEN wp_usermeta.meta_key='wp_capabilities' AND wp_usermeta.meta_value LIKE '%subscriber%' THEN 1 ELSE 0 END) AS users — qqruza
– qqruza, Commented Nov 10, 2015 at 23:23

Drew · Accepted Answer · 2015-11-11 16:18:28Z

with the 1052 error, it is simply saying that after it scours the tables, it does not know which table your column should come from as that column name appears in more than one.

In your case, you have 3 tables. You need to qualify the meta_key column with the table that is in your head but not in my db engines head (as it is ambiguous there).

If that means doing a tablename. (dot) or alias name dot infront of the column name, then that is the way to go. By the way, that is always the way to go.

It may be that after correcting it for one column, you are on to the next error. Proceed accordingly.

Edit: You wanted to see an example, see below.

create table car
(   -- a particular car on the lot, not a make and model in general
    id int auto_increment primary key,
    carId int not null, -- this is a lookup in some other table to identify year/make/model (non unique)
    paintId int not null, -- as this is a particular car, it has a particular color !
    category varchar(20), -- lease, for-sale, demo, dumpItFast, etc
    price int not null
    -- Foreign Key constraints not shown such as carId and paintId
    -- other indexes not shown
);

create table paint
(   id int auto_increment primary key,
    name varchar(20) not null,
    category varchar(50) -- metallic, undercarriage, matte, clear coat
);

select id,category,price,name
from car
join paint
on paint.id=car.paintId

Error Code: 1052. Column 'id' in field list is ambiguous

select car.id,category,price,name
from car
join paint
on paint.id=car.paintId

Error Code: 1052. Column 'category' in field list is ambiguous

select car.id,car.category,price,name
from car
join paint
on paint.id=car.paintId

-- that would succeed

Frankly it is best to use aliases on all the columns so there is no doubt. Otherwise the next guy that looks at it, even you, is always guessing.

Collectives™ on Stack Overflow

SELECT from different 3 tables with one SQL query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related