MYSQL designing query issue

Question

I am trying to create a query that shows the name and adress of all people who have incurred fines but do not have a driving license. (So do not show up in the driving license table) How would I go about including something who does not show up in a table?

Here are my tables

CREATE TABLE offences (
oOffenceID VARCHAR (30),
oDescription VARCHAR (200),
oMaximumFine INT (10),
CONSTRAINT o_pk
PRIMARY KEY (oOffenceID)
);

CREATE TABLE drivers (
dPersonID VARCHAR (30),
dLicenseNumber INT (20),
dLicenseRemoved ENUM('yes','no'),
dExpiryDate DATE,
CONSTRAINT d_pk
PRIMARY KEY (dLicenseNumber),
CONSTRAINT d_fk
FOREIGN KEY (dPersonId)
REFERENCES People (pPersonID)
);

CREATE TABLE people (
pPersonID VARCHAR (30),
pName VARCHAR (30),
pAddress VARCHAR (50),
pNIN VARCHAR (30),
CONSTRAINT p_pk
PRIMARY KEY (pPersonID)
);

CREATE TABLE vehicle (
vOwnerID VARCHAR (30),
vColour VARCHAR (30),
vModel VARCHAR (30),
vMake VARCHAR (30),
vVehicleID VARCHAR (30),
CONSTRAINT v_pk
PRIMARY KEY (vVehicleID),
CONSTRAINT v_fk
FOREIGN KEY (vOwnerID)
REFERENCES People (pPersonID)
);

CREATE TABLE fines (
fFineID INT (30) AUTO_INCREMENT,
fVehicleID VARCHAR (30),
fPersonID VARCHAR (30),
fTime DATE,
fAmount INT (10),
fOfficerStatement VARCHAR (500),
fOffenceID VARCHAR (30),
CONSTRAINT f_pk
PRIMARY KEY (fFineID),
CONSTRAINT f_fk
FOREIGN KEY (fVehicleID)
REFERENCES vehicle (vVehicleID),
CONSTRAINT f_fk1
FOREIGN KEY (fPersonID)
REFERENCES people (pPersonID),
CONSTRAINT f_fk2
FOREIGN KEY (fOffenceID)
REFERENCES offences (oOffenceID)
);

So far I have

SELECT DISTINCT pName, pAddress
FROM people, fines, drivers
WHERE 
pPersonID <> dPersonID
AND
pPersonID = fPersonID
AND
fAmount > 0

but this doesn't remove all the necessary data. Any help or guidance of the right tools to use would be much appreciated.

Duke Silver · Accepted Answer · 2012-11-23 23:17:02Z

1

Use a LEFT JOIN and check for NULL values

example:

SELECT *
FROM
table_A
LEFT JOIN table_B
ON table_A.id = table_b.a_id
WHERE table_B.a_id IS NULL

answered Nov 23, 2012 at 23:17

Duke Silver

1,6291 gold badge19 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1744093 Over a year ago

Ah awesome thankyou, if its joined together it will be the only one with null values!

Duke Silver Over a year ago

Thanks, glad you found my answer useful. Would you mind accepting this answer?

Collectives™ on Stack Overflow

MYSQL designing query issue

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related