Specific selection of data from two SQL tables using Mysql database

Question

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?

Suhel Meman · Accepted Answer · 2013-02-15 11:55:20Z

2

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

edited Feb 15, 2013 at 11:55

answered Feb 15, 2013 at 11:44

Suhel Meman

3,8621 gold badge20 silver badges27 bronze badges

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

Comments

Xavi López · Accepted Answer · 2013-02-15 11:49:38Z

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.

Collectives™ on Stack Overflow

Specific selection of data from two SQL tables using Mysql database

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related