Joining two tables when one table has multiple corresponding values

For some background, what I have is an ordering system that populates a database. In the database there are two tables, order_Parent, order_Children.

order_Parent contains broader information such as ID, shippingDesination, createdBy, highPriority. There are more columns but the ones that I am focused on are the ID column and highPriority for order_Parent.

When looking at the order_children table there can be multiple children to one parent order. The children are the individual pieces of the order and some of the columns are itemDescription, quantity, priority, linkID. Again there are more columns but for the order_Children table I’m only worried about the linkID column and the priority column.

An example, let’s say that John Smith orders 3 screws, 2 nuts, 1 bolt. These all can have different priorities in the priority column. What I am worried about is if it is a priority 1, which means I need to expedite the order.

So after completing that order the tables will look like:

order_Parent:
ID: 15
shippingDesination: 123 Road St
createdBy: John Smith
highPriority: null

order_Children:
itemDescription: screws
quantity: 3
priority: 1
linkID: 15

itemDescription: nuts
quantity: 2
priority: 0
linkID: 15

itemDescription: bolts
quantity: 1
priority: 0
linkID: 15

So here we can see that the children can have different priorities. What I then want to do is update the order_Parent highPriority row to 1 if any corresponding children have priority 1.

What I have thought of was to join the tables together by using the linkID and ID as a common point. But I don’t know how to join when there are multiple child instances of the same ID as the parent.

After I make the table I believe the next step would be to look through the child_Order priorities and if there is a 1, use the UPDATE function to update the corresponding row in the order_Parent table.

This is what I have so far but it returns way too many rows, a lot of the rows seem to be duplicates but I am unsure why because I have just started SQL. I also have not added the UPDATE function to the query but would love to know what direction to go in.

    SELECT Parent.ID AS ID
    ,Parent.HighPriority AS HighPriority
    ,order_Children.Priority AS Priority
    
    FROM order_Parent, order_Children
    INNER JOIN order_parent AS Parent ON Parent.ID = order_Children.linkID

Answer

This syntax will create a CROSS JOIN.

FROM order_Parent, order_Children

Another good example of CROSS JOINs.

The syntax you are looking for

SELECT Parent.ID AS ID
    ,Parent.HighPriority AS HighPriority
    ,order_Children.Priority AS Priority
    FROM order_Children
    INNER JOIN order_parent AS Parent ON Parent.ID = order_Children.linkID