DB Engine :: How To Solve Multiple Rows Result From Inner Join Query
Dec 3, 2015
I have 3 tables:
TABLE [dbo].[Tbl_Products](
[Product_ID] [int] IDENTITY(1,1) NOT NULL,
[Product_Name] [nvarchar](50) NOT NULL,
[Catagory_ID] [int] NOT NULL,
[Entry_Date] [date] NOT NULL,
[Code] ....
I am using this query to get ( Product name from tbl_products , Buy Price - Total Price- Total Quantity from Tbl_Details )
But am getting a multiple result if the order purchase has more than 1 item :
SELECT DISTINCT B.Product_Name,A.AllPieceBoxes,
A.BuyPrice,A.TotalPrice,A.BuyPrice
FROM
Tbl_Products B INNER JOIN Tbl_PurchaseHeader C
ON C.ProductId=B.Product_ID INNER JOIN Tbl_PurchaseDetails A
ON A.PurchaseOrder=C.purchaseOrder
WHERE A.PurchaseOrder=3
I have two tables .. in one (containing user data, lets call it u).The important fields are:u.userName, u.userID (uniqueidentifier) and u.workgroupID (uniqueidentifier)The second table (w) has fieldsw.delegateID (uniqueidentifier), w.workgroupID (uniqueidentifier) The SP takes the delegateID and I want to gather all the people from table u where any of the workgroupID's for that delegate match in w. one delegateID may be tied to multiple workgroupID's. I know I can create a temporary table (@wgs) and do a: INSERT INTO @wgs SELECT workgroupID from w WHERE delegateID = @delegateIDthat creates a result set with all the workgroupID's .. this may be one, none or multipleI then want to get all u.userName, u.userID FROM u WHERE u.workgroupIDThis query works on an individual workgroupID (using another temp table, @users to aggregate the results was my thought, so that's included) INSERT INTO @users SELECT u.userName,u.userID FROM tableU u LEFT JOIN tableW w ON w.workgroupID = u.workgroupID WHERE u.workgroupID = @workGroupIDI'm trying to avoid looping or using a CURSOR for the performance hit (had to kick the development server after one of the cursor attempts yesterday)Essentially what I'm after is: SELECT u.userName,u.userID FROM tableU u LEFT JOIN tableW w ON w.workgroupID = u.workgroupID WHERE u.workgroupID = (SELECT workgroupID from w WHERE delegateID = @delegateID) ... but that syntax does not work and I haven't found another work around yet.TIA!
I worked with someone else to create a query that gives us the age of a stock. How long it has been in the warehouse since the Purchase order date (without completely selling out). It does exactly what I want, the problem is that it only accepts 1 row as a result.
The error message I get is:
quote:Msg 512, Level 16, State 1, Line 4
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
So my question is; can this code be modified to pass it multiple SKUS and run a report on every item currently in stock?
SELECT TOP 1 @skuVar, CAST(GETDATE()-ReceivedOn AS INT) AS 'Age' FROM (SELECT pir.id AS id,aggregateQty AS aggregateQty,-qtyreceived as qtyreceived, (aggregateQty - qtyreceived) AS Diff, ReceivedOn AS ReceivedOn ,( SELECT SUM (PurchaseItemReceive.qtyreceived) FROM bvc_product pp
I know I can do a JOIN(parameter, "some seperator") and it will build me a list/string of all the values in the multiselect parameter.
However, I want to do the same thing with all the occurances of a field in my result set (each row being an occurance).
For example say I have a form that is being printed which will pull in all the medications a patient is currently listed as having perscriptions for. I want to return all those values (say 8) and display them on a single line (or wrap onto additional lines as needed).
Something like: List of current perscriptions: Allegra, Allegra-D, Clariton, Nasalcort, Sudafed, Zantac
How can I accomplish this?
I was playing with the list box, but that only lets me repeat on a new line, I couldn't find any way to get it to repeate side by side (repeat left to right instead of top to bottom). I played with the orientation options, but that really just lets me adjust how multiple columns are displayed as best I can tell.
Could a custom function of some sort be written to take all the values and spit them out one by one into a comma seperated string?
I am using SLQ Server 2008 R2. The database was designed by another company.
I have two tables: Client and Client_Location. In the Client table the pk is Client_ID. There is also a unique key: sys_Client_ID. Both the Client_ID and the sys_Client_ID fields exist as a foreign keys in the Client_Location table. However, the fields are not noted as unique in the Client_Location table. There are two fields in the Client_Location table that determine when the address was effective. They are from_dt and end_dt.
Multiple records have been loaded into the Client_Location table to track old as well as current addresses of clients.
I'm trying to run a report that will pull clients with a plan_id constraint from the Client table and join the Client_Location table to retrieve the current address of these clients.
My SQL is:
select distinct (a.client_id), a.cli_last AS Last_Name, a.cli_first AS First_Name, a.cli_middle AS Mid_Init, b.city AS City, b.county AS County, b.state AS State from ECBH.dbo.tbl_Client a inner join ECBH.dbo.tbl_Client_Location b on a.client_id = b.client_id inner join ECBH.dbo.tbl_client_insurance c on a.client_id = c.client_id inner join ECBH_TEST.dbo.tbl_GEF_County d on b.county = d.COUNTY_NAME where c.plan_id = 4 order by a.cli_last, a.cli_first
Because multiple records exist in the Client_Location table, the result set has duplicates. How can I pull only the results where the from_dt is most recent?
I have a criteria where i want to join table 1 with table 2 , table 1 consists of products which were given to salesman to sell and table 2 has the sales data which salesman has sold out. Now i want to know left over products of each sales with join .Below is my data, here is what i am trying to do, but it return only salesman 1 data.
CREATE TABLE Salesman_Product ( SalesManID int, ProductID int ) INSERT INTO Salesman_Product (SalesManID,ProductID) Values (1,1),(1,2),(1,3),(1,4) INSERT INTO Salesman_Product (SalesManID,ProductID) Values (2,1),(2,2),(2,3),(2,4)
I've a requirement where I need to merge multiple rows in single rows. For example in the attached image output, I need to return a single column for type Case like this.
CH0, CH1, CH2, CHX Case CM0, CM1, CM2, CMX Mechanical
I'm using T-SQL to generate the column type. Below is my DDL.
USE tempdb GO CREATE TABLE ProdCodes (Prefix char(8), Code char(5)
SELECT R.name, R.age,R.DOB, ISNULL(D.Doc1,'NA') AS doc1, ISNULL(C.Doc2,'NA') AS doc2 FROM REQ R inner join RES S ON R.Request_Id=S.Request_Id inner join RES1 D ON D.Response_Id=S.Response_Id inner join REQ1 C ON C.Request_Id=R.Request_Id
select * from RES1 where Response_Id = 111 -- return 3 select * from REQ1 where Request_Id = 222 --- returns 2
So at last inner join retuns 3*2 = 6 records , which is wrong here and i want to show 3 records in doc1 row and 2 records in doc 2 rows ...
I have to join two tables and i need to fetch All records from @tab2 and only max date record from @tab1 that ID is present in Tab2
1.) @Tab1 have multiple records for each ID
2.) @Tab2 also have multiple records for each ID
3.) Kind of Lef Outer join those tables with ID and take all records from @tab2 and only Max of date from @tab1 and order by ID and Date
Note: @Tab1 always have lesser dates than @tab2 for each ID
Tables looks like as follows
declare @tab1 table (id varchar(3), effDt Date, rate int) insert into @tab1 values ('101','2013-12-01',5) insert into @tab1 values ('101','2013-12-02',2) insert into @tab1 values ('101','2013-12-03',52)
[code]....
In the given ex, ID 103 should not come as it is not present in @tab2, ID 104 should come even it is not present in @tab1 as we ahve to use left outer join Result should like follows.
I have information on clothes in a table that I want to select out to a result set in a different structure - I suspect that this will include some kind of pivot (or cross-join?) but as I've never done this before I'd appreciate any kind of help possible.
Current structure is:
Colour Size Quantity ----------------------- Red 10 100 Red 12 200 Red 14 300 Blue 10 400 Blue 12 500 Blue 14 600 Green 10 700 Green 12 800 Green 14 900 Green 16 1000
I want to produce this result set:
Colour Size10 Size12 Size14 Size16 ------------------------------------- Red 100 200 300 0 Blue 400 500 600 0 Green 700 800 900 1000
There could be any number of sizes or colours.
Is this possible? Can anyone give me any pointers?
I have a subscriptions table that has many line items for each record. Each line item has a different type, dues, vol, Chapt.
101 dues Mem 100 101 Vol charity 200 101 chapt CHi 300
I want my end result to have one line item per record id, but I keep coming up with an error. I am pretty sure I am close, but need assistance before I can proceed.
101 mem 100 charity 200 chi 300
Error: Server: Msg 207, Level 16, State 3, Line 2 Invalid column name 'PRODUCT_CODE'. Server: Msg 207, Level 16, State 1, Line 2 Invalid column name 'product_code'. Server: Msg 207, Level 16, State 1, Line 2 Invalid column name 'product_code'.
SELECTp.ID, p.PRODUCT_CODE as Chapt, p.product_code as Dues, p.product_code as Vol from ( SELECT ID, product_code as Chapt, Null as dues, Null as Vol from subscriptions where prod_type = 'chapt' AND BALANCE > 0
union all
SELECT ID, Null as chapt, product_code as Dues, Null as vol from subscriptions where prod_type = 'dues' AND BALANCE > 0
union all
SELECT ID, Null as chapt, Null as dues, product_code as Vol from subscriptions where prod_type = 'vol' AND BALANCE > 0
Hi,I'm am looking for a little help. I need to create a SQL view whichjoins a few tables, and I need to return an average for a particularcolumn where a second duplicate ID exists...Heres an example of how the results could be returned...ID | Name | Order No. | Value---+------+-----------+---------5 | test | 1234 | 35 | test2| 1234 | 45 | test3| 1234 | 35 | void | 1235 | 55 | void2| 1235 | 65 | void3| 1235 | 55 | void4| 1235 | 7ID is my main join which joins the tablesName is a unique nameOrder No is the same for the different names, I only need to return onerow with this order no, and the first name (the rest are irrelevant)Value is the field which I wish to return as an average of all 3, 4 orhowever many rows is returned and share the same order no. This iswhere I get totally lost as I am pretty new to SQL. Can anyone provideany help on how I would go about limiting this query to the uniqueorder no's and returning the average of the value field, and I can takeit from there with my own tables.Thanks for your helpstr8
My site have a complicated search, the search give the results in two stages- the first one giving the number of results in each section:
"In the forums there is X results for the word X In the articles there is X results...."
And when the user click one of those lines, the list shows the specific results in that section.
My problem is that I don't know how to calculate the first part, for now I use dataset, and table.rows.count to show the number of results in each section. Since my site have more then ten, it looks like a great waste to fill such large dataset (in some words it can be thousands of rows in each section) only for getting the number of rows…
Are there is a sql procedure or key word that will give me only the number of results (the number of times that specific word showing in the columns?)
Working in Sql Server 2005,got 3 different table.Need to fetch the list of persons.A person can belong to different categories.When i am using inner join on tables i am getting the duplicate rows because a perosn can belong to different categories.I want that there should be only onoe row for the person and the different categories he belongs to can come up in single field as comma sepatated string Now the results are like this: firstname lastname adress category abc xyz aaa a abc xyz aaa b abc xyz aaa c I want like below: abc xyz aaa a,b,c I am thinking of using cursors in the stored procedure.Can you provide me the solution of this including the stored procedure..
Current ResultSet ------------------ ColA ColB TEST1 111 TEST1 222 TEST2 333 TEST3 444 TEST3 555
I am currently outer-joiing two tables to retrive some data in the above format. My intent is to modify the query so I can retrive the data in below fashion.
Hi, Please help me with an SQL Query that fetches all the records from the three tables but a unique record for each forum and topicid with the maximum lastpostdate. I have to bind the result to a GridView.Please provide separate solutions for SqlServer2000/2005. I have three tables namely – Forums,Topics and Threads in SQL Server2000 (scripts for table creation and insertion of test data given at the end). Now, I have formulated a query as below :- SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads FROM Forums f FULL JOIN Topics t ON f.forumid=t.forumid FULL JOIN Threads th ON t.topicid=th.topicid GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate ORDER BY t.topicid ASC,th.lastpostdate DESC Whose result set is as below:-
forumid topicid name author lastpostdate NoOfThreads
5 17 General NULL NULL 0 On modifying the query to:- SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads FROM Forums f FULL JOIN Topics t ON f.forumid=t.forumid FULL JOIN Threads th ON t.topicid=th.topicid GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate HAVING th.lastpostdate=(select max(lastpostdate)from threads where topicid=t.topicid) ORDER BY t.topicid ASC,th.lastpostdate DESC I get the result set as below:-
forumid topicid name author lastpostdate NoOfThreads
5 17 General NULL NULL 0 I want all the rows from the Forums,Topics and Threads table and the row with the maximum date (the last post date of the thread) as shown above. The scripts for creating the tables and inserting test data is as follows in an already created database:- if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Topics__forumid__79A81403]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Topics] DROP CONSTRAINT FK__Topics__forumid__79A81403 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Threads__topicid__7C8480AE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Threads] DROP CONSTRAINT FK__Threads__topicid__7C8480AE GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Forums]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Forums] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Threads]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Threads] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Topics]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Topics] GO CREATE TABLE [dbo].[Forums] ( [forumid] [int] IDENTITY (1, 1) NOT NULL , [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Threads] ( [threadid] [int] IDENTITY (1, 1) NOT NULL , [topicid] [int] NOT NULL , [subject] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [replies] [int] NOT NULL , [author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [lastpostdate] [datetime] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Topics] ( [topicid] [int] IDENTITY (1, 1) NOT NULL , [forumid] [int] NULL , [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[Forums] ADD PRIMARY KEY CLUSTERED ( [forumid] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Threads] ADD PRIMARY KEY CLUSTERED ( [threadid] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Topics] ADD PRIMARY KEY CLUSTERED ( [topicid] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Threads] ADD FOREIGN KEY ( [topicid] ) REFERENCES [dbo].[Topics] ( [topicid] ) GO ALTER TABLE [dbo].[Topics] ADD FOREIGN KEY ( [forumid] ) REFERENCES [dbo].[Forums] ( [forumid] ) GO ------------------------------------------------------ insert into forums(name,description) values('Developers','Developers Forum'); insert into forums(name,description) values('Database','Database Forum'); insert into forums(name,description) values('Desginers','Designers Forum'); insert into forums(name,description) values('Architects','Architects Forum'); insert into forums(name,description) values('General','General Forum'); insert into topics(forumid,name,description) values(1,'Java Overall','Topic Java Overall'); insert into topics(forumid,name,description) values(1,'JSP','Topic JSP'); insert into topics(forumid,name,description) values(1,'EJB','Topic Enterprise Java Beans'); insert into topics(forumid,name,description) values(1,'Swings','Topic Swings'); insert into topics(forumid,name,description) values(1,'AWT','Topic AWT'); insert into topics(forumid,name,description) values(1,'Web Services','Topic Web Services'); insert into topics(forumid,name,description) values(1,'JMS','Topic JMS'); insert into topics(forumid,name,description) values(1,'XML,HTML','XML/HTML'); insert into topics(forumid,name,description) values(1,'Javascript','Javascript'); insert into topics(forumid,name,description) values(2,'Oracle','Topic Oracle'); insert into topics(forumid,name,description) values(2,'Sql Server','Sql Server'); insert into topics(forumid,name,description) values(2,'MySQL','Topic MySQL'); insert into topics(forumid,name,description) values(3,'CSS','Topic CSS'); insert into topics(forumid,name,description) values(3,'FLASH/DHTLML','Topic FLASH/DHTLML'); insert into topics(forumid,name,description) values(4,'Best Practices','Best Practices'); insert into topics(forumid,name,description) values(4,'Longue','Longue'); insert into topics(forumid,name,description) values(5,'General','General Discussion'); insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'About Java Tutorial',2,'a@b.com','1/27/2008 02:44:29 PM'); insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'Java Basics',0,'x@y.com','1/27/2008 02:48:53 PM'); insert into threads(topicid,subject,replies,author,lastpostdate) values (4,'Swings',0,'p@q.com','1/27/2008 03:12:51 PM');
Please refer to the below query. I want to filter inner join rows based on outer query column value (refer to bold text).
SELECT M.MouldId, SI.StockCode, MI.Cavity, MI.ShotCounter, CQ.SumOfCastedQty as CastedQty, MI.CounterStartup FROM MouldItem MI JOIN (SELECT JD.MouldId, JC.StockCode, SUM(JS.CastedQty) AS SumOfCastedQty FROM JobCasting AS JS INNER JOIN JobCreationDet AS JD ON JS.JobDetId = JD.Uniid INNER JOIN JobCreation AS JC ON JD.JobIdx = JC.Uniid
I have 4 tables (SqlServer2000/2005). In the select query, I have FULL JOINED all the four tables A,B,C,D as I want all the data. The result is as sorted by DDATE desc:- AID BID BNAME DDATE DAUTHOR 1 1 abcxyz 2008-01-20 23:42:21.610 c@d.com 1 1 abcxyz 2008-01-20 23:41:52.970 a@b.com 1 2 xyzabc 2008-01-21 00:17:14.360 c@d.com 1 2 xyzabc 2008-01-20 23:43:17.110 a@b.com 1 2 xyzabc 2008-01-20 23:42:43.937 a@b.com 1 2 xyzabc NULL NULL 2 3 pqrlmn NULL NULL 2 4 cdefgh NULL NULL Now, I want unique rows from the above result set like :- AID BID BNAME DDATE DAUTHOR 1 1 abcxyz 2008-01-20 23:42:21.610 c@d.com 1 2 xyzabc 2008-01-21 00:17:14.360 c@d.com 2 3 pqrlmn NULL NULL 2 4 cdefgh NULL NULL I want to remove the duplicate rows and show only the unique rows but contains all the data from the first table A. I have to bind this result set to a nested GridView.
What is the best way to use a left join in a SQL statement with multiple tables (more than 2)? I have a query that uses 7 tables, where most of the joins are inner joins, except for one, which needs to be a left join. The current SQL statement looks something like this:
FROM [table1],[table2],[table3],[table4],[table5],[table6],[table7] WHERE [table4].[field2]=[table1.field2]{this is an inner join} [table4].[field2]=[table2.field2]{this is an inner join} [table4].[field2]=[table3.field2]{this is an inner join} [table4].[field2]=[table5.field2]{this is an inner join} [table5].[field3]=[table6.field2]{this is an inner join} [table5].[field4]=[table7.field2]{this is needs to be a left join}
As it stands now, the last line in the WHERE clause is an INNER JOIN and limits the number of rows in my result. I need to select rows from [table7].[field2] whether or not a matching record exists in [table5].[field4]. The other INNER JOINS in the SQL statement must have matching records. Please advise.
I have a sql query that I am using to populate a datagrid. The problem is one of the tables is a month table. and the other tables are full of data. So there is no common column name to match using a inner join "on".
i have Two tables... with both the table having LastUpdated Column. And, in my Select Query i m using both the table with inner join.And i want to show the LastUpdated column which has the maxiumum date value. i.e. ( latest Updated Column value).
in my table i ve the column of item code which contains '1000' ,'2000' ,'3000' series i jus wanna display the output of item codes '1000','2000'series and some of ('3000019','3000020','3000077','3000078').
i tried in my join query
these code left(itemcode,4) in ('1000','2000') or itemcode in ('3000019','3000020','3000077','3000078')
I'm trying to run a sql query using the core dotnetnuke database. I am tying to pull from two separate tables in the same database and return the results below: ------------------------------------
LastName | FirstName | City | PhoneNumber
Smith Joe New York (555)555-5555
------------------------------------
Last Name and FirstName are two separate columns in the DB_Users table, while City and PhoneNumber are both values of the same column DB_UserProfile.PropertyValue located in separate rows defined by an ID in the DB_UserProfile.PropertyDefinitionID column.
The UserProfile table looks something like this...
ID | PropertyDefinitionID | PropertyValue
1 27 New York 2 31 (555)555-5555
------------------------------------
This is what I have so far but it is not working out to well...
SELECT DB_Users.FirstName, DB_Users.LastName, DB_UserProfile.PropertyValue AS City, DB_UserProfile.PropertyValue AS PhoneNumber FROM DB_Users, DB_UserProfile WHERE DB_Users.UserID = DB_UserProfile.UserID AND DB_UserProfile.PropertyDefinitionID = 27 AND DB_UserProfile.PropertyDefinitionID = 31 ORDER BY DB_Users.LastName
Can someone please help with a query I have? Basically I want to return all rows in a table that have multiple date entries that are different. For example:
This security 1636 has two entries in the DB with different dates. They are lots of securities with multiple entries with the same date but I need a list of the ones with different dates. Any ideas please?
I am not sure if I wrote the question's title appropriatly or not.....but anyways I wud try to explain the problem.
I am facing a problem solving the following task in MS Access (I believe it can surely be solved in access or SQL server).I am posting a sample table format here for reference and also postin the expected result table there-after.
The problem goes like this.(Read Carefully)
I have a table which contains a payment history with one row per account (each account number is unique) and related payment history for each account.
where Dt is date and P1/2/3/... is payment.There cud be 1 or more than 1 payments in a particular month as shown.
The original file has more than 200 columns for the payment i.e till Dt200,P200
I need to see aggregated monthly payment history for all accounts. i.e. a table for all accounts with related payments for each month (its OK if a day vary in particular month...consolidation shud be monthly) starting from the earliest to the latest possible.
I hope I have tried to explain the problem in as much detail as possible.
Please help me with your valuable solutions to the above task ASAP.If u want i can also send in as attachment the original file i am workin at to ur email id
I am trying to do the following: Insert n rows into A Table called EAItems. For each row that is inserted into EAItems I need to take that ItemID(PK) and insert a row into EAPackageItems. I'm inserting rows from a Table called EATemplateItems. So far I have something like this: (I have the PackageID already at the start of the query). INSERT INTO EAItems(Description, Recommendation, HeadingID)SELECT Description, Recommendation, HeadingIDFROM EATemplateItems WHERE EATemplateItems.TemplateID = @TemplateID INSERT INTO EAPackageItems(ItemID, PackageID) ....
I have no idea how to grab each ITemID as it's created, and then put it into the EAPackageItems right away.
