Query for a one to many relationship SQL

sql query to find one to many relationship in same table
many-to-many relationship sql query
one-to-many relationship sql query create table
one-to-many relationship mysql
sql server relationships
sql server one to many join
left join one to many
mysql query one-to-many relationship

I have these tables

Table FoodName - Table FoodIngredients - Table FoodDescrip     
  ID | Name    -    ID | Ingredient    - ID | Descrip | IDFoodN | IDIngred
  01 |  A      -   001 |    AA         - 01 |   abcb  |   01    |  001
  02 |  B      -   002 |    BB         - 02 |   abcb  |   01    |  002
                   003 |    CC           03 |   abcb  |   01    |  003
                                         04 |   abcb  |   02    |  001
                                         05 |   abcb  |   02    |  003

I tried

Select 
  f.Name 
From 
  f.FoodName 
INNER JOIN 
  FoodDescrip D ON f.ID = D.ID 
INNER JOIN 
  FoodIngredients I ON I.ID = D.ID 
WHERE 
  Ingredient LIKE NOT ='BB'"

I need to know that food that doesn't have the ingredient BB, but I need to do the search by the name of the ingredient not by the id. I tried to use Ingredient NOT LIKE but it doesn't work. (I'm using Inner JOIN)


One approach uses conditional aggregation to check for the ingredient:

SELECT
    f1.ID, f1.Name
FROM FoodName f1
INNER JOIN FoodDescrip f2
    ON f1.ID = f2.IDFoodN
INNER JOIN FoodIngredients f3
    ON f2.IDIngred = f3.ID
GROUP BY
    f1.ID, f1.Name
HAVING
    SUM(CASE WHEN f3.Ingredient = 'BB' THEN 1 ELSE 0 END) = 0

SQL One-to-Many Relationships and JOINS – TruthSeekers, Skip to content. TruthSeekers. Main Menu. Tutorials · Blog · Services · Portfolio · Contact. The One-to-Many relationship is defined as a relationship between two tables where a row from one table can have multiple matching rows in another table. This relationship can be created using Primary key-Foreign key relationship. In the One-to-Many Relationship in SQL Server, for example, a book can have multiple authors.


You can try this. Get FoodId which has ingredient 'BB' and then retrieve all food which do not have FoodId retrieved earlier.

SELECT *
FROM   FoodName
WHERE  ID NOT IN (   SELECT d.IDFoodN
                     FROM   FoodIngredients i
                            JOIN FoodDescrip d ON i.ID = d.IDIngred
                     WHERE  i.Ingredient = 'BB' );

One-to-One, One-to-Many Table Relationships in SQL Server, One-to-One (1-1) relationship is defined as the relationship between two tables where both the tables should be associated with each other  233 Posts. SQL Query to get data from two table with one to many relationship Jul 04, 2011 04:45 AM|NidaNovice|LINK. Hi I have two tables: EMP with EmpID(int) and EmpName (nvarchar 100)fields. and a second table Distribution with fields JobNo(nvarchar50),Distribute1(int),Distribute2(int)


I would use NOT EXISTS for this one. (Or NOT IN like AB_87’s answer.) Your FoodDescrip table is a cross reference between food and ingredients. You want every food that doesn’t have a cross reference to the BB ingredient.

SELECT f.Name 
FROM FoodName f 
WHERE NOT EXISTS 
(
    SELECT * 
    FROM FoodDescrip d 
    JOIN FoodIngredients i ON i.ID = d.IDIngred 
    AND i.Ingredient = ‘BB’
    AND d.IDFoodN = f.ID
)

"Select all food we’re not exists a cross reference between that food and the BB ingredient."

The outer query selects from the food table so you can return the food name. The subquery in the NOT EXISTS joins out from the cross reference table to the ingredient table so you can find the ones with the BB name, and is also filtered down to only the food in the outer query. So, if any BBs are found to exist in the subquery linked to the food, the NOT EXISTS will be false, and the food will be removed from the result by the WHERE clause.

This can be tweaked slightly to use NOT IN instead of NOT EXISTS. It’s basically the exact same approach. Here’s that:

SELECT f.Name 
FROM FoodName f 
WHERE f.ID NOT IN 
(
    SELECT d.IDFoodN
    FROM FoodDescrip d 
    JOIN FoodIngredients i ON i.ID = d.IDIngred 
    AND i.Ingredient = ‘BB’
)

"Select all foods where the ID is not in the list of food IDs linked to the BB ingredient in the cross reference table."

I prefer these over Tim’s approach because I think they read nicer. Also, they may perform better because they can throw out a food from the results as soon as they find one reference to the BB ingredient. Tim’s method of conditional aggregation requires counting all the matches and seeing if the result is equal to vs. greater than zero.

Efficient way to join two tables with one to many relationship , In this case, you might say: The table on the "one" side of the "one-to-many" relationship should have a primary key column. The other table should have a foreign-key defined pointing to the primary key on the first table. To return results from both tables you'd add an INNER JOIN clause to join both tables. down-vote due to the provided solution is one for a many-to-many relationship. Even if using for a one-to-many, this is extra overhead when having to run joins. Plus you have to inner join across 3 tables, thus more to type. – andrewWinn Aug 20 '09 at 19:40


SQL Query to get data from two table with one to many relationship , Hi I have two tables: EMP with EmpID(int) and EmpName (nvarchar 100)fields and a second table Distribution with fields JobNo(nvarchar50)  I have two tables A and Band the relation between A to B is A--->one-to-Many--->B. Normally i have one record of B for every record of A. I am trying to write a query which would give me list of ONLY records of A which have more than ONE(MULTIPLE) record(s) in B. I am very confused as I have only done basic sql queries and this one seems


Creating multiple tables and table relationships, Such relationships include: One-to-One, One-to-Many, and Many-to-Many. Our app will be used to manage a library of SQL books and allow users to check out that column in many of our SELECT queries in order to uniquely identify rows;  These relationships need to be represented in the database. Also, when fetching data with SQL, we need to use certain types of JOIN queries to get what we need. There are several types of database relationships. Today we are going to cover the following: One to One Relationships; One to Many and Many to One Relationships; Many to Many Relationships


Getting One Record Per Group From A One-to-Many Join, This is an easy query IF (and only if) "primary office" is explicitly defined you would get a lot of duplicate names (one for each office relationship), but this One-To-Many SQL JOIN Returning Multiple Records Per Grouping. Sql Queries to show contents of the table many to many relationship. Rate this: Please Sign up or sign in to vote. sql parent child relationship query.