November 28, 2017 at 10:35 am
I have a Master table.This table contains two fields
Listings(Master Table)--------
TableID EntityID
30047 100
30047 101
30047 102
In above table TableID (30047) contains 15 lacks entityids(103,104,105......)Total count of Records nearly crores.
Bindings(ChildTable)
Entityid Fid Value
100 7100 JK
101 7100 JK
102 7100 JK
103 7101 VV
104 7101 VV
105 7101 VV
106 7102 22-nov-2017
107 7102 22-nov-2017
ABOVE TABLE fid 7100 contains 140000 records and it is value is 'JK' 7101 contains 120000 records and it is value is 'vv'
7102 contains 10000 records and it is value is '22-nov-17' i NEED below output.
o/p
COUNT(1) VALUE
140000 JK
120000 VV
100000 22-nov-17
I tried below query. But it is not working and also taking more time to execute.I created index on fid and entityid and value columns
select count(1),vb.Value from Listings el inner join Bindings vb on vb.fieldid=7100 and el.EntityID=vb.EntityID
where TableID=30047 group by vb.value
select count(1) count,vb.Value from Listings el inner join Bindings vb on vb.fieldid=7102 and el.EntityID=vb.EntityID and vb.value in ('22-Nov-2017') inner join ValueBindings vb1 on vb.fieldid=7100 and el.EntityID=vb1.EntityID and vb1.value in ('JK') where TableID=30047 group by vb.value
select count(1),vb.Value from Listings el inner join Bindings vb on el.EntityID=vb.EntityID where TableID=30047 and exists (select 1 from Bindings v2 where vb.EntityID = v2.EntityID and v2.FieldID=7100 and(vb.value IN('jk'))) -- and exists (select 1 from Bindings v2 where vb.EntityID = v2.EntityID and v2.FieldID=7102 and(vb.value IN('22-Nov-2017'))) --and exists (select 1 from Bindings v3 where vb.EntityID = v3.EntityID and v3.FieldID=7101 and(vbvalue IN('VV'))) group by vb.value
November 28, 2017 at 11:02 am
jkramprakash - Tuesday, November 28, 2017 10:35 AMI have a Master table.This table contains two fields
Listings(Master Table)--------TableID EntityID
30047 100
30047 101
30047 102In above table TableID (30047) contains 15 lacks entityids(103,104,105......)Total count of Records nearly crores.
Bindings(ChildTable)Entityid Fid Value
100 7100 JK
101 7100 JK
102 7100 JK
103 7101 VV
104 7101 VV
105 7101 VV
106 7102 22-nov-2017
107 7102 22-nov-2017ABOVE TABLE fid 7100 contains 140000 records and it is value is 'JK' 7101 contains 120000 records and it is value is 'vv'
7102 contains 10000 records and it is value is '22-nov-17' i NEED below output.
o/p
COUNT(1) VALUE
140000 JK
120000 VV
100000 22-nov-17I tried below query. But it is not working and also taking more time to execute.I created index on fid and entityid and value columns
select count(1),vb.Value from Listings el inner join Bindings vb on vb.fieldid=7100 and el.EntityID=vb.EntityID
where TableID=30047 group by vb.valueselect count(1) count,vb.Value from Listings el inner join Bindings vb on vb.fieldid=7102 and el.EntityID=vb.EntityID and vb.value in ('22-Nov-2017') inner join ValueBindings vb1 on vb.fieldid=7100 and el.EntityID=vb1.EntityID and vb1.value in ('JK') where TableID=30047 group by vb.value
select count(1),vb.Value from Listings el inner join Bindings vb on el.EntityID=vb.EntityID where TableID=30047 and exists (select 1 from Bindings v2 where vb.EntityID = v2.EntityID and v2.FieldID=7100 and(vb.value IN('jk'))) -- and exists (select 1 from Bindings v2 where vb.EntityID = v2.EntityID and v2.FieldID=7102 and(vb.value IN('22-Nov-2017'))) --and exists (select 1 from Bindings v3 where vb.EntityID = v3.EntityID and v3.FieldID=7101 and(vbvalue IN('VV'))) group by vb.value
Not sure what "not working" means, not even sure what you are asking in your question.
November 28, 2017 at 12:00 pm
SELECT COUNT(1),
vb.Value
FROM Listings AS el
INNER JOIN Bindings AS vb
ON vb.fieldid = 7100
AND el.EntityID = vb.EntityID
WHERE TableID = 30047
GROUP BY vb.value;
SELECT COUNT(1) AS count,
vb.Value
FROM Listings AS el
INNER JOIN Bindings AS vb
ON vb.fieldid = 7102
AND el.EntityID = vb.EntityID
AND vb.value IN ( '22-Nov-2017' )
INNER JOIN ValueBindings AS vb1
ON vb.fieldid = 7100
AND el.EntityID = vb1.EntityID
AND vb1.value IN ( 'JK' )WHERE TableID = 30047
GROUP BY vb.value;SELECT COUNT(1) AS count,
vb.Value
FROM Listings AS el
INNER JOIN Bindings AS vb
ON vb.fieldid = 7102
AND el.EntityID = vb.EntityID
AND vb.value IN ( '22-Nov-2017' )
INNER JOIN ValueBindings AS vb1
ON vb.fieldid = 7100
AND el.EntityID = vb1.EntityID
AND vb1.value IN ( 'JK' )
WHERE TableID = 30047
GROUP BY vb.value;
SELECT COUNT(1),
vb.Value
FROM Listings AS el
INNER JOIN Bindings AS vb
ON el.EntityID = vb.EntityID
WHERE TableID = 30047
AND EXISTS ( SELECT 1
FROM Bindings AS v2
WHERE vb.EntityID = v2.EntityID
AND v2.FieldID = 7100
AND (vb.value IN ( 'jk' ))); -- and exists (select 1 from Bindings v2 where vb.EntityID = v2.EntityID and v2.FieldID=7102 and(vb.value IN('22-Nov-2017'))) --and exists (select 1 from Bindings v3 where vb.EntityID = v3.EntityID and v3.FieldID=7101 and(vbvalue IN('VV'))) group by vb.value
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 28, 2017 at 12:02 pm
Doggone it. The formatting is messing up. I've fixed it twice now. I get it right and posting it messes it up.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 28, 2017 at 7:18 pm
Lynn Pettis - Tuesday, November 28, 2017 11:02 AMjkramprakash - Tuesday, November 28, 2017 10:35 AMI have a Master table.This table contains two fields
Listings(Master Table)--------TableID EntityID
30047 100
30047 101
30047 102In above table TableID (30047) contains 15 lacks entityids(103,104,105......)Total count of Records nearly crores.
Bindings(ChildTable)Entityid Fid Value
100 7100 JK
101 7100 JK
102 7100 JK
103 7101 VV
104 7101 VV
105 7101 VV
106 7102 22-nov-2017
107 7102 22-nov-2017ABOVE TABLE fid 7100 contains 140000 records and it is value is 'JK' 7101 contains 120000 records and it is value is 'vv'
7102 contains 10000 records and it is value is '22-nov-17' i NEED below output.
o/p
COUNT(1) VALUE
140000 JK
120000 VV
100000 22-nov-17I tried below query. But it is not working and also taking more time to execute.I created index on fid and entityid and value columns
select count(1),vb.Value from Listings el inner join Bindings vb on vb.fieldid=7100 and el.EntityID=vb.EntityID where TableID=30047 group by vb.valueselect count(1) count,vb.Value from Listings el inner join Bindings vb on vb.fieldid=7102 and el.EntityID=vb.EntityID and vb.value in ('22-Nov-2017') inner join ValueBindings vb1 on vb.fieldid=7100 and el.EntityID=vb1.EntityID and vb1.value in ('JK') where TableID=30047 group by vb.value
select count(1),vb.Value from Listings el inner join Bindings vb on el.EntityID=vb.EntityID where TableID=30047 and exists (select 1 from Bindings v2 where vb.EntityID = v2.EntityID and v2.FieldID=7100 and(vb.value IN('jk'))) -- and exists (select 1 from Bindings v2 where vb.EntityID = v2.EntityID and v2.FieldID=7102 and(vb.value IN('22-Nov-2017'))) --and exists (select 1 from Bindings v3 where vb.EntityID = v3.EntityID and v3.FieldID=7101 and(vbvalue IN('VV'))) group by vb.value
Not sure what "not working" means, not even sure what you are asking in your question.
Actually i am trying to get my output by using first query and last query.First query takes more time to display the count for particular vb.fieldid=7100 (7 seconds).
I tried second query for taking count using two conditions vb.fieldid=7102 and vb.fieldid=7100 by inner join but it is not giving output.(some logic problem)
Third query(and exists) gives fast result .but not giving desired output for all the FieldID.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply