fork download
copy 
  1. create table Document(
  2.     DocKey char(1), 
  3.     DocTypeKey char(1));
  4. create table DocumentField(
  5.     DocKey char(1) references Document(DocKey),
  6.     FieldId char(1),
  7.     FieldValue varchar(240));
  8.  
  9. insert into Document values ('A', 'X'); 
  10. insert into Document values ('B', 'Y');
  11. insert into Document values ('C', 'Z');
  12.  
  13. insert into DocumentField values ('A', '1', 'PO123');
  14. insert into DocumentField values('A', '2', 'INV123');
  15. insert into DocumentField values('B', '1', 'PO456');
  16. insert into DocumentField values('B', '2', 'INV456');
  17. insert into DocumentField values('C', '1', 'PO123');
  18. insert into DocumentField values('C', '2', 'INV456');
  19.  
  20. select d.* from Document d
  21. where d.DocTypeKey is not null
  22. and (
  23.     select count(*)>=2 from DocumentField df
  24.     where df.DocKey = d.DocKey
  25.     and (
  26.         (df.FieldId = '1' and df.FieldValue like 'PO1%')
  27.         or (df.FieldId = '2' and df.FieldValue like 'INV1%')));
  28.  
Success #stdin #stdout 0s 2964KB
comments (?)
stdin
copy 
Standard input is empty
stdout
copy 
A|X
https://ideone.com/uURwu
language:
SQLite (sqlite 3.27.2)
created:
11 years ago
visibility:
public

Share or Embed source code

Discover > Sphere Engine API

The brand new service which powers Ideone!

Discover > IDE Widget

Widget for compiling and running the source code in a web browser!