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
