Help - SQL Server Join
Aug 10, 1999I am having major trouble joining 2 tables in SQL Server. Each table has about 300,000 records and the join operation is taking well over 30 minutes. Any suggestions ??
View 1 Replies
ADVERTISEMENT
Can Any One Tell Me The Difference Between Cross Join, Inner Join And Outer Join In Laymans Language
Apr 30, 2008Hello
Can any one tell me the difference between Cross Join, inner join and outer join in laymans language
by just taking examples of two tables such as Customers and Customer Addresses
Thank You
Integration Services :: How To Perform Left Restricted Join In Merge Join Transformation
May 22, 2015I have two xml source and i need only left restricted data.
how can i perform left restricted join?
Transact SQL :: Difference Between Inner Join And Left Outer Join In Multi-table Joins?
Oct 8, 2015I 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.
Warning - The Join Order Has Been Enforced Because A Local Join Hint Is Used
Dec 23, 2014I have two select statements, in between select statement taking UNION ALL . I need to avoid the error
Warning: The join order has been enforced because a local join hint is used.
'Left Outer Merge Join' Failing To Join Valid Row
Aug 10, 2007Scenario:
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?
Multi-table JOIN Query With More Than One JOIN Statement
Apr 14, 2015I'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.
Select Command - Left Join Versus Inner Join
Aug 9, 2013Why 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?
View 4 Replies View RelatedMerge Join (Full Outer Join) Never Finishes.
Jun 5, 2006I 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?
View 3 Replies View RelatedWhy Does My Query Timeout Unless Force Join To Hash Join?
Jul 25, 2007I'm using SQL Server 2005.
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?
Thanks,
john
Page 2 - Within The INNER JOIN, How To Limit The Row To 1 Row Inside The INNER JOIN?
Apr 24, 2007Awesome! I don't alway get the email notification of whoever reply to the posting. I think it only work after I log off of the forum.
Scott
Changing From Implicit Join To Explicit Join
Dec 24, 2013We 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.
Below is my Implict join query
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 , #RIF_TEMP0 prevrow
[Code] ....
and below is explict join query
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.
Right Join Returns Same Results As Left Join
Feb 5, 2015Why does this right join return the same results as using a left (or even a full join)?There are 470 records in Account, and there are 1611 records in Contact. But any join returns 793 records.
select Contact.firstname, Contact.lastname, Account.[Account Name]
from Contact
right join Account
on Contact.[Account Name] = Account.[Account Name]
where Contact.[Account Name] = Account.[Account Name]
How To Join 3 Tables Using Left Or Right Join Keyword?
Aug 17, 2007Hi guys,
I'll appreciate any help with the following problem:
I need to retrieve data from 3 tables. 2 master tables and 1 transaction table.
1. Master table TBLOC contain 2 records :
rcd 1. S01
rcd 2. S02
2. Master table TBCODE contain 5 records:
rcd 1. C1
rcd 2. C2
rcd 3. C3
rcd 4. C4
rcd 5. C5
3. Transaction table TBITEM contain 4 records which link to 2 master table:
rcd 1. S01, C1, CAR
rcd 2. S01, C4, TOY
rcd 3. S01, C5, KEY
rcd 4. S02, C2, CAR
I use Left Join & Right Join to retrieve result below (using non-ASNI method) but it doesn't work.
Right Join method:
SELECT C.LOC, B.CODE, A.ITEM FROM TBITEM A RIGHT JOIN TBCODE B ON A.CODE = B.CODE
RIGHT JOIN TBLOC C ON A.LOC = C.LOC
GROUP BY C.LOC, B.CODE, A.ITEM ORDER BY C.LOC, B.CODE
When I use Non-ASNI method it work:
SELECT C.LOC, B.CODE, A.ITEM FROM TBITEM A, TBCODE B, TBLOC C
WHERE A.CODE =* B.CODE AND A.LOC =* C.LOC
GROUP BY C.LOC, B.CODE, A.ITEM ORDER BY C.LOC, B.CODE
Result:
LOC CODE ITEM
-----------------------------
S01 C1 NULL
S01 C2 NULL
S01 C3 CAR
S01 C4 TOY
S01 C5 KEY
S02 C1 NULL
S02 C2 CAR
S02 C3 NULL
S02 C4 NULL
S02 C5 NULL
Please Help.
Thanks.
Super Join - Is Merge Join The Answer?
Nov 7, 2006Is there a way to do a super-table join ie two table join with no matching criteria? I am pulling in a sheet from XL and joining to a table in SQLServer. The join should read something like €œfor every row in the sheet I need that row and a code from a table. 100 rows in the sheet merged with 10 codes from the table = 1000 result rows.
This is the simple sql (no join on the tables):
select 1.code, 2.rowdetail
from tblcodes 1, tblelements 2
But how to do this in SSIS?
Thanks - Ken
How Would You Convert A Hash Join Into A Merge Join?
May 6, 2008I read that merge joins work a lot faster than hash joins. How would you convert a hash join into a merge join? (Referring to output on Execution Plan diagrams.)
THANKS
SQL Server 2008 :: No-lock Across Linked Server With Join?
Mar 9, 2015I have two servers (lets call them sA and sB) connected from sA -> sB via a linked server (i.e. sA pulls data across from sB).
I'm building a temp table full of stock symbols on sA, and then I need to update some values on sA using content on sB. The tables on sB are very large (about 500m rows) so I don't want to pull even close to everything across the linked server. Ordinarily I'd do this by joining to the linked server table, but the target table needs to have nolock on it due to their high use.
update t
set someValue = s.SomeValue
from #myTab t
inner join lnk_sB.xref.dbo.Symbols s with (nolock)
on t.id = s.id
From reading around I gather that nolock doesn't work across linked servers. It was noted in another SSC article that you could use nolock by using an OPENQUERY, but then I can't join to my local temp table, and I end up pulling all .5B rows across the linked server.
Is there some way I can limit the content on sB by my temp table on sA but still use nolock?
Join Bug In SQL Server? (can't Be...)
May 24, 2006If this is a SQL-Server bug, what do I do from here? If it's my problem, I sure can't see it.
The second SELECT in the UNION below is virtually identical to the first. However, the second SELECT joins on a different fields (blue & red ink) and is limited (green ink) in the WHERE clause to show only partially null rows for brevity, but fails to show both partially null rows that do show up in the first. (The Join_Field values are different for each SELECT, so the missing field should not be due to a coalescing of similar rows)
As can be seen, all qualifying source rows are fully shown in the first SELECT because this is a FULL OUTER JOIN, so there is no EmpID = 1 for TeamID = 2 in table X and there is also no EvaluatorID = 1 for TeamID = 2 in table X, but a record for EmpID =1 and TeamID =2 does exist in table Y. Nonetheless, EvaluatorID does not produce the respective, partially null records in the second SELECT.
This appears to me to be a bug in SQL Server, but must be due to something not obvious. I've cloned the data and SQL to a test database, where everything works correctly, so unless there is something else to consider in the schema, there seems to be a bug based on a database with more than just the test tables and fields.
Ancillary information: all fields shown for both tables are members of a compound primary key for their respective tables. There are additional, unused fields in both tables (table X has dozens of defined fields with lots of potential comments and scores). The database currently has no indexes (under development, not yet production, but the deadline is looming large). The database is runing remotely on a test internet site.
SQL Statement:
SELECT 'Y.EmpID=X.EmpID' AS Join_Field, X.CoID, X.EvalID, X.TeamID, X.EvaluatorID, X.EmpID, Y.CoID, Y.TeamID, Y.EmpID AS Y_EmpIDFROM EvalAnswers X FULL OUTER JOIN MergeEmps Y ON X.CoID = Y.CoID AND X.TeamID = Y.TeamID AND Y.EmpID = X.EmpIDWHERE (ISNULL(X.CoID, Y.CoID) = 113) AND (ISNULL(X.EvalID, 1) = 1)UNIONSELECT 'Y.EmpID=X.EvaluatorID' AS Join_Field, X.CoID, X.EvalID, X.TeamID, X.EvaluatorID, X.EmpID, Y.CoID, Y.TeamID, Y.EmpID AS Y_EmpIDFROM EvalAnswers X FULL OUTER JOIN MergeEmps Y ON X.CoID = Y.CoID AND X.TeamID = Y.TeamID AND Y.EmpID = X.EvaluatorIDWHERE (ISNULL(X.CoID, Y.CoID) = 113) AND (ISNULL(X.EvalID, 1) = 1) AND (X.CoID IS NULL OR Y.CoID IS NULL)ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9
Results:
Join_Field X.CoID X.EvalID X.TeamID X.Ev...ID X.EmpID Y.CoID Y.TeamID Y.EmpIDY.EmpID=X.EmpID null null null null null 113 2 1Y.EmpID=X.EmpID 113 1 1 1 1 113 1 1Y.EmpID=X.EmpID 113 1 1 1 2 113 1 2Y.EmpID=X.EmpID 113 1 1 2 1 113 1 1Y.EmpID=X.EmpID 113 1 1 2 2 113 1 2Y.EmpID=X.EmpID 113 1 1 3 3 null null nullY.EmpID=X.EmpID 113 1 2 2 2 113 2 2Y.EmpID=X.EmpID 113 1 2 2 3 113 2 3Y.EmpID=X.EmpID 113 1 2 3 2 113 2 2Y.EmpID=X.EmpID 113 1 2 3 3 113 2 3Y.EmpID=X.EvaluatorID 113 1 1 3 3 null null null
The results for the second SELECT in the UNION should be two partially null rows, not just one.
Join Multi Server
Oct 9, 2006hi,consider this:i have american users in a db called "user" in a server called AMERICAand rest of the world users in a db called "user" in a server called OTHERhow can i do a join betwhen American's users and Rest of the word's User db?
View 3 Replies View RelatedHow Do I Join Two Tables In SQL Server?
Mar 4, 2008I have been reviewing the video series at this site and have found them very helpful but have a need...
I have two data tables one of which is quite large and another table that is small. They have at least one exact field in common, lets call it room number. How do I recognize both tables (in different databases) and join them so I can search a unique item in one table and select its associated data in the other table? I don't image there is a video on this topic for this VB.NET student?
Join Translation In SQL Server
Jun 8, 2004Hi
Can any one please translate these oracle inner join and outer join in SQL
Server. Any help will be highly appreciate.
SELECT V1.DB_VENDOR V1.NAME1,1,21)||DECODE(B1.JOINT_DESC,NULL,'',
'(JV)')||
DECODE(B1.NON_RESP,'Y','(NR)',
'N','') V1_NAME1,
B2.DB_CONTRACT B2_DB_CONTRACT, B2.BID_VENDOR B2_BID_VENDOR, B2.RANK_NUMB B2_RANK_NUMB,
V2.DB_VENDOR V2_DB_VENDOR,
SUBSTR(V2.NAME1,1,21)||DECODE(B2.JOINT_DESC,NULL,' ',
'(JV)')||
DECODE(B2.NON_RESP,'Y','(NR)',
'N','') V2_NAME1
FROM BID_TOTAL B1, BID_TOTAL B2, VENDOR V1, VENDOR V2
WHERE V1.DB_VENDOR = B1.BID_VENDOR
AND V2.DB_VENDOR (+) = B2.BID_VENDOR
AND B1.DB_CONTRACT = B2.DB_CONTRACT (+)
AND B1.RANK_NUMB > 1
AND MOD(B1.RANK_NUMB,2) = 0
AND B2.RANK_NUMB (+) = B1.RANK_NUMB + 1
AND B2.DB_CONTRACT IS NOT NULL
and b2.db_Contract=39624
ORDER BY B1.RANK_NUMB
Sql Server Query For Self Join
Nov 18, 2006Hi
I have following data in a table
Table_Test
ColA ColB ColC ColD ColE (Column Header)
1 NE A CM 5
2 NE A MU 7
3 NE A NO 9
I want to write a query which returns me resultset like this
ColB ColC ColD ColE ColD ColE ColD ColE (Column Header)
NE A CM 5 MU 7 NO 9
can any body tell me, what will be the query for this result set.
Regards
Mahesh
Within The INNER JOIN, How To Limit The Row To 1 Row Inside The INNER JOIN?
Apr 17, 2007There is a table called "tblvZipCodes" that contain a zipcode of all cities, area code that are located in that zip code.
The problem I have with the inner join is that there are more than 1 cities in one zipcode code. Is there a way to just return only the 1st row and not return the rest of the rows from the tblvZipCodes in the INNER JOIN query?
Thanks..
Code:
SELECT TOP 100 PERCENT dbo.tblPurchaseRaw.Year, dbo.tblPurchaseRaw.Make, dbo.tblPurchaseRaw.Model, dbo.tblPurchaseRaw.ModelType,
dbo.tblPurchaseRaw.Color, dbo.tblvZipCodes.ZIPCode, dbo.tblvZipCodes.City, dbo.tblvZipCodes.County, dbo.tblvZipCodes.State,
dbo.tblvZipCodes.AreaCode, dbo.tblvZipCodes.Region, dbo.tblaAccounts.Name, dbo.tblaAccounts.PhoneOne, dbo.tblaAccounts.AccountID,
dbo.tblPurchaseRaw.AcceptedID, dbo.tblPurchaseRaw.Series, dbo.tblPurchaseRaw.BodyStyle, dbo.tblaAccounts.WebSite,
dbo.tblaAccounts.SalesEmail, dbo.tblPurchaseRaw.EmailTo, dbo.tblPurchaseRaw.PhotoURL, dbo.tblPurchaseRaw.Mileage,
dbo.tblPurchaseRaw.RawID, dbo.tblvRegions.Name AS RegionName, dbo.tblPurchaseRaw.VIN, dbo.tblPurchaseRaw.Style,
dbo.tblPurchaseRaw.StockDate
FROM dbo.tblPurchaseRaw INNER JOIN
dbo.tblaAccounts ON dbo.tblPurchaseRaw.AccountID = dbo.tblaAccounts.AccountID INNER JOIN
dbo.tblvZipCodes ON dbo.tblPurchaseRaw.ZipCode = dbo.tblvZipCodes.ZIPCode INNER JOIN
dbo.tblvRegions ON dbo.tblvZipCodes.Region = dbo.tblvRegions.RegionID
WHERE (CONVERT(char, dbo.tblPurchaseRaw.StockDate, 101) <> '01/01/1900') AND (dbo.tblPurchaseRaw.SoldRawID IS NULL) AND
(dbo.tblPurchaseRaw.AcceptedID <> - 10) AND (dbo.tblPurchaseRaw.AcceptedID <> - 1)
ORDER BY dbo.tblvZipCodes.ZIPCode
Inner Join To Outer Join Problem
Mar 1, 2008hello, i am running mysql server 5 and i have sql syntax like this:
select
sales.customerid as cid,
name,
count(saleid)
from
sales
inner join
customers
on
customers.customerid=sales.customerid
group by
sales.customerid
order by
sales.customerid;
it works fine and speedy. but when i change inner join to right join, in order to get all customers even there is no sale, my server locks up. note: there is about 10000 customers and 15000 sales.
what can be the problem?
thanks,
SQL Query Question - JOIN Or Not To JOIN
Jan 2, 2006I have 2 tables, I will add sample data to them to help me explain...Table1(Fields: A, B)=====1,One2,Two3,ThreeTable2(Fields: A,B)=====2,deux9,neufI want to create a query that will only return data so long as the key(Field A) is on both tables, if not, return nothing. How can I dothis? I am thnking about using a 'JOIN' but not sure how to implementit...i.e: 2 would return data- but 9 would not...any help would be appreciated.
View 3 Replies View RelatedUse ANSI Join Or Old Style Join?
May 20, 2006Hi,Just curious. Would you use ANSI style table joining or the 'oldfashion' table joining; especially if performance is the main concern?What I meant is illustrated below:ANSI Styleselect * from a join b on a.id = b.idOld Styleselect * from a, b where a.id = b.idI noticed that in some SQL, the ANSI is much faster but sometimes, theold style looks much better.It's ridiculous to try out both styles to see which is better wheneverwe want to write an SQL statement.Please comment.Thanks in advance.
View 1 Replies View RelatedPerformance Between Standard Join And Inner Join
Jun 28, 2007Hello, everyoneI have one question about the standard join and inner join, which oneis faster and more reliable? Can you recommend me to use? Please,explain me...ThanksChamnap
View 12 Replies View RelatedSelf Join Outer Join Question
Oct 10, 2007Given a table of building components e.g. floors, walls, etc, etc:
create table component_multiplier_table
(
system_code char(4),
system_component_code char(3),
function_code char(4),
component_multiplier dec(7,6)
)
Where function_code is the function of the area e.g. Auditorium, Classrom, etc, etc. And not all components are available for all functions e.g. Carpeting is available for Classrooms but not Power Plants or Warehouses.
I need to self join the above table to itself on system_code and system_component_code and find out which rows are missing from each side.
A query that I've been banging away at with no success is:
SELECT c1.*, c2.*
FROM [dbo].[component_multiplier_table] c1 FULL OUTER JOIN [dbo].[component_multiplier_table] c2
ON (c1.system_component_code = c2.system_component_code) AND (c1.[system_code] = c2.[system_code])
WHERE c1.function_code = '2120' AND c2.[function_code] = '2750' AND (c1.[system_code] IS NULL OR c2.system_code IS NULL);
I added the is null conditions, no joy. I've tried every flavor of outer join w/o success.
Could any T-SQL gurus out there help me figure out how to do this in a set before I start coding
DECLARE crsr CURSOR
Thanks.
How Do I Join Tables From Different Databases (under Same Server Though)
May 12, 2006Hey, I have two databases (db1 and db2) under the same server. How do I combine tables from both of them?I searched the forum and triedSELECT
View 3 Replies View RelatedQuery And Join Between 2 Databases On Same Server?
Nov 9, 2011I am trying to query 2 SQL databases on the same server and inner joining together.
Databases: Goldmine_MIOA
Goldmine_GMBase
From the "Goldmine_MIOA" database I need to query contact1.* and from the "Goldmine_GMBase" database i need to query cal.
I would then like to inner join both these together.
SQL Server 2012 :: INTERSECT Over INNER JOIN
Jan 13, 2014These two T-SQL statements return the same results.
If Microsoft deemed it necessary to add the EXCEPT command, then what are its advantages over an INNER JOIN
-- LIST ONLY PRODUCTS THAT ARE ON A WORK ORDER
USE AdventureWorks2008R2;
GO
SELECT ProductID
FROM Production.Product
INTERSECT
SELECT ProductID
FROM Production.WorkOrder ;
USE AdventureWorks2008R2;
GO
SELECT DISTINCT Production.WorkOrder.ProductID
FROM Production.Product
INNER JOIN Production.WorkOrder ON Production.WorkOrder.ProductID = Production.Product.ProductID
SQL Server 2012 :: Two Tables - Join One To Many
Oct 11, 2015I have two tables, INCIDENT and PEOPLE
INCIDENT has two fields, ID and Date
PEOPLE has three fields, INC_ID [foreign key to INCIDENT.ID], PersonIteration, Complaint
There is always only one incident, but there can be one or more people involved in each incident.
I'd like to retrieve incidents, but have it include information from all involved person on one line.
No matter how I have tried to join these tables, I am ending up with one record each for each person involved in an incident, like so
ID PersonIteration Complaint
1 1 Head
1 2 Neck
1 3 Shoulder
I would like to join in this fashion:
ID PersonIteration1 Complaint1 PersonIteration2 Complaint2
1 1 Head 2 Neck
Is there any way to do this?
SQL Server 2012 :: Syntax On A Join
Oct 23, 2015What is wrong with my syntax?I want to return the value of the AchiveYear Value based on records in theCall that match.
SELECT DATEPART(yyyy,Call_Date) AS ArchiverYear
FROM tblCall
INNER JOIN PrismDataArchive.dbo.ArchiveDriver AS Arch ON tblCall.DATEPART(yyyy,Call_Date) = Arch.ArchiveYear
[code]...