trevor1940
asked on
MYSQL: Combine 2 select query's into 1
Hi,
Given the sample data attached How do I combine the following 2 select queries so I return 1 row
The two tables are from the back end of Wordpress
The result would look something like this
EE_data.xlsx
Given the sample data attached How do I combine the following 2 select queries so I return 1 row
The two tables are from the back end of Wordpress
SELECT id p,title p,meta_value m as wanted_code
FROM wp_post p, wp_postmeta m
WHERE p.status ='published'
AND m.meta_key = 'wanted_code' AND m.meta_value REGEXP 'AA|XX'
AND m.post_id = p.id SELECT id p,title p,meta_value m as docid
FROM wp_post p, wp_postmeta m
WHERE p.status ='published'
AND m.meta_key = 'docid'
AND m.post_id = p.id The result would look something like this
id | title | docid | wanted_code
123 | Title 123 | xyz123 | AAA, BBB ,XXXEE_data.xlsx
ASKER
That gives back multiple rows like this
I need 4 columns like this
id | title | wanted_code
123 | Title 123 | xyz123
123 | Title 123 | AAA, BBB ,XXXI need 4 columns like this
id | title | docid | wanted_code
123 | Title 123 | xyz123 | AAA, BBB ,XXX
Sorry we have to use JOIN, pls try this updated..
SELECT * FROM
(
SELECT id p,title p,meta_value m as docid
FROM wp_post p, wp_postmeta m
WHERE p.status ='published'
AND m.meta_key = 'docid'
AND m.post_id = p.id
) as r
INNER JOIN
(
SELECT id p,title p,meta_value m as wanted_code
FROM wp_post p, wp_postmeta m
WHERE p.status ='published'
AND m.meta_key = 'wanted_code' AND m.meta_value REGEXP 'AA|XX'
AND m.post_id = p.id
) as k ON k.id = r.id and r.title = p.titleASKER
Thats better however I get a error @ 'as docid' on line 3 & similar @ 11 remove them works and the return looks like this
id | title | m | id | title | meta_value
123 | Title 123 | xyz123 |123 | Title 123 | AAA, BBB ,XXXASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you
I had to change
[code]
SELECT r.id , r.title , r.m as docid , k.m as wanted_code FROM
[/code]
TO
[code]
SELECT r.id , r.title , r.m as docid , k.meta_value as wanted_code FROM
[/code]
I had to change
[code]
SELECT r.id , r.title , r.m as docid , k.m as wanted_code FROM
[/code]
TO
[code]
SELECT r.id , r.title , r.m as docid , k.meta_value as wanted_code FROM
[/code]
Cool.. welcome :)
Open in new window