Query for a one to many relationship SQL
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
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.