CODEWITHSUNDEEP
wordpressmariadb
qqruza
qqruza

Reputation: 1417

SELECT from different 3 tables with one SQL query

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

Upvotes: 0

Views: 88

Answers (1)

Drew
Drew

Reputation: 24960

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.

Upvotes: 1

Related Questions