I am trying to merge the selected contents of 2 tables by using a date field in both tables.
For example, in the AdventureWorks database of VS2005, I would like 3 columns: loginID, modifieddate and addressline1. Where the row has come from the Employee table, the address field should be blank, so its not really a join that I want (at least I dont think so).
This query only returns 2 colums: select loginid, modifieddate from HumanResources.Employee union all select addressLine1, modifiedDate from Person.Address
Is this possible, or do I need an additional common column that can be inner joined on the 2 tables? Can anyone help?
Ok, getting a little better at joins, but now need to take it two more steps.Main table tblEquipmentsFields: ID, EquipmentType, Location, StatusSecond Table tblEquipments_GroupFields: equipmentID, categoryID, subCategoryID (all are ints)I joined these two together and got the info back I needed:SELECT a.ID, a.Description, a.Status, a.Location, from tblEquipments ainner join tblEquipments_Group U on u.categoryID = a.EquipmentTypeand u.subCategoryID = @type Now, the a.status returns a number. What the status actually is is in tblStatus.Fields: statusID (int) , Status (varchar).So, the statusID matches a.Status.I need to get the text from tblStatus in the field Status and replace the a.Status (which is a number still) with the text.The other one, location, is the same situation.I've been trying to do another join and am at an end on this.Suggestions?Thanks!!Zath
Hi, I have three tables named as BroadCastetails,PaymetMaster,MemberMaster. I have two query as mentioned below-- SELECT MemberMaster.FirstName, MemberMaster.LastName, BroadCastDetails.BroadCastName FROM BroadCastDetails INNER JOIN MemberMaster ON 5 = MemberMaster.MemberID AND BroadcastCreationDateTime between '01/01/2007' AND '12/12/2007' SELECT BroadCastDetails.ScheduledStartDateTime, BroadCastDetails.TotalCalls FROM BroadCastDetails UNION SELECT PaymentTransaction.TransactionDate, PaymentTransaction.CallsToCredit FROM PaymentTransaction
I want the result of the above two query in a GridView.How can I do that ? Its urgent...
How can I write a query that will return the following result to show the order details for customerid 1000 for a date range between 01/01/2003 and 01/08/2003
OrderDate Quantity FreeShipping ========= ======== ============ 01/01/2003 5 Y 01/03/2003 0 Y 01/04/2003 9 N 01/08/2003 14 N
Note that even if an order was not placed and the date was a freeshipping date, it is still displayed in the resultset
I am trying to normalize data using the unpivot transform. I have to unpivot using more than one key so I have a multicast feeding into two unpivot transforms then into a sort transform. This is where my problem starts - I have tried using a Merge Join (inner Join) transform but dont get the expected result.
My original data looks like this:
Pk_ID
Choice1
Choice2
Feedback1
Feedback2
10
a
b
x
y
After the mulitcast - unpivot - Merge Join, the expected result is: (pk_newID is an identity)
Pk_newID
fk_ID
Choice
Feedback
563
10
a
x
564
10
b
y
However with a Merge-Join (inner join on pk_ID) I get
Not the server software obviously, but storing the files on their RAID solution? it doesn't seem that bad of a bang for your buck and I read good things about it.
Simple Explanation: I have a table with numbers 1-5, I want to left join a table with numbers 1-10, and show a sum... So since numbers 6-10 will not match anything, I will have a sum of zero for those rows. Simple, I thought!
SELECT SUM(ISNULL(RI.quantity,0)) AS Quantity, usr.conversionRate, usr.CorpType FROM ( SELECT 0 AS conversionRate, 1 AS CorpType UNION ALL SELECT .05, 1 UNION ALL SELECT .5, 1 UNION ALL SELECT 1, 1 UNION ALL SELECT 1.5, 1 UNION ALL SELECT 1.75, 1 ID UNION ALL SELECT 2, 1 UNION ALL SELECT 0, 2 UNION ALL SELECT .05, 2 UNION ALL SELECT .5, 2 UNION ALL SELECT 1, 2 UNION ALL SELECT 1.5, 2 UNION ALL SELECT 1.75, 2 ID UNION ALL SELECT 2, 2 ) usr LEFT OUTER JOIN Items I ON I.ConversionRate = usr.conversionRate LEFT OUTER JOIN Requests_Items RI ON I.ID = RI.itemID AND ISNULL(RI.impactMonth,RI.dueDate) >= '02/01/2008' and ISNULL(RI.impactMonth,RI.dueDate) < '03/01/2008' LEFT OUTER JOIN Corporations_Requests CR ON CR.requestID = RI.requestID LEFT OUTER JOIN Corporations C ON C.CorpID = CR.corporationID AND C.CorpType = usr.CorpType GROUP BY usr.CorpType, usr.conversionRate ORDER BY usr.CorpType, usr.conversionRate
I have no clue why this doesn't work. It appears that the UNION ALL(s) are being crossed joined. Any light shed on this would be greatly appreciated!
Hallo !I have 2 tables with similar construction.DataOLD and DataNEW.In my grid I need a JOINED table.On the one hand I need all the data from the old table and the datafrom then new table with the same id (left outer join works)But additionally I need all rows from the new table where no equivalentrows are in the old table.example:TableOLDdiameter amountOLD20 10050 20030 300TableNEWdiameter amountNEW20 50060 60070 120Wished Result:diameter amountOLD amoutNEW20 100 50050 200 NULL30 300 NULL60 NULL 60070 NULL 120Who can help me?Many Thanks.aaapaul
I have 3 tables I want to use in a view. Table A has field 1,2,3,4,5and table B has field 1,2,3,4,5. I want to do a union on these. (I havedone so successfully if I stop here) I also want to join table C whichhas field 1,6,7,8,9. I would like to join on field 1 and bring in theother fields. I can join table C to A or B. I can union table A and Bbut I do not know how to both union A and B then join C. Can someoneplease help me? Thanks in advance.
Can i do a Full outer join on a union? and if so how?
I have this stored procedure. but then i was notified that they want to show all the sales reps, even if they dont have visits, and all the activities, even if neither has values. Right now this is what it shows:
Code Block ALTER PROCEDURE [dbo].[PROC_DAILY_ACTIVITY] (@Region_Key int=null) AS BEGIN
SELECT NULL as Progress_time, NULL As completed_time, NULL as Dial_in_date, NULL AS Customer_Code, Non_Customer_Activities.question_code, Non_Customer_Activities.description, Sales_Group.Region, Sales_Group.Name AS Territory_Name, Non_Customer_Activities.Que_Desc AS Store_Name, Non_Customer_Activities.Logged_Time AS TheDate, CONVERT(FLOAT,Non_Customer_Activities.Logged_Time) Float_Date, Non_Customer_Activities.response AS Response, NULL AS is_Visit_Fg FROM Qry_Sales_Group AS Sales_Group INNER JOIN (SELECT QH.question_code, Question_Header.description, CONVERT(datetime, DATEADD(day, QH.cycle_day - 1, P.start_date), 6) AS Logged_Time, SUBSTRING(QH.entity_code, 1, 5) AS SR_Code, QH.response, Territory_In_Sales_Responsible.Territory_Code AS SR_Territory_Code, 'Not Customer Related' AS Que_Desc FROM question_history AS QH INNER JOIN period AS P ON P.period_code = QH.period_code INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Sales_Responsible AS Territory_In_Sales_Responsible ON Territory_In_Sales_Responsible.SalesPerson_Purchaser_Code = SUBSTRING(QH.entity_code, 1, 5) COLLATE Latin1_General_CI_AS INNER JOIN questions AS Question_Header ON Question_Header.question_code = QH.question_code WHERE (QH.entity_code LIKE '%.USER%') AND (Question_Header.question_code IN ('AME01', 'ASE01', 'ACO01', 'ALU01', 'AOS01', 'APH01', 'ATR01', 'ATE01', 'ACR06', 'ACR05', 'ACR02', 'ACR03', 'ACR08', 'ACR07')) AND (CONVERT(datetime, DATEADD(day, QH.cycle_day - 1, P.start_date), 6) = case when Datepart(Weekday, Getdate()) = 2 then datediff(dd,0,GetDate()) - 3 else datediff(dd,0,GetDate()) - 1 end )) AS Non_Customer_Activities ON Sales_Group.Code = Non_Customer_Activities.SR_Territory_Code WHERE Region_Key=@Region_Key UNION ALL SELECT in_progress_time, completed_time , dial_in_date, Customer_Activities.Customer_Code, NULL AS Expr1, NULL AS Expr2, Sales_Group.Region, Sales_Group.Name AS Territory_Name, Customer_Activities.Customer_Name AS Store_Name, Customer_Activities.Logged_Time AS TheDate, CONVERT(FLOAT,Customer_Activities.Logged_Time) Float_Date, NULL AS Response, Customer_Activities.Is_Visit_Fg FROM Qry_Sales_Group AS Sales_Group INNER JOIN (SELECT DISTINCT in_progress_time,completed_time,dial_in_date, V.visit_date AS Logged_Time, Customer_Code, customer_name AS Customer_Name, Territory_In_Sales_Responsible.Territory_Code AS Cust_Territory_Code, 1 AS Is_Visit_Fg FROM Qry_visits AS V INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Sales_Responsible AS Territory_In_Sales_Responsible ON Territory_In_Sales_Responsible.SalesPerson_Purchaser_Code = V.sales_person_code COLLATE Latin1_General_CI_AS INNER JOIN FSSRC.dbo.Communication_statistics as Communication ON Communication.sales_person_code=Territory_In_Sales_Responsible.SalesPerson_Purchaser_Code COLLATE Latin1_General_CI_AS WHERE (CONVERT(datetime, CONVERT(Varchar, V.visit_date, 110)) = case when Datepart(Weekday, Getdate()) = 2 then datediff(dd,0,GetDate()) - 3 else datediff(dd,0,GetDate()) - 1 end )
) AS Customer_Activities ON Sales_Group.Code = Customer_Activities.Cust_Territory_Code WHERE Region_Key=@Region_Key end
SELECT plan2008.jahr, plan2008.monat, plan2008.kdkrbez, plan2008.kdgrbez, plan2008.abgrbez, plan2008.artnr, FROM plan2008 GROUP BY plan2008.jahr, plan2008.monat, plan2008.kdkrbez, plan2008.kdgrbez, plan2008.abgrbez, plan2008.artnr
UNION
SELECT fsp_auftrag.jahr, fsp_auftrag.monatnr, fsp_auftrag.kundenkreis, fsp_auftrag.kundengruppe, fsp_auftrag.abnehmergruppe, fsp_auftrag.artnr FROM fsp_auftrag GROUP BY fsp_auftrag.jahr, fsp_auftrag.monatnr, fsp_auftrag.kundenkreis, fsp_auftrag.kundengruppe, fsp_auftrag.abnehmergruppe, fsp_auftrag.artnr
My problem is that each table contains additional values like art_amount, art_turnover etc... whereby the first table contains plan values while the second table contains actual values.
My goal is to get plan as well as the actual values in one row, how is that possible? If I put the values into each of the selects I get two rows, which is not the wished output.
Is it possible to join the tables after the union took place?
I have a query which wants to union join the data. no matter how many times I tried, I got an error. How to change my union query?
select distinct b.lev5 AS "LEVEL 1",b.lev5NAME, C.lev7 "FUND", C.lev7NAME,round (sum(a.data),2) AS AMOUNT (Select distinct b.lev5 from bf_data a inner join bf_orgn_cnsl_tbl b on a.bf_orgn_cd = b.bf_orgn_cd
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
Struggling to come up with an overall sum for each individual user when using the below code
SELECT Practice.ibvStaffCategorisation.StaffId ,Practice.ibvStaffTotalsCL2Y.Period ,SUM(Practice.ibvStaffTotalsCL2Y.ChargeableMinutes) AS Sum_ChargeableMinutes ,SUM(Practice.ibvStaffTotalsCL2Y.NonChargeableMinutes) AS Sum_NonChargeableMinutes
[Code] ....
As I am using union all it shows two values for each user, the problem is I am getting the data via lookup like below
I don't think I can use a Sum within a lookup so at the moment it is only bringing through the first value for each user but I would like it to bring through both.
Is there a way to do this within the dataset or via the query in the table?
We all were new at one point.... any help is appreciated.
Objective:
Combining two 49,000 row tables and remove records where there is only 1 column difference. (keeping the specified column value removing the one with a blank.)
Reason:
I have 2 people going through a list, coding a specific column with a single letter value. They both have different progress on each sheet. Hence I am trying to UNION them and have a result of their combined efforts without duplicates.
My progress/where I'm stuck:
Here is my first query/union:
SELECT * FROM [Eds table] UNION SELECT * FROM [Vickis table];
As shown above, I have unioned these 2 tables and my results removed th obvious whole record duplicates, but since 1 column is different on these, a union without criteria considers them unique.....
an example of duplicates that I must remove are as follows:
I am writing a stored procedure that takes in a customer number, a current (most recent) sales order quote, a prior (to most current) sales order quote, a current item 1, and a prior item 1, all of these parameters are required.Then I have current item 2, prior item 2, current item 3, prior item 3, which are optional.
I added an IF to check for the value of current item 2, prior item 2, current item 3, prior item 3, if there are values, then variable tables are created and filled with data, then are retrieved. As it is, my stored procedure returns 3 sets of data when current item 1, prior item 1, current item 2, prior item 2, current item 3, prior item 3 are passed to it, and only one if 2, and 3 are omitted.I would like to learn how can I return this as a one data set, either using a full outer join, or a union all?I am including a copy of my stored procedure as it is.
I have a query which does 3 selects and Union ALLs each to get a final result set. The performance is unacceptable - takes around a minute to run. If I remove the Union All so that the result sets are returned individually it returns all 3 from the query in around 6 seconds (acceptable performance).
Any way to join the result sets together without using Union All.
Each result set has exactly the same structure returned...
Query below [for reference]...
WITH cte AS ( SELECT A.[PoleID], ISNULL(B.[IsSpanClear], 0) AS [IsSpanClear], B.[SurveyDate], ROW_NUMBER() OVER (PARTITION BY A.[PoleID] ORDER BY B.[SurveyDate] DESC) rownum FROM[UT_Pole] A LEFT OUTER JOIN [UT_Surveyed_Pole] B ON A.[PoleID] = B.[PoleID]
I have a Union All transformation with 4 inputs and one output when I debug the package the sum of the different inputs rows does not match the row count in output.
I don't understand, I've used the Union All transform many times and I've never seen this.
I was writing a query using both left outer join and inner join. And the query was ....
SELECT S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname FROM Production.Suppliers AS S LEFT OUTER JOIN (Production.Products AS P INNER JOIN Production.Categories AS C
[code]....
However ,the result that i got was correct.But when i did the same query using the left outer join in both the cases
i.e..
SELECT S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname FROM Production.Suppliers AS S LEFT OUTER JOIN (Production.Products AS P LEFT OUTER JOIN Production.Categories AS C ON C.categoryid = P.categoryid) ON S.supplierid = P.supplierid WHERE S.country = N'Japan';
The result i got was same,i.e
supplier country productid productname unitprice categorynameSupplier QOVFD Japan 9 Product AOZBW 97.00 Meat/PoultrySupplier QOVFD Japan 10 Product YHXGE 31.00 SeafoodSupplier QOVFD Japan 74 Product BKAZJ 10.00 ProduceSupplier QWUSF Japan 13 Product POXFU 6.00 SeafoodSupplier QWUSF Japan 14 Product PWCJB 23.25 ProduceSupplier QWUSF Japan 15 Product KSZOI 15.50 CondimentsSupplier XYZ Japan NULL NULL NULL NULLSupplier XYZ Japan NULL NULL NULL NULL
and this time also i got the same result.My question is that is there any specific reason to use inner join when join the third table and not the left outer join.
OLEDB source 1 SELECT ... ,[MANUAL DCD ID] <-- this column set to sort order = 1 ... FROM [dbo].[XLSDCI] ORDER BY [MANUAL DCD ID] ASC
OLEDB source 2 SELECT ... ,[Bo Tkt Num] <-- this column set to sort order = 1 ... FROM ....[dbo].[FFFenics] ORDER BY [Bo Tkt Num] ASC
These two tasks are followed immediately by a MERGE JOIN
All columns in source1 are ticked, all column in source2 are ticked, join key is shown above. join type is left outer join (source 1 -> source 2)
result of source1 (..dcd column) ... 4-400-8000119 4-400-8000120 4-400-8000121 4-400-8000122 <--row not joining 4-400-8000123 4-400-8000124 ...
result of source2 (..tkt num column) ... 4-400-1000118 4-400-1000119 4-400-1000120 4-400-1000121 4-400-1000122 <--row not joining 4-400-1000123 4-400-1000124 4-400-1000125 ...
All other rows are joining as expected. Why is it failing for this one row?
I'm having trouble with a multi-table JOIN statement with more than one JOIN statement.
For each order, I need to return the following: CarsID, CarModelName, MakeID, OrderDate, ProductName, Total ordered the Car Category.
The carid (primary key) and carmodelname belong to the Cars table. The makeid and orderdate belong to the OrderDetails table. The productname and carcategory belong to the Product table.
The number of rows returned should be the same as the number of rows in OrderDetails.
Why would I use a left join instead of a inner join when the columns entered within the SELECT command determine what is displayed from the query results?
I have a merge join (full outer join) task in a data flow. The left input comes from a flat file source and then a script transformation which does some custom grouping. The right input comes from an oledb source. The script transformation output is asynchronous (SynchronousInputID=0). The left input has many more rows (200,000+) than the right input (2,500). I run it from VS 2005 by right-click/execute on the data flow task. The merge join remains yellow and the task never finishes. I do see a row count above the flat file destination that reaches a certain number and seems to get stuck there. When I test with a smaller file on the left it works OK. Any suggestions?
A piece of software I wrote starting timing out on a query that left outer joins a table to a view. Both the table and view have approximately the same number of rows (about 170000).
The table has 2 very similar columns, one is a varchar(1) and another is varchar(100). Neither are included in any index and beyond the size difference, the columns have the same properties. One of the employees here uses the varchar(1) column (called miscsearch) to tag large sets of rows to perform some action on. In this case, he had set 9000 rows miscsearch value to "g". The query then should join the table and view for all rows where miscsearch is set to g in the table. This query takes at least 20 minutes to run (I stopped it at this point).
If I remove the "where" clause and join all rows in the two tables, the query completes in about 20 seconds. If set the varchar(100) column (called descrip) to "g" for the same rows set via miscsearch, the query completes in about 20 seconds.
If I force the join type to a hash join, the query completes using miscsearch in about 30 seconds.
So, this works:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER HASH JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE miscsearch = 'g' ORDER BY balance DESC
and this works:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE descrip = 'g' ORDER BY balance DESC
But this does't:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE miscsearch = 'g' ORDER BY balance DESC
What should I be looking for here to understand why this is happening?
We are trying to migrate from sql 2005 to 2012. I am changing one of the implicit join to explicit join. As soon as I change the join, the number of rows returned are fewer than before.
INSERT #RIF_TEMP1 (rf1_row_no,rf1_rif, rf1_key_id_no, rf1_last_date, rf1_start_date) SELECT currow.rf0_row_no, currow.rf0_rif, currow.rf0_key_id_no, prevrow.rf0_start_date, currow.rf0_start_date FROM #RIF_TEMP0 currow LEFT JOIN #RIF_TEMP0 prevrow ON (currow.rf0_row_no = prevrow.rf0_row_no + 1)
[Code] ....
the count returned from both the queries is different.
I am not sure what am I doing wrong. The count of #RIF_TEMP0 is always 32, it never changes, but the variable @countTemp is different for both the queries.
