CODEWITHSUNDEEP
mysql
dannjoroge
dannjoroge

Reputation: 608

Matching two columns from different tables - MySQL PHP

I have two tables in mysql

I have a column in both tables called regno

I want a query that will display vehicle regno that is not in contributions

What I tried:

SELECT vehicles.regno FROM vehicles,contributions
WHERE vehicles.regno<>contributions.regno
ORDER BY vehicles.regno;

Please help. thanks in advance.

Upvotes: 2

Views: 1957

Answers (3)

darioguarascio
darioguarascio

Reputation: 1087

Try this:

SELECT V.regno FROM vehicles AS V
LEFT JOIN contributions AS C
ON (V.regno = C.regno)
WHERE C.regno IS NULL;

Upvotes: 2

bitkot
bitkot

Reputation: 4504

You can use LEFT JOIN to get the results:

SELECT v.regno
FROM vehicles v

LEFT JOIN contributions c ON c.regno = v.regno -- A LEFT JOIN produces a set of records which matches every entry in the left table (user) regardless of any matching entry in the right table

WHERE c.regno IS NULL -- This will filter out results where right table entry is not available
ORDER BY vehicles.regno;

Upvotes: 1

Danyal Sandeelo
Danyal Sandeelo

Reputation: 12391

SELECT regno FROM vehicles
WHERE regno NOT IN (SELECT regno FROM contributions);

Upvotes: 0

Related Questions