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); This email and attachments may contain privileged or confidential information intended only for the addressee(s) indicated. The sender does not waive any of its rights, privileges or protections respecting this information. If you are not the named addressee, an employee, or agent responsible for sending this message to the named addressee (or this message was received by mistake), you are not authorized to read, print, retain, copy or disseminate this message or any part of it. If received in error, please notify us immediately by e-mail, discard any paper copies and delete all electronic files of the email. Computer viruses can be transmitted via email. The recipient should check this email and any attachments for viruses. Email transmission cannot be guaranteed to be secured or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender accepts no liability for any damage caused by any transmitted viruses or errors or omissions in the contents of this message. Overture Networks, Inc. 637 Davis Drive, Morrisville, NC USA 27560 www.overturenetworks.com _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users