Oi! What follows is a hypothetical situation, but it is a totally analogous to a real problem Im having, but provides an easier model to understand.
Imagine that you have database-driven battleship game and its time to render the board. Also imagine that you have to render more than one board and that the ships are all the size of one point on the grid.
One sql result you need is a list of all the points on the grid, regardless of whether or not there is a ship on it. This will make rendering much easier for you, because you can simply look at the record index to determine if a ship is present. The data that is stored about the position of the ships consists of one record containing the grid index and ship name.
One possible way to retrieve this data is to build a table that you will not change which contains a record for each point on the grid. Is it possible to union or join on this table to retrieve a list of results that contain both unoccupied locations and occupied ones?
Here is what I've come up with, but it contains results that have a null location when there are no ship records:
SELECT
Grids.GridID,
Ships.GridLocation,
Ships.Name
FROM
Grids
FULL OUTER JOIN Ships
ON Ships.GridID = Grids.GridID
WHERE
Grids.PlayerID = 1
UNION-- (**not** UNION ALL)
SELECT
Grids.GridID,
GridLocations.GridLocation,
(SELECT ShipName FROM Ships WHERE GridID = Grids.GridID AND GridLocation = GridLocations.GridLocation)
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
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)
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 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?
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
SELECT * FROM a LEFT OUTER JOIN b ON a.id = b.id instead of
SELECT * FROM a LEFT JOIN b ON a.id = b.id
generates a different execution plan?
My query is more complex, but when I change "LEFT OUTER JOIN" to "LEFT JOIN" I get a different execution plan, which is absolutely baffling me! Especially considering everything I know and was able to research essentially said the "OUTER" is implied in "LEFT JOIN".
The following query doesn't work, it display 1 in leads column instead of 0:
select t.Tdate, count(l.id) as Leads, sum(t.shown) as Views from tracking t left outer join Leads l on r.clientid = t.clientid where l.clientid = 101 and l.Ldate >= 'April 2,2004' and t.Tdate >= 'April 2,2004' group by t.Tdate
The problem is that the Task data in tblLookup consists only of the first 5 chars of the same kind of data in tblRecords (e.g., if a field on that record in tblRecords says "BILLYGOAT", that field in tblLookup is entered only as "BILLY").
I am having problems with an outer join statement. I have written a procedure that tests a table for missing and corrupt data and to test my procedure, I take a table with 100% correct entries and corrupt them by hand. Then I test if my repaird data is looking like the correct data did. To do the test, I copy the correct data into a temp table "copy", join it with the "repaired" table and check if any fields look different. The problem is, that i don't get the missing data. The statement is looking like this:
select o.*,'#',k.* from repaired o right outer join copy k on (str(o.a) + 'A' + str(o.b) + 'A' + str(o.c) =str(k.a)+ 'A' + str(k.b) + 'A' + str(k.c) ) where o.D<>k.D or o.E<>k.E or o.F<>k.F or ...
I have dont the concatenation because I thougt, that a join with 3 fields could be responsible for not finding the missing data in table "copy". Before that it looked like:
... on (o.a=k.a and o.b=o.b and o.c = k.c) where ...
In table "copy" is a record missing that is in table "repaired". Why is my statement not printingout that missing record? Shouldn't be an outer join exactly what I have to use for finding missing data?
I want to see all of the Entity records with their corresponding Address and Phone records. (select e.name, a.street, a.zip, p.phone_number)But only show the Address record for that Entity if the mailing_flag is 'Y' and I only want to see the Top 1 Phone record where the phone_type_key = 'Home'. If the above criteria isn't met I just want to see nulls for the Address and Phone records.
My problem is getting ALL the Entity records to return. It only wants to give me the Entity records that have Address or Phone associated with them. That and somehow showing the Top 1 phone record for the Entity are my issues.
I have the following script which is *sort of working* !!
The problem I have is that I need to add an outer join to one of the tables and I don't know where to add it or what the syntax is.
Basically, anyone who has an 'STRA' role in the contacts_roles table does not usually have an email address (shown as communications.notes). However, because I don't have any outer joins in place, the script is ignoring everyone who has an 'STRA' role and only pulling back those with an 'STRE' role.
Any help would be much appreciated as to how and where I put my outer join.
Thanks so much.
Jon
SELECT contacts.label_name, contact_positions.position, contact_roles.role, contact_roles.organisation_number, communications.notes, organisations.status, organisations.name, addresses.address, addresses.town, addresses.county, addresses.postcode FROM bmf.dbo.addresses addresses, bmf.dbo.communications communications, bmf.dbo.contact_positions contact_positions, bmf.dbo.contact_roles contact_roles, bmf.dbo.contacts contacts, bmf.dbo.organisations organisations WHERE contact_roles.contact_number = contacts.contact_number AND communications.contact_number = contacts.contact_number AND organisations.organisation_number = contact_roles.organisation_number AND addresses.address_number = organisations.address_number AND contact_positions.contact_number = contacts.contact_number AND contact_positions.organisation_number = organisations.organisation_number AND ((contact_roles.role In ('STRE','STRA')) AND (organisations.status In ('BRAN','FULL','HOLD'))) ORDER BY organisations.name
Hi i am having problem getting a resultset in a specific format which i wanted
i am suppose to get this:
team_id|Student|student_not_yet_submitted Team 1|A,B,C|A Team 2|D,E,F|NULL
Where (team_id, student) and student_not_yet_submitted are from different tables. Issue of concatenating aside (i am able to do this with java loop), I derived them like this:
1st select=select team_id, student_name from team, student where (....) to get the 1st 2 columns.
To get the 3rd column, my second select is the same as 1st select but it has an additional condition based on results from 1st select stmt (using the team_id passed in)
2nd select=select team_id, student_name from team where (..... and student_name not in (a 3rd query stmt with result based on team_id from 1st select statement))
i am trying to use left outer join on student_name to join the 2 stmt together, but i am stuck because the 2nd select statement (or rather the 3rd inner query) requires input from the 1st. is there a more efficient way of doing this?
Below is my query. I am a relative novice to SQL. I'd like to rewrite this with joins. All should be inner joins except for the last one Aritem to shmast. That should be a left outer join because not all of our invoices (in the Aritem tables) have actually been shipped.
How would I do this? I have already read through 2 SQL books, but the examples they give are much simpler than what I need to do. Here's the Query:
SELECT DISTINCT Ardist.fcacctnum, Ardist.fcrefname, Ardist.fccashid, Ardist.fcstatus, Armast.fcinvoice, Armast.fbcompany, Armast.fcustno, Ardist.fddate, Ardist.fnamount * -1 as fnAmount, glmast.fcdescr, shmast.fcstate, slcdpm.fcompany as CompanyName FROM ardist, glmast, armast, slcdpm, shmast, aritem WHERE Glmast.fcacctnum = Ardist.fcacctnum AND Armast.fcinvoice = SUBSTRING(Ardist.fccashid,8,20) AND Ardist.fnamount <> 0 AND ((Ardist.fcrefname = 'INV' OR Ardist.fcrefname = 'CRM' OR Ardist.fcrefname = 'VOID') AND Glmast.fccode = 'R') AND armast.fcustno = slcdpm.fcustno AND armast.fcinvoice = aritem.fcinvoice AND left(aritem.fshipkey,6) = shmast.fshipno
what is difference between inner join and outer join also right and left join can you explain with simple example(because i m fresher) and the query which are there in previous forum will work for the mainframe environment?
In my (admittedly brief) sojurn as an SQL programmer I've often admired"outer joins" in textbooks but never really understood their use. I'vefinally come across a problem that I think is served by an outer join.-- This table stores the answer to each test questionCREATE TABLE TestResults (studentIdvarchar (15)NOT NULL,testIdintNOT NULL REFERENCES Tests(testId),qIdintNOT NULL REFERENCES TestQuestions(qId),responseintNOT NULL REFERENCES TestDistractors(dId),CONSTRAINT PK_TestResultsPRIMARY KEY NONCLUSTERED (testId, studentId, qId),)-- This table defines which questions are on which testsCREATE TABLE TestQuestions_Tests (testIdintNOT NULL REFERENCESTests(testId),qIdintNOT NULL REFERENCESTestQuestions(qId),)(Table Tests contains housekeeping information about a particular test,TestQuestions defines individual questions, TestDistractors lists thepossible responses.)In schematic form, the simplest form of my problem is to find all thequestions that haven't been answered. That would be:SELECT tqt.qIdFROM TestResults AS trRIGHT OUTER JOIN TestQuestions_Tests AS tqtON tr.testId = tqt.testId AND tr.qId = tqt.qIdWHERE tr.qId is NULLSo far I think this is pretty straightforward and an efficient solution.Agreed?But my real problem is a little bit more complex. What I really want toknow is "for a given student, on a given test, which questions haven'tbeen answered?"So now I have:SELECT tqt.qIdFROM TestResults AS trRIGHT OUTER JOIN TestQuestions_Tests AS tqtON tr.testId = tqt.testId AND tr.qId = tqt.qIdWHERE tqt.testId = '1' AND tr.studentId = '7' AND tr.qId IS NULLIs this the canonical form of the solution to my problem? It seems tome like it is generating a whole slew of rows and then filtering them.Is there a more elegant or efficient way to do it?-- Rick
Wonder if any could help be putting together a SQl select statement. I have 2 tables of road-data, one having default data for the area, and one with actual data. They look like this:
Create table AreaDefaults { AreaCode Int(3), RoadCode Int(3) } Insert into table AreaDefaults values (34, 21); Insert into table AreaDefaults values (35, 21); Insert into table AreaDefaults values (36, 21); Insert into table AreaDefaults values (37, 21);
Insert #Prior_Year_YTD_Amount Select Year, Month, Account_No, Amount as Prior_Year_YTD_Amount From Financial_Values Where Financial_Value.Year = @Current_Year
And (Financial_Value.Month >= 1 and <= @Current_Month)
From #Current_Values RIGHT OUTER JOIN #Prior_Year_Values On #Current_Values.Year = #Prior_Year_Values.Year And #Current_Values.Month = #Prior_Year_Values.Month And #Current_Values.Account_No = #Prior_Year_Values.Account_No
From #Prior_Year_Values RIGHT OUTER JOIN #Current_Values On #Prior_Year_Values.Year = #Current_Values.Year And #Prior_Year_Values.Month = #Current_Values.Month And #Prior_Year_Values.Account_No = #Current_Values.Account_No
Steps 1 thru 6 are working fine, however when I get to Step 7, my stored procedure fails with
trying to insert into #Current_and_Prior_Year_Values a null value the primary key Account_No.
If I create all the tables not as temporary tables it still fails the same way, however
if I don't run step seven and then run views like the select statements in Step 7
I get the correct results from the views.
Also if a perform an inner join in step seven vs an right outer join, the step does not fail with
the null insert, however I don't the right number of rows (account no)
I quess my question is why would the right outer joins in step 7, run as part of a sp, return
any null Account No values?
Or could anyone suggest a different way to get the result set I need?
I am trying to grab All Sales Reps (no matter if they have any valid records, i just want to show all names) and Display all question descriptions (even if the question wasnt answered). So it should look like this: Travel Holiday Sick Ride With Office Day Vacation Seminar Sally jones 5 1
Kim Smith 2 Tawny Rodes Jim Tyler Steven Jones Calvin Moore Tina Hood Cristine Smart 3 20 Mark foley Fred Rogers 2
Instead im getting this:
Travel Holiday Ride With Vacation Sally jones 5 1
Kim Smith 2 Tawny Rodes Cristine Smart 3 20 Fred Rogers 2
Here's my query:
Code Block SELECT Qry_Sales_Group.Name, Qry_Sales_Group.SR_NAME, Qry_Sales_Group.Salesperson_Purchaser_code as SR_Code, CONVERT(datetime, DATEADD(day, q.cycle_day - 1, q.start_date), 6) AS Logged_Time, y.question_code, y.description, q.response
FROM Qry_Sales_Group LEFT OUTER JOIN(SELECT CONVERT(datetime, DATEADD(day, dbo.question_history.cycle_day - 1, dbo.period.start_date), 6) AS Logged_Time, Qry_Sales_Group.SR_Name, Qry_Sales_Group.Name, questions.question_code, questions.description, question_history.response, entity_code, cycle_day, start_date FROM dbo.questions INNER JOIN question_history ON questions.question_code = question_history.question_code INNER JOIN period ON question_history.period_code = period.period_code RIGHT OUTER JOIN Qry_Sales_Group ON SUBSTRING(dbo.question_history.entity_code, 1, 5) = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code COLLATE Latin1_General_CI_AS
WHERE CONVERT(datetime, DATEADD(day, dbo.question_history.cycle_day - 1, dbo.period.start_date), 6) = '11/14/2007' AND (dbo.questions.question_code IN ('AME01', 'ASE01', 'ACO01', 'ALU01', 'AOS01', 'APH01', 'ATR01', 'ATE01', 'ACR06', 'ACR05', 'ACR02', 'ACR03', 'ACR08', 'ACR07')) ) q ON SUBSTRING(q.entity_code, 1, 5) = Qry_Sales_Group.SalesPerson_Purchaser_Code COLLATE Latin1_General_CI_AS RIGHT OUTER JOIN(Select description, question_code from Questions Where questions.question_code IN ('AME01', 'ASE01', 'ACO01', 'ALU01', 'AOS01', 'APH01', 'ATR01', 'ATE01', 'ACR06', 'ACR05', 'ACR02', 'ACR03', 'ACR08', 'ACR07') )y ON y.question_code=q.question_code
order by QRY_SALES_GROUP.SR_NAME
Please help. If i do a Right outer join, i get all the questions i want. But if i do a left outer join i get all the sales reps. How do i get both??
SELECT S.SessionID,S.SessionName,T.number FROM Sessions S LEFT OUTER JOIN (SELECT SessionID,COUNT(*) AS number FROM EventLog WHERE MachineID = @machineID GROUP BY SessionID) AS T ON S.SessionID = T.SessionID ORDER BY S.SessionID
The result sets T.number as NULL if there is no record related to SessionName. How to change NULL to ZERO? Thanks a lot.
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?
helloi know that this post is not related to asp.net forum but if anyone can help me.i have made three sql tables called table1,table2 and table3.each one contains primary field called employeeidtable1 contains in addition to the primary a field callled field1.table2 contains in addition to the primary a field callled field2.table3 contains in addition to the primary a field callled field3.the first time table1 contains one record employeeid field1 ------------ ------ 1353 abcthe second table contains no datathe third table contains also one record employeeid field3 ------------ ------ 1353 defi have made a query :select field1,field2,field3 from table1full outer join table2 on table1.employeeid=table2.employeeidfull outer join table3 on table2.employeeid=table3.employeeidthe result is :field1 field2 field3abc null nullnull null def when i delete the record from the first table and put it in the second empty table:the result :field1 field2 field3null abc defi need to understand the results ?i know that the outer join will get the rows from the both tables,but the results how can i get i don't understandthank you for the help
This FULL OUTER JOIN seems inconsistent between two SELECT statements—it works right for one, but not for another, which is virtually identical (see below). I am looking for missing records, and the problem is that the NULL half of the joined record is showing up for one case, but NOT showing up for the other, as it should.
Fields A, B, C, D1, and D2 make up the complete primary key in table X (in that order) while fields A, C, and D make up the complete primary key in table Y (in that order). The example below works correctly for the first, but not for the second (all other SQL-Statement details are exactly identical).
… FROM X FULL OUTER JOIN Y ON X.A=Y.A AND X.C=Y.C AND X.D2=Y.D … … FROM X FULL OUTER JOIN Y ON X.A=Y.A AND X.C=Y.C AND X.D1=Y.D …
The value 1 exists for Y.D, but is missing from both D1 and D2 in table X (matching values for A and C exist in both tables). Again, the problem is that the null, outer-joined record shows up only in the first example above.
The only difference I can see in the schema is that D2 is the last element of the primary key of X (and it works OK), while D1 is the second to last element of the same primary key (and it does not work). All key elements are INTEGER fields in SQL SERVER. For this test case I currently have no indexes in the database.
Does this look like a SQL-Server bug? am I missing something? help! I don't know how to work around this.
Hello! I need to write a query using left outer join and I'm having trouble with it. I have 2 tables:customer and cust_info. I want to pull all records from customer table and cust_info table even if there is no related data in cust_info table. But I need one condition in this query:only records from customer table where cus_type in ("A","B","C"). I don't need all other types ("D","E").
So my query looks like this:
select customer.cus_name,customer.cus_address,customer.cu s_type,cus_info.status from customer left outer join cus_info ON customer.sxdat_pk = cus_info.sxdat_pk and cus_type in ("A","B","C") AND cus_info.cus_table = 'CUSTOMER'
The result should be like this:
cus_name cus_address cus_type status Amoco 457 bent A new Bingo 47 lone oak C NULL Sears 1020 Magic dr. B exist
But my query pulls records for customers with type "D" and "E" that I'm trying to exclude from result.
In a database here we have a table that is a list of codes. Many other tables in our database have foreign keys to this table (T_TYPE_CODE). The joined column is an integer that is a clustered index on the table.
One of the developers here says that in one of his queries he has to OUTER JOIN to this table 15 times and that the OUTER JOIN is killing performance. He wants to add a record to T_TYPE_CODE that will represent NULL so that any NULL values in the tables that foreign key to this table will use that ID instead of NULL. In this way he could use all INNER JOINs.
To me this seems like a bad idea - NULL is NULL and creating a value to represent NULL will open a whole can of worms.
My question: Is there a performance hit for using OUTER JOINs against this table, considering that the join is on a single column and is a clustered, unique index?
Also, what problems can we expect to run into if we use a
I have a issue with using sum in an outer join query. I have created a view ( vw_spREPORT ) and run a left outer join of the view with a table ( exposure ). This correctly returns me everything from the query, however the query doesn't sum the relevant columns:
select v.clientid, client, relationshipid, relationship, CBISECTOR, sum(e.amount) from vw_spCBIPROPERTY v left outer join exposure e on v.clientid = e.clientid group by e.amount, v.clientid, v.client, relationshipid, relationship, CBISECTOR
I don't know enough about outer joins to predict why the sum / group doesn't work.
