CODEWITHSUNDEEP
mysqlsqlselect
Joro Seksa
Joro Seksa

Reputation: 1583

Specific selection of data from two SQL tables using Mysql database

I have a mysql database and i want to make a specific selection. Here is the scenario:

First table

Road list data about cargo vehicles.

roadListNumber(primary_key), vehicle, driver, startWorkTime, endWorkTime and so on.

Second table

Cargo zones mapped with the primary key of the first table.

roadListNumber(foreign_key), zoneId, timeInside, spentMoney and so on.

The problem is that not every vehicle goes to the cargo zones so some of the road lists are not mapped with cargo zones (the second table ) and here comes my problem and the question:

How can i select all the roadlist from first table and join the result with the second table ( cargo zones ) without skipping the roadlist which are not mapped with cargo zones?

Upvotes: 1

Views: 394

Answers (2)

Suhel Meman
Suhel Meman

Reputation: 3852

You can use LEFT JOIN to get data... also use GROUP BY to avoid repeated data..

SELECT rl.roadListNumber,rl.vehicle,cz.zoneId,cz.spentMoney  
FROM road_list rl 
LEFT JOIN cargo_zone cz on (rl.roadListNumber=cz.roadListNumber) 
GROUP BY rl.roadListNumber

Upvotes: 2

Xavi López
Xavi López

Reputation: 27880

You were already using the correct word (JOIN). You can use a LEFT OUTER JOIN in order to fetch all the records in the first (or left) table, and relate them to their corresponding records from the second (or right) table according to the ON clause.

Notice the usage of the left and right table terms here and how it relates to LEFTOUTER JOIN.

If there's no matching record on the second table, you will get NULL on the second table's columns in the result set. 

SELECT * FROM roadlist rl 
    LEFT OUTER JOIN cargozones cz ON rl.roadlistnumber = cz.roadlistnumber

There are many kinds of JOIN. If you were using an INNER JOIN, for instance, you would only get the records that have a match on the second table.

See this blog post for a good visual explanation on how do SQL joins work: Coding Horror: A Visual Explanation of SQL Joins.

Upvotes: 1

Related Questions