Styjun

So I have the "main" table (A) with fields: id, order_number, order_name and table (B) with fields: id, fk.order_number, tracking_number

Table (A) is responsible for keeping track of each order, while table (B) stores all associated tracking information per each order.

What I am trying to accomplish is to query each order from table A and join table B to show the first tracking number that has been stored for each order, almost like a limit 1 (return only the first stored tracking number for each order).

How I am doing this currently is a join between table A and table B on the order_number field, but I am using the GROUP BY tableA.order_number at the end of the statement.

select tablea.order_number, tablea.order_name, tableb.tracking_number
from tablea
join table b
on tablea.order_number = tableb.order_number
group by tablea.order_number

I guess the question revolves around, what is the default group by ordering when you return multiple rows back from the joined table?
For example, in table A, there is only 1 row, while in tale B there are 2 rows (2 tracking numbers for the order). So, when I group by in this case, does that always take the first match from the joined table where the condition matches the group by? If I removed group by, 2 rows would be returned.

I realize what is happening because I have the group by condition on tableA, and so it only shows the first row because both rows returned from the join have the same order number, which appears to be what I want (limit 1 tracking number per order), but I'm not sure if programmatically I actually did this correctly or if it happens this way because that is how the group by clause works and how I used it here. I just want to limit 1 tracking number from the tableB based on the order_number of table A.

Updated (with example query)

SELECT m.message_id, m.message_date, m.message_order_number, m.message_purchase_order, m.message_vendor_invoice, ve.vendor_email_display, concat(c.customer_first_name, ' ', c.customer_last_name) as customer_name,
min(ti.tracking_information_id) as tracking_information_id, ti.tracking_information_tracking_number, ti.tracking_information_tracking_number_status
FROM email.message m
JOIN email.customer c
ON m.message_tagged_customer_first = c.customer_id and m.message_tagged_customer_last = c.customer_id
JOIN vendor_email ve
ON m.message_sender = ve.vendor_email_id
LEFT JOIN tracking_information ti
ON m.message_order_number = ti.tracking_information_order_number
group by m.message_order_number

In this case, I want to return all information in message table, and the first matching row in table tracking

Group byis for aggregation function as MIN() ,, MAX() , COUNT() . .. and is for define respect which column the aggreagtion function must be performed..

If you are looking for a specific ordered result you should use ORDER BY that work for the columns value as is

select tablea.order_number, tablea.order_name, tableb.tracking_number
from tablea
join table b on tablea.order_number = tableb.order_number
order by tablea.order_number