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 want to use multiple datasets in a table and wants to do the full outer join on the two datasets in the same table. For example, my two datasets are:
I want to display a ssrs table like:  Both the datasets are coming from different sources. So I cannot integrate them at sql query level.
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.
I'm trying to join 3 tables in an outer join since I am loosing records that need to be included if I only use an inner join. I am pulling data from an MSDE database using the microsoft query tool.
The problem is that I get the message that I can't use an outer join on a query with more than 2 tables, but that can't be right can it?
I'm a SQL code novice so any help would be greatly appreciated!
SELECT Article.articleId , Article.articleName , Article.articleStatus , Articlegroup_2.ArticlegroupId , Articlegroup_2.g2_key , Articlegroup_2.g2_name , articleGroup.articleGroupId FROM HIP.dbo.Article Article, HIP.dbo.articleGroup articleGroup, HIP.dbo.Articlegroup_2 Articlegroup_2 WHERE articleGroup.articleGroupId = Article.articleGroupId AND Article.articleGroupId2 = Articlegroup_2.Articlegroup_2_Id
I'm trying to join 2 tables in an outer join, but MS Query won't let me do this because I have another 2 tables included in an inner join ("only two tables are allowed in an outer join"). I am pulling data from an MSDE database using the microsoft query tool.
I'm a SQL code novice so any help would be greatly appreciated!
Here is my existing SQL query (without the new outer join table):
I am trying to select specific columns from multiple tables based on acommon identifier found in each table.For example, the three tables:PUBACC_ACPUBACC_AMPUBACC_ANeach have a common column:PUBACC_AC.unique_system_identifierPUBACC_AM.unique_system_identifierPUBACC_AN.unique_system_identifierWhat I am trying to select, for example:PUBACC_AC.namePUBACC_AM.phone_numberPUBACC_AN.zipwhere the TABLE.unique_system_identifier is common.For example:----------------------------------------------PUBACC_AC=========unique_system_identifier name1234 JONES----------------------------------------------PUBACC_AM=========unique_system_identifier phone_number1234 555-1212----------------------------------------------PUBACC_AN=========unique_system_identifier zip1234 90210When I run my query, I would like to see the following returned as oneblob, rather than the separate tables:-------------------------------------------------------------------unique_system_identifier name phone_number zip1234 JONES 555-1212 90210-------------------------------------------------------------------I think this is an OUTER JOIN? I see examples on the net using a plussign, with mention of Oracle. I'm not running Oracle...I am usingMicrosoft SQL Server 2000.Help, please?P. S. Will this work with several tables? I actually have about 15tables in this mess, but I tried to keep it simple (!??!) for the aboveexample.Thanks in advance for your help!NOTE: TO REPLY VIA E-MAIL, PLEASE REMOVE THE "DELETE_THIS" FROM MY E-MAILADDRESS.Who actually BUYS the cr@p that the spammers advertise, anyhow???!!!(Rhetorical question only.)
I'm trying to run a SELECT on 3 tables: Class, Enrolled, Waiting. I want to select the name of the class, the count of the students enrolled, and the count of the students waiting to enroll.
My current query...
SELECT Class.Name, COUNT(Enrolled.StudentID) AS EnrolledCount, COUNT(Waiting.StudentID) AS WaitingCount FROM Class LEFT OUTER JOIN
Enrolled ON Class.ClassID = Enrolled.ClassID LEFT OUTER JOIN
Waiting ON Class.ClassID = Waiting.ClassID GROUP BY Class.Name
...results in identical counts for enrolled and waiting, which I know to be incorrect. Furthermore, it appears that the counts are being multiplied together (in one instance, enrolled should be 14, waiting should be 2, but both numbers come back as 28).
If I run this query without one of the joined tables, the counts are accurate. The problem only occurs when I try to pull counts from both the tables.
Can anyone find the problem with my query? Should I be using something other than a LEFT OUTER JOIN?
We have two tables that have somewhat of a parent-child relationship. We are trying to use a SQL-92 outer join that returns the same results as a TSQL *= outer join. The difficulty we are having is that some of the parent records do not have any corresponding child records, but we still want to see those parent records with 0 (zero) for the count. How can we accomplish this with a SQL-92 compliant join (if it is even possible)? In the query results below, we would like the first set of results.
Thanks in advance for any help. -David Edelman
Test script below, followed by results =========================================== create table parent (p_id int NOT NULL) go create table child (p_id int NOT NULL, c_type varchar(6) NULL) go insert parent values (1) insert parent values (2) insert parent values (3) insert parent values (4) insert parent values (5) insert parent values (6) insert parent values (7) insert parent values (8) insert parent values (9) insert parent values (10) go
I have a MSDE query that includes a "left outer join..." clause. It runs fine in MSDE Query (a 3rd party GUI tool) and produces 12 rows. column 3 has some NULL values (because of the outer join).
But when I use the same query in an ASP.NET page, and display the result in a datagrid, it only displays 7 rows - the rows with the NULL value in column 3 do not display.
Is there a parameter somewhere in datagrid or dataset that I should be setting?
Hi!I have a problem with a query:Two tables:CREATE TABLE Emp (empno INT, depno INT)CREATE TABLE Work (empno INT, depno INT, date DATETIME)I want a list of all employees that belongs to a department (from Emptable), together with ("union") all employeees WORKING on that department aspescial day (An employee can have been borrowed from another departmentwhich he does not belong)Sample dataINSERT INTO Emp (empno, depno) VALUES (1,10)INSERT INTO Emp (empno, depno) VALUES (2,10)INSERT INTO Emp (empno, depno) VALUES (3,20)INSERT INTO Work (empno, depno, date) VALUES (1,10,'2003-10-17')INSERT INTO Work (empno, depno, date) VALUES (3,10,'2003-10-17')INSERT INTO Work (empno, depno, date) VALUES (3,10,'2003-10-18')Note that Employee 3 works on a department to which he does not belong (heis borrowed to another department)The following querySELECT empno, depno, date FROM work WHERE depno = 10 AND date = '2003-10-17'gives me this result set:empno depno date1 10 2003-10-17 00:00:00.0003 10 2003-10-17 00:00:00.000But I want employee 2 to appear in the result set as well, because hebelongs to department 10 (eaven thoug he is not working this particular day)The result set should look like thisempno depno date1 10 2003-10-01 00:00:00.0002 10 NULL3 10 2003-10-01 00:00:00.000I have tried different approaches, but none of them is good.Could someone please help me?Thanks in advanceRegards,Gunnar VøyenliEDB-konsulent asNORWAY
Hi,I need your help to resolve this problem. I have written a right outerjoin query between 2 indipendent tables as follows.select b.Account_desc, b.Account, a.CSPL_CSPL from Actual_data_final aright outer join Actual_account_Tbl b on a.account_desc =b.account_desc where (a.source_type = 'TY02' or a.source_type isnull) and (a.month = '2ND HALF' or a.month is null) and (a.year = 2004or a.year is null) and (a.product = 'NP' or a.product is null) orderby b.SnoBut the problem is I have few records in table Actual_account_Tbl butdo not match the condition "a.account_desc = b.account_desc".As per right outer join, I suppose to get those records as a result ofthe above query with null values of a.CSPL_CSPL. But it is notdisplaying.Please help me to resolve this problem.Regards,Omav
I am reporting on a system with 32 devices, each of these devices can have certain events that happen to it that are logged and timestamped. I need a table to show the count of each events that have happened to it within a certain time period. This code snippet below works fine BUT if there are no events that happen to a certain device in the time period, then that device is 'missing' from the table. What I need is basically a row for every device, regardless of if it has had any events happen to it (I will just show '0' for the event count) Any thoughts? I'm a complete newbie at this by the way.
Thanks
Code Snippet
SELECT DeviceStatusWords.DeviceName, COUNT(DeviceEventDurationLog.StatusBit) AS BitCount, DeviceEventDurationLog.StatusBit AS Bit FROM DeviceEventDurationLog RIGHT OUTER JOIN DeviceStatusWords ON DeviceEventDurationLog.DeviceID = DeviceStatusWords.DeviceID WHERE (DeviceEventDurationLog.TimeIn > @StartDate) AND (DeviceEventDurationLog.TimeIn < @EndDate) GROUP BY DeviceStatusWords.DeviceName, DeviceEventDurationLog.StatusBit ORDER BY DeviceStatusWords.DeviceName
I am stuck in a bit of a mess, and i really don't know how to get out of it.
I am a dot.net developer, working with MS SQL server 2000.
I am trying to write a query which essentially gives the amount of stock available for a particular stock_code.
select Stock_code, description from stock
Now what i want to do is, for each one of the stock_code that apears in the table above i want to look into another table called pop_item, and get the closest delivery_date for that particular stock_code. So i have top 1 and order it by delivery_date desc.
Individually for a particular stock_code, i can do this no problem. And here is my code for stock_code='E0016/6'
select top 1 stock_code, delivery_date, order_number,qty_order-qty_delivered as onorder from pop_item where stock_code='E0016/6' and qty_order>qty_delivered order by delivery_date desc
But I can't seem to be able to do this for all the stock_code, and not a specific one, cause even though i try and left outer join it, i can't access the outer stock_code from the first query into the next...
i.e
select stock.Stock_code, description, tempp.stock_code, tempp.delivery_date, tempp.onorder from stock
left outer join
(select top 1 stock_code, delivery_date, order_number,qty_order-qty_delivered as onorder from pop_item where
--Can't say this(stock_code= stock.stock_code and ) qty_order>qty_delivered order by delivery_date desc) as tempp
on tempp.stock_code=stock.stock_code
Now my question is, is there anyway to access stock.stock_code within the second query? Casue the whole query on top returns only one value for delivery_date, only of the highest delivery date in the whole of pop_item. which make sense... but i don;t know how to get around this...
OOOOOOOOOOOOOOOOOOhhhhhhhhhhhhhhhhhhhhhhhhhhhhh!
Hope someone can help me.
Regards,
Munira
ps- should i be using a cursor, can i call cursors from asp.net. every where i read about cursors they adivice us not to use them.
Hi - I'm struggling with a query, which is as follows. (I have changed the context slightly for simplicity)
I have 4 tables: users, scores, trials, tests Each pair of users takes a series of upto 4 tests in 1 trial, getting a score for each test. There are a different numbers of trials for each pair of users.
Important: Users do not take all tests. EG TrialId 1 contains userA & userB with userA scoring 10 on test1, 20 on test2 and userB scoring 30 on test2, 40 on test3, 50 on test4 and is userA & userB's 1st attempt. TrialId 2 may be the same, but their 2nd attempt. TrialId 3 may be the 1st attempt for 2 different users etc.
Suppose the Tests table has 4 tests (1,test1),(2,test2),(3,test3),(4,test4)
There are always 2 users for each trial id.
I want a query which will return all scores for all users for all trials, BUT must include NULLs if a user did not take a test on that trial.
I thought it may involve a cross join between the Tests table and the Trials table.
I was trying to run the follwoing query.It is giving me error
"select woancestor.wonum,wplabor.laborcode, wplabor.quantity,wplabor.laborhrs from wplabor,workorder left outer join woancestor on workorder.wonum=woancestor.wonum and wplabor left outer join labtrans on wplabor.laborcode=labtrans.laborcode where woancestor.ancestor='572099' and workorder.istask=1 and workorder.wonum=wplabor.wonum"
Error is "Server: Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'left'."
But the following query works fine
"select woancestor.wonum,wplabor.laborcode, wplabor.quantity,wplabor.laborhrs from wplabor,workorder left outer join woancestor on workorder.wonum=woancestor.wonum where woancestor.ancestor='572099' and workorder.istask=1 and workorder.wonum=wplabor.wonum"
I came across this statement from ASP.NET forum : "..There is a limit to the level for OUTER JOIN ANSI SQL limit is four after that you may get strange results. ..." . I did a little research but without getting clear answer from the SQL92 standard itself. I am wondering whether I can get help about this in SQL Server 2005 implementation here.
I put this question in another way, How many tables can we use in OUTER JOIN(or INNER JOIN) in SQL Server 2005?
Hi, I have this oracle query with outer join situation. how can i convert it into sql server query.
SELECT distinct ae.dB_CONTRACT,CP.PC_CODE,BID_ITEM.ITEM_NO, 'N',BID_ITEM.PRICE_WORDS,OFF_ITEM.DESCPT, OFF_ITEM.UNITS,OFF_ITEM.TYPE_ITEM, PRES_ITEM.RET_PERC FROM BID_TOTAL,BID_ITEM,OFF_ITEM,PRES_ITEM, AE_CONTRACT AE, CONTRACT_PC CP WHERE RANK_NUMB = 1 AND BID_TOTAL.DB_CONTRACT = 37044 AND BID_TOTAL.DB_CONTRACT = BID_ITEM.DB_CONTRACT AND BID_TOTAL.BID_VENDOR = BID_ITEM.BID_VENDOR AND BID_ITEM.DB_CONTRACT = OFF_ITEM.DB_CONTRACT AND BID_ITEM.ITEM_NO = OFF_ITEM.ITEM_NO AND OFF_ITEM.ITEM_NO = PRES_ITEM.ITEM_NO (+) AND AE.DB_CONTRACT=BID_TOTAL.DB_cONTRACT AND CP.DB_CONTRACT = AE.DB_CONTRACT AND CP.pc_code = 1
This might not be possible, but on the chance that it can - is there away to do the following:Given a arbitray one dimesional value list:('AALGX','12345','XXXXX','AAINX','AMMXX')Is there a way that I could do a select statement, or similiar, in thevalue list, to get the following resultfield_name-----------AALGX12345XXXXXAAINXAMMXXBecause, what I want to be able to do in the long run is essentiallyperform an outer join on the value list.Something along the lines ofselect value_list.field_name, dbtable.otherfield FROM value_list leftouter join dbtable on value_list.field_name = dbtable.field_nameSo I want all the values in the field list to show up, and anymatching data in the database table that exists, otherwise null.Maybe there is another approach to this???Thanks!KT
I need to convert a OUTER APPLY hint in my query to LEFT JOIN.How it can be done?The code which is presently is this: OUTER APPLY Additional Fields. nodes('/AdditionalFields/AdditionalField') AS AF (C)
The 2nd table is for storing product codes for customers, in other words, one product can have different ProductCode for different customers. But some customers do not have ProductCode for a ProductID.
I want to create a query to return all the Products and its ProductCode (null is valid) for a specific customer.
I tried:
SELECT dbo.tblProductCodes.ProductCode, dbo.tblProductCodes.CustomerID, dbo.tblProducts.ProductName, dbo.tblProducts.ProductID FROM dbo.tblProducts LEFT OUTER JOIN dbo.tblProductCodes ON dbo.tblProducts.ProductID = dbo.tblProductCodes.ProductID WHERE dbo.tblProductCodes.CustomerID = 2
But the query left out all products that does not have ProductCode value in tblProductCodes table for CustomerID = 2. I want all the ProductName returned from query and render null or empty string for ProductCode value if the code does not exist in tblProductCodes table for the customer.
Cod_Lingua - Des_Lingua ------------------------------ ITA Italian GER German ENG English FRA French
and another table with product/description
ProductID - Cod_Lingua - Description ------------------------------------------- 1 ITA Mia Descrizione 1 ENG My Description
I've this SELECT:
SELECT Tab_Lingue.Cod_Lingua, Descrizioni_Lingua.Description FROM Descrizioni_Lingua RIGHT OUTER JOIN Tab_Lingue ON Tab_Lingue.Cod_Lingua=Descrizioni_Lingua.Cod_Lingua WHERE Descrizioni_Lingua.ProductID=1
I get these results: ITA - Mia Descrizione ENG - My Description
I don't want this. I'd like to have this: ITA - Mia Descrizione ENG - My Description GER - (null) FRA - (null)
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 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?
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out How i can fetch the lotus notes data in my asp.net pages.
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out How i can fetch the lotus notes data in my asp.net pages.
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.
hello, 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,
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