syntax error when to join or concat mysql

Question

I want to retrive the mysql result, but my query didn't work because I am new and computer programming or computer database.

SELECT guid, post_name
FROM `wp_posts`
WHERE `post_parent` = 0 and `post_type` = 'post' and post_title != 'Auto Draft'

Returns:

guid                    post_name   
http://localhost/?p=21  post-2-title
http://localhost/?p=12  post-4-title

And

SELECT guid as children_url, post_parent as post_parent
FROM `wp_posts`
WHERE `post_parent` != 0 and `post_type` = 'attachment' and post_title != 'Auto Draft'

Returns:

children_url                               post_parent  
http://localhost/attachment-post-1            12
http://localhost/attachment-post-2            12
http://localhost/attachment-post-3            12
http://localhost/attachment-post-4            12
http://localhost/attachment-post-5            12
http://localhost/attachment-post-6            12
http://localhost/attachment-post-7            12
http://localhost/attachment-post-8            12
http://localhost/attachment-post-9            21
http://localhost/attachment-post-10           21
http://localhost/attachment-post-11           21
http://localhost/attachment-post-12           21
http://localhost/attachment-post-13           21
http://localhost/attachment-post-14           21
http://localhost/attachment-post-15           21
http://localhost/attachment-post-16           21

I have tried several query but didn't succeed,

SELECT guid as children_url, post_parent as post_parent
FROM `wp_posts`
WHERE `post_parent` != 0 and `post_type` = 'attachment' and post_title != 'Auto Draft'
  and SELECT guid, post_name FROM `wp_posts`
      WHERE `post_parent` = 0 and `post_type` = 'post' and post_title != 'Auto Draft'

Raises the error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT guid, post_name FROM wp_posts WHERE post_parent = 0 and post_type =' at line 1

What I want the result like :

 children_url                              post_parent              post_paraent_guid       post_parent_title
http://localhost/attachment-post-1            12                    http://localhost/?p=12  post-2-title
http://localhost/attachment-post-2            12                    http://localhost/?p=12  post-2-title
http://localhost/attachment-post-3            12                    http://localhost/?p=12  post-2-title
http://localhost/attachment-post-4            12                    http://localhost/?p=12  post-2-title
http://localhost/attachment-post-5            12                    http://localhost/?p=12  post-2-title
http://localhost/attachment-post-6            12                    http://localhost/?p=12  post-2-title
http://localhost/attachment-post-7            12                    http://localhost/?p=12  post-2-title
http://localhost/attachment-post-8            12                    http://localhost/?p=12  post-2-title
http://localhost/attachment-post-9            21                    http://localhost/?p=21  post-4-title
http://localhost/attachment-post-10           21                    http://localhost/?p=21  post-4-title
http://localhost/attachment-post-11           21                    http://localhost/?p=21  post-4-title
http://localhost/attachment-post-12           21                    http://localhost/?p=21  post-4-title
http://localhost/attachment-post-13           21                    http://localhost/?p=21  post-4-title
http://localhost/attachment-post-14           21                    http://localhost/?p=21  post-4-title
http://localhost/attachment-post-15           21                    http://localhost/?p=21  post-4-title
http://localhost/attachment-post-16           21                    http://localhost/?p=21  post-4-title

Any help would appreciate.

Thank you

You want four columns, and asking for only two columns. Or did I just misread your question? — DDMC
– DDMC, Commented Oct 5, 2016 at 7:41
you are right, I am sorry because of my limited knowledge about database query — Rhezashan
– Rhezashan, Commented Oct 5, 2016 at 7:56

Max P. · Accepted Answer · 2016-10-05 07:45:28Z

1

Try this

SELECT
    pc.guid as children_url,
    pc.post_parent as post_parent,
    pp.guid as post_paraent_guid,
    pp.post_title as post_parent_title
FROM
    wp_posts pc
    left join wp_posts pp on pp.ID=pc.post_parent
WHERE
    pc.post_type = 'attachment'
    and pc.post_title != 'Auto Draft'
ORDER BY
    pc.guid

answered Oct 5, 2016 at 7:45

Max P.

5,6892 gold badges17 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Rhezashan Over a year ago

anyway also how to limit retrieve from only categoryId 2?

Max P. Over a year ago

add where condition WHERE pc.post_type = 'attachment' and pc.post_title != 'Auto Draft' and categoryId=2

Rhezashan Over a year ago

I am sorry but Unknown column 'categoryId' in 'where clause', I use wordpress database

Max P. Over a year ago

I don't have wordpress DB, so I can't say how it is organized

Collectives™ on Stack Overflow

syntax error when to join or concat mysql

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related