sql,sql-server , Select from 3 tables (one to many, many to one relationship)
Select from 3 tables (one to many, many to one relationship)
I have 3 tables: Recipes (1 --- * ) Ingridients ( *---1) Products. I need to obtain Recipes that contains products in a given list or products that are not in list but have a specific flag set. I have a flag in product table (bool). So where clause looks like:
WHERE Product.Caption IN ('A', 'B', 'C') OR (Product.Caption NOT IN ('A', 'B', 'C') AND Product.Flag=TRUE)
Important is: I do not need recipes that contain products in list and also contain other products (not in list and flag is false).
Bellow is an example database dump for MSSQL:
IF EXISTS (SELECT * FROM master.dbo.sysdatabases WHERE name = N'movedb') ALTER DATABASE [movedb] SET SINGLE_USER With ROLLBACK IMMEDIATE
IF EXISTS (SELECT * FROM master.dbo.sysdatabases WHERE name = N'movedb') DROP DATABASE [movedb]
IF NOT EXISTS (SELECT * FROM master.dbo.sysdatabases WHERE name = N'movedb') CREATE DATABASE [movedb]
-- Table structure for table 'Ingridients'
IF object_id(N'Ingridients', 'U') IS NOT NULL DROP TABLE [Ingridients]
CREATE TABLE [Ingridients] (
[Id] INT NOT NULL IDENTITY,
[Quantity] INT DEFAULT 0,
[IdProduct] INT DEFAULT 0,
[IdRecipe] INT DEFAULT 0,
PRIMARY KEY ([Id])
SET IDENTITY_INSERT [Ingridients] ON
-- Dumping data for table 'Ingridients'
INSERT INTO [Ingridients] ([Id], [Quantity], [IdProduct], [IdRecipe]) VALUES (1, 0, 1, 2)
INSERT INTO [Ingridients] ([Id], [Quantity], [IdProduct], [IdRecipe]) VALUES (2, 0, 3, 2)
INSERT INTO [Ingridients] ([Id], [Quantity], [IdProduct], [IdRecipe]) VALUES (3, 0, 4, 2)
INSERT INTO [Ingridients] ([Id], [Quantity], [IdProduct], [IdRecipe]) VALUES (4, 0, 6, 2)
INSERT INTO [Ingridients] ([Id], [Quantity], [IdProduct], [IdRecipe]) VALUES (5, 0, 2, 3)
INSERT INTO [Ingridients] ([Id], [Quantity], [IdProduct], [IdRecipe]) VALUES (6, 0, 4, 3)
INSERT INTO [Ingridients] ([Id], [Quantity], [IdProduct], [IdRecipe]) VALUES (7, 0, 8, 3)
INSERT INTO [Ingridients] ([Id], [Quantity], [IdProduct], [IdRecipe]) VALUES (8, 0, 1, 4)
INSERT INTO [Ingridients] ([Id], [Quantity], [IdProduct], [IdRecipe]) VALUES (9, 0, 6, 4)
INSERT INTO [Ingridients] ([Id], [Quantity], [IdProduct], [IdRecipe]) VALUES (10, 0, 5, 4)
-- 10 records
SET IDENTITY_INSERT [Ingridients] OFF
-- Table structure for table 'Products'
IF object_id(N'Products', 'U') IS NOT NULL DROP TABLE [Products]
CREATE TABLE [Products] (
[Id] INT NOT NULL IDENTITY,
[EasyToFind] BIT DEFAULT 0,
PRIMARY KEY ([Id])
SET IDENTITY_INSERT [Products] ON
-- Dumping data for table 'Products'
INSERT INTO [Products] ([Id], [Caption], [EasyToFind]) VALUES (1, N'ProductA', 0)
INSERT INTO [Products] ([Id], [Caption], [EasyToFind]) VALUES (2, N'ProductB', 0)
INSERT INTO [Products] ([Id], [Caption], [EasyToFind]) VALUES (3, N'ProductC', 0)
INSERT INTO [Products] ([Id], [Caption], [EasyToFind]) VALUES (4, N'ProductD', -1)
INSERT INTO [Products] ([Id], [Caption], [EasyToFind]) VALUES (5, N'ProductE', 0)
INSERT INTO [Products] ([Id], [Caption], [EasyToFind]) VALUES (6, N'ProductF', -1)
INSERT INTO [Products] ([Id], [Caption], [EasyToFind]) VALUES (7, N'ProductG', 0)
INSERT INTO [Products] ([Id], [Caption], [EasyToFind]) VALUES (8, N'ProductH', 0)
INSERT INTO [Products] ([Id], [Caption], [EasyToFind]) VALUES (9, N'ProductI', 0)
INSERT INTO [Products] ([Id], [Caption], [EasyToFind]) VALUES (10, N'ProductJ', 0)
-- 10 records
SET IDENTITY_INSERT [Products] OFF
-- Table structure for table 'Recipes'
IF object_id(N'Recipes', 'U') IS NOT NULL DROP TABLE [Recipes]
CREATE TABLE [Recipes] (
[Id] INT NOT NULL IDENTITY,
PRIMARY KEY ([Id])
SET IDENTITY_INSERT [Recipes] ON
-- Dumping data for table 'Recipes'
INSERT INTO [Recipes] ([Id], [Caption]) VALUES (2, N'RecipeA')
INSERT INTO [Recipes] ([Id], [Caption]) VALUES (3, N'RecipeB')
INSERT INTO [Recipes] ([Id], [Caption]) VALUES (4, N'RecipeC')
-- 3 records
SET IDENTITY_INSERT [Recipes] OFF
Example: If I search for ProductA and ProductE it should give me only RecipeC
Right now I have something like this for MySQL ( it is not final. I can only operate with Ids, I neet somehow to change it to work only with product captions and adapt for MSSQL)
recipes AS r
ingridients i ON i.IdRecipe = r.Id
i.IdProduct IN (1 , 5, 6)
GROUP BY r.Id
HAVING COUNT(*) = (SELECT
ingridients AS ing
ing.IdRecipe = r.Id);
The following sql fetches the recipes that contains no products other than those in the list or having P.EasyToFind=-1.
Where Id not in
from Ingridients I
inner join Products P ON I.IdProduct = P.Id
where P.Caption NOT IN ('ProductA','ProductE')
It works by having an inner query that identifies the unwanted ingredients and fetching the recipes that does not match any of them.
Background: I have a directory with a number of files that are imported to SQL server. Task: Creating a PowerShell script which will pick up files within this directory and use the filenames as in the SQL query. Ultimate objective: To display SQL results besides the filenames but the resultset...
I have a TestPack table that can have n no of Lines associated with it. But a Line may exist without a TestPack. Later on, a Line might be assigned a TestPack#. What this relationship is called and how do I implement this relationship in SQL Server 2008?...
I am creating a very small, simple CRM for a company, they require the function to be able to view the last 25 orders via the dashboard. The orders are added via a Order-add form within the CRM. When adding the following code to the CRM I get an error:...
I have this issue, I need your help: EmpNo Shift_Date Shift1 shift2 stamp_Date stamp_Time stamp_Type 426 2015-04-12 A 2015-04-12 10:09:00.000 I 426 2015-04-15 B C 2015-04-15 23:46:00.000 I 426 2015-04-15 B C 2015-04-15 23:45:00.000 O 426 2015-04-16 OF 2015-04-16 07:02:00.000 O 426 2015-04-17 A 2015-04-17 07:34:00.000 I 426 2015-04-18 A...
I am creating a key-wording module where I want to search data using the comma separated words.And the search is categorized into comma , and minus -. I know a relational database engine is designed from the principle that a cell holds a single value and obeying to this rule...
I have this mysql table structure: ------------------------------------ | item_id | meta_key | meta_value | ------------------------------------ 159 category Bungalow 159 location Lagos 159 price 45000 160 category Bungalow 160 location Abuja 160 price 53500 ... 350 category Bungalow 350 location Lagos 350 price 32000 What I'd like to do is select...
One of my sql table is injected with some html code. It is inserted such that the html tags are inserted after actual data. How to remove this from my table.
I have two 2 table t1(years int, numOfppl int), t2(years int, numOfppl int). t1 contains years between 2001 and 2010 , t2 contains years between 2003-2005 and 2007-2010 I want to query a result like that t1.years t1.numOfppl t2.yeras t2.numOfppl 2001 7 null null 2002 6 null null 2003 4...
I have a connected SQL Server database in Visual Studio and am displaying its content in a grid. I created a dropdown menu with the column names as selectable options and a text field to filter for specific content, e.g., DropDown = "Start" - Textfield = 14.03.2015 = Filter Column...
I have to parse very big (abt 40Gb) text files(logs) very often. Usually AWK/grep is enough for my needs, but logs are growing and now I'm curious whether sqlite3 will allow me to do the same things more efficiently. I chosen sqlite for my tests as it installed out-of-the-box (SQLite...
Can someone tell me how this is an exclude? Assuming that tableID is auto generated and in columnY there can be value of 0 or 1. This statement should exclude everything were columnY has a value of 1. SELECT * FROM [table].[dbo].[one] AS t1 LEFT JOIN [table].[dbo].[one] AS t2 ON...
I've the following table in my PHPMYADMIN The desired output is headline impressions clicks Buy A new Iphone ...
I have a table t as follows: CREATE TABLE t( id INTEGER PRIMARY KEY AUTOINCREMENT, date TEXT, a TEXT, b TEXT ); Sample Data 1|2015-06-15|a1 15|b1 15 2|2015-06-15|a2 15|b2 15 3|2015-06-16|a1 16|b1 16 4|2015-06-16|a2 16|b2 16 5|2015-06-17|a1 17|b1 17 6|2015-06-17|a2 17|b2 17 I want to select all values of a...
I'm currently working on a report that shows me all post codes covered by our sales team. Each team covers over 100 post codes. What i would like to do is create a report that brings back the clients within the post code. Currently my code looks like this. SELECT...
from sqlalchemy import create_engine import _mssql from matplotlib import pyplot as plt engine = create_engine('mssql+pymssql://**:****@127.0.0.1:1433/AffectV_Test') connection = engine.connect() result = connection.execute('SELECT Campaign_id, SUM(Count) AS Total_Count FROM Impressions GROUP BY Campaign_id') for row in result: print row connection.close() The above code generates an array: (54ca686d0189607081dbda85', 4174469) (551c21150189601fb08b6b64', 182) (552391ee0189601fb08b6b73', 237304) (5469f3ec0189606b1b25bcc0',...
I couldn't find the answer to my specific question anywhere. Is it possible to select from two different views? For example my code looks something like this right. select view1.col1, view1.col2, view1.col3 from dbo.view1 inner join ~~~~~ inner join ~~~~~ but I want to include a column from a different...
[Aim] We would like to find out how often an event "A" ocurred before time "X". More concretely, given the dataset below we want to find out the count of the prior purchases. [Context] DMBS: MySQL 5.6 We have following dataset: user | date 1 | 2015-06-01 17:00:00 2 |...
My table looks like this: CREATE TABLE MyTable ( TableID INT IDENTITY NOT NULL, ForeignID INT NOT NULL, Value sql_variant NOT NULL, CodeOne VARCHAR(4) NOT NULL, CodeTwo VARCHAR(4) NOT NULL ) I'm trying to do a insert with the following code: INSERT INTO MyTable(ForeignID, Value, CodeOne, CodeTwo) VALUES ( 1,...
If i have a resultset like this for example (just a list of numbers) : 1,2,3,4,5,6,7,8,9,10,11 and I would like to add a grouping column so i can group them per 4 like this : 1,1,1,1,2,2,2,2,3,3,3 (The last one in this examle does not have a forth element, so that...
I want to select the sum of values of my_field. The unit of my_field is in seconds. I don't want to exceed 24 hours, so when it exceeds 24 hours, I want it to select 24*60*60 = 86400. SELECT IF(SUM(my_field) > 86400, 86400, SUM(my_field)) ... This solution doesn't seem to...
I have two tables, one with various details such as Username, email id, user code and another table with columns which are: UserCode, supervisor email id, active_flag I required an output of a table which only displays the active users i.e. the users with active_flag (Y or N) column as...
I want use OFFSET and Fetch in my SQL server 2012 query.But without any order by.I can not use order by.Because my sort order will be lost. How can I use OFFSET and Fetch without order by and row number and where in my query? My 2 select tables have...
I am getting the following error message when I am trying to do replace null to zero. The column name "jan" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument. Query below: select * from(select isnull(jan,0),isnull(feb,0),sum(data) as amount )as p pivot( sum(amount) for month...
I am trying (and failing) to correctly order my recursive CTE. My table consists of a parent-child structure where one task can relate to another on a variety of different levels. For example I could create a task (this is the parent), then create a sub-task from this and then...
I have this situation in a stored Procedure: SET @DATE_RELEASE_START = '2015-01-01'; SET @DATE_RELEASE_END = '2015-05-31' SELECT @statement = ' SELECT * FROM (SELECT AFCDENTE, M.ID_MODIFICATION_CODE, COUNT(*) AS Conteggio--, CAST((COUNT(*) * 100/ 15032) AS decimal(10,7)) AS Percentage FROM CIC_LOG_MODIFICHE AS L INNER JOIN ADM_MODIFICATION_CODE AS M ON L.CD_MODIFICATION_CODE = M.CD_MODIFICATION_CODE...
I've a database value that when inserted into a SQL variable, shows with question mark at the end !! can't find a reason?! declare @A varchar(50) = 'R2300529' select @A Results: R2300529? any explanation? i'm using SQL server 2012....
I want a query to make which return 1 when the time is in between of 10:00 pm - 5:00 pm. Else it should return 0. Select * from table ...
I have values like below I need to take only the thousand value in sql. 38,635.123 90,232.89 123,456.47888 I need to take result as below. 635 232 456...
I have two tables, A and B, in a dataset in SQL Server; I have created a connection to the dataset in a c# project in visual studio. How can I create a foreign key ( A is the parent) between my two tables ? I want to create the...
I have Two tables first us IMG_detail S.NO Title 1 women holding stack of gifts 2 Rear view of a man playing golf 3 Women holding gifts 4 Close-up of a golf ball on a tee 5 Businessman reading a newspaper and smiling and Second is tbl_NoiceWords SN Key 1...
As the title says I cannot browse and cannot see backup folder. is there any other way to restore bak file ? or how can I fix this ? ...
SELECT * FROM FirstTable WHERE RowProcessed = 'N' AND ( CASE WHEN EXISTS(SELECT top 1 FROM SecondTable) THEN 1 ELSE EXISTS( SELECT SecondTable.RowProcessed FROM SecondTable WHERE FirstTable.Key = SecondTable.Key AND SecondTable.RowProcessed = 'Y' ) END ) AND OtherConditions Case When then else in where clause. Not sure about the syntax....
I have the following DB table called messages with columns: thread_id, sender_id, receiver_id, date Example: +-----------+------------+-------------+-------------+ | thread_id | sender_id | receiver_id | date | +----------------------+-----------+-----------------+ | 1 | 1 | 2 | 11/06/2015 | | 1 | 2 | 1 | 14/06/2015 | | 1 | 1 | 2...
I am receiving an error as below: PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE: [Microsoft][ODBC Driver 11 for SQL Server]SQL Server Network Interfaces: Connection string is not valid . ' My codes are as follow: $this->link = new PDO( "sqlsrv:server=$this->serverName:$this->port;Database=$this->db", "$this->uid", "$this->pwd" ); I wish someone can enlighten...
Here is my SQL query which returns number of days by subtracting current date from specified date and returns exact as i need, but in addition i want to add 1 to result if current time passes 14:30 or 2:30. My query SELECT reservations.customerid, DateDiff("d",reservations.checkin_date,Now()) AS Due_nights FROM reservations Am...
This is the query I'm using to count how many events are booked for each category of bookings. SELECT Categories.name, count(case when locations.name ='loc 1' then 1 end) as Location1, count(case when locations.name ='loc 2' then 1 end) as Location2, count(case when locations.name ='loc 3' then 1 end) as Location3,...
I have a query to display the year value from a table and default the current year. select distinct year(startdate) as syear, year(startdate) as eyear, 1 as sOrder from [TTS].[dbo].[Class] where year(startdate) <> year(getdate()) union all select year(getdate()) as syear, year(getdate()) as eyear, 0 as sOrder order by sOrder asc,...
When I use mysql_real_escape_string in my localhost and I output the result of html in a table I have no problem. But when I use it on my server it outputs even the \ This is how I use it: $_GETVARS['txtEmpNum'] = mysql_real_escape_string($_GETVARS['txtEmpNum']); $_GETVARS['txtLName'] = mysql_real_escape_string($_GETVARS['txtLName']); $_GETVARS['txtFName'] = mysql_real_escape_string($_GETVARS['txtFName']); $varSQL...