Minimizing SQL Queries Using Join with One-To-Many Relationship

Minimizing SQL queries using join with one-to-many relationship

You can get it all in a single query with a simple join, e.g.:

SELECT   d.name AS 'department', p.name AS 'name'
FROM     department d
  LEFT JOIN people p ON p.department_id = d.id
ORDER BY department

This returns all the data, but it's a bit of a pain to consume, since you'll have to iterate through every person anyway. You can go further and group them together:

SELECT   d.name AS 'department',
         GROUP_CONCAT(p.name SEPARATOR ', ') AS 'name'
FROM     department d
  LEFT JOIN people p ON p.department_id = d.id
GROUP BY department

You'll get something like this as the output:

department | name
-----------|----------------
sales      | Tom, Bill, Rachel
marketing  | Jessica, John

how data is structured in join clause in one to many relation

In case of one-to-many you should insert the id of the referenced item in the table that is on the many side.

one-to-many case

A person can own many cars but a car is owned by only one person.

In a relational database this is translated with the creation of the person_id inside the car entity. In this way you can retrieve all the cars owned by a person with a simple join.

In case of a many-to-many relationship, you need to create a bridge table between the two entities.

Get owners of the Mazdas query Example

SELECT name, surname
FROM car JOIN person ON car.person_id = person.id
WHERE car.brand = 'mazda'

many-to-many case

Example:

Let's suppose that the requirement has been changed and a car can be owned by many people.

This is translated by adding a new bridge table (e.g. person_car) that has the following columns:

• person_car_id
• car_id
• person_id
• other_columns_that_could_map_the_relationship (e.g. is_the_main_owner)

Get owners of the Mazdas query Example

SELECT DISTINCT name, surname
FROM car JOIN person_car ON person_car.id_car = car.id JOIN person ON person_car.person_id = person.id
WHERE car.brand = 'mazda'

If you see here you can map many-to-many relationship between the two entities and perform a double JOIN (passing through the bridge table) to retrieve the elements.

MYSQL query through many layers of one to many relationships

Without knowing your schema and making some assumptions:

SELECT c.*
FROM
    Institution i
    INNER JOIN Department d ON d.InstitutionID = i.InstitutionID
    INNER JOIN Forums f ON f.DepartmentID = d.DepartmentID
    INNER JOIN `User` u ON u.ForumID = f.ForumID
    INNER JOIN Post p ON p.UserID = u.UserID
    INNER JOIN Comment c ON c.PostID = p.PostID
WHERE
    i.InstitutionID = 42

To use a FK in a one-to-many relationship versus using a join table

Whenever you normalize your database, there is always a performance hit. If you do a join table (or sometimes referred to as a cross reference) the dbms will need to do work to join the right records.

DBMS's these days do pretty well with creating indexes and reducing these performance hits. It just depends on your situation.

Is it more important to have readability and normalization? Then use a join/xref table.

Is this for a small application that you want to perform well? Just make Table B have a FK to its parent.

SQL query one to many relationship join without duplicates

I wouldn't mix data retrieval and data display, which is what I think you are asking about. Do you have some sort of column to indicate which payment should be displayed first? I'm thinking something like:

SELECT columnlist, 
rn = ROW_NUMBER() OVER (PARTITION BY sales.salesID ORDER BY payment.paymentID)
FROM sales JOIN payments ON sales.salesID=payments.salesID

Then, in your GUI, just display the values for the first 3 columns where RN = 1, and blank out the values where RN > 1.

How to efficiently retrieve data in one to many relationships

Try using EXISTS, I suppose, for such case it might be better then joining tables. On my oracle db it's giving slightly better execution time then the sample query, but this may be db-specific.

SELECT first_table.ID, CASE WHEN EXISTS (SELECT * FROM second_table WHERE first_table.ID = second_table.ID) THEN 1 ELSE 0 END FROM first_table

---

Related Topics