CODEWITHSUNDEEP
mysqlsql
nielsv
nielsv

Reputation: 6800

SQL Join from another table

I have a table segment with segmentCode, Description, pst-active, .... I also have a table subarea with FK SegmentCode, deelgebiedID, Deelgebied, .... Now I would like to select all the segments but also the subareas of the segment. My ideal array would be

segments[[segmentCode, Description, ..., [array with subareas data]],[segmentCode, Description, ..., [array with subareas data]]]

How can I do this in a query? I've tried this but it's just given me segments duplicates ..

Upvotes: 0

Views: 73

Answers (2)

fmgonzalez
fmgonzalez

Reputation: 823

Ok, try this one.

SELECT s.segmentCode, s.Description, a.* 
  FROM segment s 
  LEFT JOIN subarea a 
    ON (s.segmentCode = a.SegmentCode);

I hope it works for you.

Upvotes: 0

SebastianH
SebastianH

Reputation: 2182

There are two ways to achieve this:

Either do one select statement on the database that holds all the required information and build your array from that. The statement would looke something like SELECT * FROM segment seg JOIN subarea sub ON seg.segmentCode = sub.SegmentCode ORDER BY seg.segmentCode, sub.deelgebiedID;. You would then have to iterate through the results, keep track of the segmentCode and create a new segment object whenever the segmentCode changes. While it does not change you simply add the subareas to that object (or create an array).

The other way is to split the process in several selects. First get the segments with SELECT * FROM segment seg ORDER BY seg.segmentCode;. Then loop over the retrieved segments and fetch the subareas for each: SELECT * FROM subarea sub WHERE sub.SegmentCode = PARAMETER ORDER BY sub.deelgebiedID;.

Which approach to choose in your situation is a trade-off between performance (one database statement usually is much better) vs. memory consumption and code complexity.

Upvotes: 1

Related Questions