Op-Zyra - 1 year ago 49

MySQL Question

`SELECT DISTINCT CONCAT('TOTAL NUMBER OF LEGS IN A TRIP NUMBER ', T1.TNUM, ' IS NOT EQUAL TO THE LARGEST NUMBER OF LEG IN THE TRIP') AS 'ERR'`

FROM TRIPLEG T1

WHERE MAX(T1.LEGNUM) = (SELECT COUNT(T2.TNUM)

FROM TRIPLEG

WHERE T1.TNUM = T2.TNUM);

with my code, i am trying to finds all trips that violate the following consistency constraint.

the last leg of each trip must be equal to the total number of legs in a trip"

`CREATE TABLE TRIPLEG(`

TNUM DECIMAL(10) NOT NULL,

LEGNUM DECIMAL(2) NOT NULL,

DEPARTURE VARCHAR(30) NOT NULL, DESTINATION VARCHAR(30) NOT NULL,

CONSTRAINT TRIPLEG_PKEY PRIMARY KEY (TNUM, LEGNUM),

CONSTRAINT TRIPLEG_UNIQUE UNIQUE(TNUM, DEPARTURE, DESTINATION),

CONSTRAINT TRIPLEG_FKEY1 FOREIGN KEY (TNUM) REFERENCES TRIP(TNUM) );

INSERT INTO TRIPLEG VALUES( 1, 1, 'Sydney', 'Melbourne');

INSERT INTO TRIPLEG VALUES( 1, 2, 'Melbourne', 'Hobart');

Answer Source

Try following;)

```
SELECT DISTINCT CONCAT('TOTAL NUMBER OF LEGS IN A TRIP NUMBER ', T1.TNUM, ' IS NOT EQUAL TO THE LARGEST NUMBER OF LEG IN THE TRIP') AS 'ERR'
FROM TRIPLEG T1
GROUP BY T1.TNUM
HAVING COUNT(T1.TNUM) <> MAX(T1.LEGNUM)
```