April 26, 2016 at 9:08 am
Hi,
How can I get the query below to show the invoice delivery date to be 2015-12-12?
Drop table #postmeta
Drop table #post
create table #post
(
id int,
postvalue varchar(50)
)
create table #postmeta
(
meta_id int,
post_id int,
meta_key nvarchar(50),
meta_value nvarchar(50)
)
insert into #postmeta values (1345,2810,'_billing_first_name','Marco')
insert into #postmeta values (1346,2810,'_billing_last_name','Fernandes')
insert into #postmeta values (1347,2811,'_invoice_no','2810')
insert into #postmeta values (1348,2811,'_invoice_date','2015-12-12')
insert into #post values (2810,'test')
insert into #post values (2811,'test 2')
select p.id as order_id
,max( CASE WHEN pm.meta_key = '_billing_first_name' and p.id = pm.post_id THEN pm.meta_value END ) as _billing_first_name
,max( CASE WHEN pm_inv.meta_key = '_invoice_date' and p.id = pm_inv.post_id THEN pm_inv.meta_value END ) as _invoice_delivery_date
from #post p
left join #postmeta pm on p.id = pm.post_id
left join #postmeta pm_inv on str(p.id) = pm_inv.meta_value and pm_inv.meta_key='_invoice_no'
where p.id = 2810
group by p.id
thanks
Craig.
April 26, 2016 at 9:16 am
Try explaining why it should. Looking at the data, you are getting just what you asked for, the data where post_id = 2810. If there is more to your requirements, you need to explain it.
April 26, 2016 at 9:20 am
In your join you have this,
pm_inv.meta_key='_invoice_no'
In your case statement in the select you have this,
pm_inv.meta_key = '_invoice_date'
Won't those be mutually exclusive?
April 26, 2016 at 9:21 am
ok, yes I know it looks confusing, but I need to programmatically match the invoice no from post_id.
e.g. Marco Ferandes, the post id is 2810, but if I search the meta_key for "Invoice_no" with meta_value of 2810 I find the post id of 2811, therefore I want to return also details that have post id 2811 ?
any further rows that have invoice data will have post id of 2811 for this user.
does that make sense?
April 26, 2016 at 9:25 am
ZZartin (4/26/2016)
In your join you have this,
pm_inv.meta_key='_invoice_no'In your case statement in the select you have this,
pm_inv.meta_key = '_invoice_date'Won't those be mutually exclusive?
yes. I know that bit is wrong.. I cant figure it out.
April 26, 2016 at 9:32 am
Craig, I know this doesn't answer your question, but if you have any influence over the structure of your database, please do not use this Entity - Attribute - Value design. Why not? Well, you've already seen how difficult it is to query. Also, data integrity is going to suffer, because, short of implementing a raft of complicated triggers, you've no way of ensuring that invoice numbers are valid, dates are correct and customers are actual customers.
John
April 26, 2016 at 9:35 am
Are you able to change your database design? This design will give you nightmares and will also cause poor performance. Please consider this before building additional functionality on this system. It reminded me of this horror story: https://www.simple-talk.com/opinion/opinion-pieces/bad-carma/
I'll try to come back with a solution.
April 26, 2016 at 9:50 am
You might need to use a recursive CTE if you can nest more than one level.
WITH postmeta AS(
SELECT *
FROM #postmeta
WHERE post_id = @id
UNION ALL
SELECT pm.meta_id,
@id,
pm.meta_key,
pm.meta_value
FROM #postmeta pm
WHERE pm.post_id in( SELECT r.post_id
FROM #postmeta r
WHERE r.meta_value = CAST( @id AS varchar(10))
AND r.meta_key = '_invoice_no')
)
SELECT p.id as order_id
,max( CASE WHEN pm.meta_key = '_billing_first_name' and p.id = pm.post_id THEN pm.meta_value END ) as _billing_first_name
,max( CASE WHEN pm.meta_key = '_invoice_date' and p.id = pm.post_id THEN pm.meta_value END ) as _invoice_delivery_date
from #post p
left join postmeta pm on p.id = pm.post_id
where p.id = @id
group by p.id;
Remember, with a good DB design this would be as easy as SELECT columns FROM table WHERE id = @id;
April 26, 2016 at 10:05 am
thanks everyone, but i cant amend the structure. this is a wordpress meta table and is a nightmare
April 27, 2016 at 2:05 am
Luis Cazares (4/26/2016)
You might need to use a recursive CTE if you can nest more than one level.
WITH postmeta AS(
SELECT *
FROM #postmeta
WHERE post_id = @id
UNION ALL
SELECT pm.meta_id,
@id,
pm.meta_key,
pm.meta_value
FROM #postmeta pm
WHERE pm.post_id in( SELECT r.post_id
FROM #postmeta r
WHERE r.meta_value = CAST( @id AS varchar(10))
AND r.meta_key = '_invoice_no')
)
SELECT p.id as order_id
,max( CASE WHEN pm.meta_key = '_billing_first_name' and p.id = pm.post_id THEN pm.meta_value END ) as _billing_first_name
,max( CASE WHEN pm.meta_key = '_invoice_date' and p.id = pm.post_id THEN pm.meta_value END ) as _invoice_delivery_date
from #post p
left join postmeta pm on p.id = pm.post_id
where p.id = @id
group by p.id;
Remember, with a good DB design this would be as easy as SELECT columns FROM table WHERE id = @id;
Hi, this works for an id that is known.. how can I amend this query when I dont know the id.. ie run the query to loop through a table with different ids but show the correct invoice details
April 27, 2016 at 8:22 am
cajsoft (4/27/2016)
Luis Cazares (4/26/2016)
You might need to use a recursive CTE if you can nest more than one level.
Remember, with a good DB design this would be as easy as SELECT columns FROM table WHERE id = @id;
Hi, this works for an id that is known.. how can I amend this query when I dont know the id.. ie run the query to loop through a table with different ids but show the correct invoice details
Can you post an example?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply