Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | The Published Post Puzzle

In this puzzle you have to find out the published posts. We also have to check for post from wp_postmeta table, here for each post id we should not have any meta_key = ‘Wanted’ and one meta_key should be ‘DocID’. Please check out the sample input values and sample expected output below.

Can you do that in simple query ?

Sample Input

wp_post table

ID Title Staus
123 Title 123 published
124 Title 124 published
125 Title 125 draft
126 Title 126 published
127 Title 127 published

wp_postmeta table

meta_id post_id meta_key meta_value
1 123 DocID xyz123
2 123 wanted yes
3 123 another hello
4 124 DocID xyz124
5 124 name Bill
6 124 another hello
7 125 DocID xyz125
8 125 wanted yes
9 125 another hello
10 126 DocID xyz126
11 126 name Trevor
12 126 another hello
13 127 MK1 MV1
14 127 name Trevor
15 127 another hello

Expected Output

ID Title Staus
124 Title 124 published
126 Title 126 published

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE wp_post	
(
	 ID	INT 
	,Title VARCHAR(1000)
	,Staus VARCHAR(1000)
)
GO

INSERT INTO wp_post VALUES
('123',	'Title 123',	'published'),	
('124',	'Title 124',	'published'),
('125',	'Title 125',	'draft'),
('126',	'Title 126',	'published'),
('127',	'Title 127',	'published')
GO

CREATE TABLE wp_postmeta	
(		
	 meta_id INT	
	,post_id INT	
	,meta_key VARCHAR(1000)	 
	,meta_value VARCHAR(1000)
)
GO

INSERT INTO wp_postmeta VALUES
(1	,123	,'DocID'	,'xyz123'),
(2	,123	,'wanted'	,'yes'),
(3	,123	,'another'	,'hello'),		
(4	,124	,'DocID'	,'xyz124'),
(5	,124	,'name'	,'Bill'),
(6	,124	,'another'	,'hello'),
(7	,125	,'DocID'	,'xyz125'),
(8	,125	,'wanted'	,'yes'),
(9	,125	,'another'	,'hello'),
(10	,126	,'DocID'	,'xyz126'),
(11	,126	,'name'	,'Trevor'),
(12	,126	,'another'	,'hello'),
(13	,127	,'MK1'	,'MV1'),
(14	,127	,'name'	,'Trevor'),
(15	,127	,'another'	,'hello')
GO




--
--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution – 1


--
 

SELECT *
FROM wp_post p
WHERE p.Staus ='published'
AND NOT EXISTS ( SELECT 1 FROM wp_postmeta m WHERE ( m.meta_key = 'wanted' AND m.meta_key IS NOT NULL ) AND m.post_id = p.Id )
AND EXISTS ( SELECT 1 FROM wp_postmeta m WHERE ( m.meta_key = 'DocID' AND m.meta_key IS NOT NULL ) AND m.post_id = p.Id  )

--

Execution


--
 

/*------------------------
SELECT *
FROM wp_post p
WHERE p.Staus ='published'
AND NOT EXISTS ( SELECT 1 FROM wp_postmeta m WHERE ( m.meta_key = 'wanted' AND m.meta_key IS NOT NULL ) AND m.post_id = p.Id )
AND EXISTS ( SELECT 1 FROM wp_postmeta m WHERE ( m.meta_key = 'DocID' AND m.meta_key IS NOT NULL ) AND m.post_id = p.Id  )
------------------------*/
ID          Title                                                                                                                                                                                                                                                            Staus
----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
124         Title 124                                                                                                                                                                                                                                                        published
126         Title 126                                                                                                                                                                                                                                                        published

(2 row(s) affected)

--

Solution – 2


--
 


Select ID FROM wp_post where [staus] = 'Published' AND ID in 
(
Select post_ID from wp_postmeta WHERE meta_key = 'DocID'
EXCEPT
Select post_ID from wp_postmeta WHERE meta_key ='wanted'
)



--

Solution – 3


--
 


Select p.* from wp_post p 
INNER JOIN wp_postmeta m1 ON m1.post_id = p.id
LEFT JOIN wp_postmeta m2 ON m2.meta_key ='wanted' and m1.post_id = m2.post_id
WHERE m1.meta_key = 'DocID' AND m2.post_id is null
AND p.Staus = 'Published'


--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

Advertisements