The answer to you exact question is "Yes". Didn't help much did it? <grin/>
But I will at least start off by giving some hints. You need to do an inner join on the common column (service_no). You need to use the HAVING clause to select only those entries with more than 2 entries in the result table (which requires a GROUP BY phrase). And you then need to count the number of distinct service_no values which pass that test. I am somewhat hesitant to post the actual code. Why? Because it seems like something which an SQL person should know. But I will be somewhat more helpful that I normally am, and post my solution. select count(distinct a.service_no) as count from service_table as a inner join service_port_table as b on a.service_no = b.service_no where a.service_type='e-lan' group by a.service_no having count(a.service_no) > 2 ; The output is a bit difficult, for me, to read when I enter that in the sqlite3 program, so I also did: .separator "\t" .headers on On Tue, Sep 3, 2013 at 6:00 PM, ChingChang Hsiao < chingchang.hs...@overturenetworks.com> wrote: > > There are 2 related table above. There are service_no 1,2,3,5. Service > 1,2,3 are type e-line, service 5 is type e-lan. Service 1,2,3 own 2 > service_port(sp_no 1,2). Service 5 owns 3 service_port(sp_no 1,2,3). > > What is the count of services have more than 2 service_port(sp) and type > is e-lan? The answer is 1. Could it be described as one sql statement? > > > > CREATE TABLE service_table ( > service_name TEXT, /* name of the service (unique) */ > service_no INTEGER, /* internal service number, assigned by > the system. */ > service_type TEXT, /* Type of Service. Should be one of > the following: > e-lan, e-line, e-tree, ip-forward > The default should be e-line */ > learning_enabled INTEGER, /* Learning Enabled 0=no 1=yes 1=default > */ > col_status TEXT, > cfg_status TEXT); > INSERT INTO "service_table" VALUES('e-line-1',1,'e-line',1,'','committed'); > INSERT INTO "service_table" VALUES('test',2,'e-line',1,'','committed'); > INSERT INTO "service_table" > VALUES('uni1-evc1',3,'e-line',1,'','committed'); > INSERT INTO "service_table" > VALUES('zero-touch-test',5,'e-lan',1,'','committed'); > > > CREATE TABLE service_port_table( > sp_no INTEGER, /* Range 1 to 256 */ > service_no INTEGER, /* The Service this service port belongs > to */ > sp_idx INTEGER, /* This is used to identify the > classification rule: > This should range from 1 to 10000 */ > if_idx INTEGER); /* Interface Index of the underlying > interface */ > > INSERT INTO "service_port_table" VALUES(1,1,1,26); > INSERT INTO "service_port_table" VALUES(2,1,2,43); > INSERT INTO "service_port_table" VALUES(1,2,3,53); > INSERT INTO "service_port_table" VALUES(2,2,4,26); > INSERT INTO "service_port_table" VALUES(1,3,5,29); > INSERT INTO "service_port_table" VALUES(2,3,6,54); > INSERT INTO "service_port_table" VALUES(1,5,20,12); > INSERT INTO "service_port_table" VALUES(2,5,21,58); > INSERT INTO "service_port_table" VALUES(3,5,22,27); > > -- As of next week, passwords will be entered in Morse code. Maranatha! <>< John McKown _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users