Rhezashan Rhezashan - 1 year ago 68
SQL Question

syntax error when to join or concat mysql

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'


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


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'


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
= 0
=' 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

Answer Source

Try this

    pc.guid as children_url,
    pc.post_parent as post_parent,
    pp.guid as post_paraent_guid,
    pp.post_title as post_parent_title
    wp_posts pc
    left join wp_posts pp on pp.ID=pc.post_parent
    pc.post_type = 'attachment'
    and pc.post_title != 'Auto Draft'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download