Why I Got Added Rows When Using FULL OUTER JOIN?
Nov 19, 2006
I've met a curiouse thing when using full outer join, that I got some rows belong to neither tables @_@
The SQL statement is like this:
SELECT PreviousMonth.InvoiceID, PreviousMonth.CategoryName, PreviousMonth.ProdName, PreviousMonth.Qty, ISNULL(CurrentMonth.SellTotal, 0)
AS SellTotal, ISNULL(PreviousMonth.SellTotal, 0) AS PriorSellTotal
FROM (SELECT SellTotal, InvoiceID, CategoryName, ProdName, Qty
FROM viewProdSales AS viewProdSales_1
WHERE (DateCreated >= @pStartDate) AND (DateCreated <= @pEndDate)) AS CurrentMonth
FULL OUTER JOIN
(SELECT SellTotal, InvoiceID, CategoryName, ProdName, Qty
FROM viewProdSales
WHERE (DateCreated >= @pPriorStartDate) AND (DateCreated < @pStartDate)) AS PreviousMonth
ON
CurrentMonth.InvoiceID = PreviousMonth.InvoiceID
The result include some added rows, if I query the 2 sub table seperately, neither contains such rows.
For example, if I pass 01/09/2006, 01/10/2006 and 31/10/2006 as pPriorStartDate, pStartDate and pEndDate, to query the sales statistics in Sept and Oct, I would get some reords in June or even earlier... Anyone know about this? Thanks in advance.
Summer
View 3 Replies
ADVERTISEMENT
Jun 5, 2006
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?
View 3 Replies
View Related
Nov 1, 2005
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
View 1 Replies
View Related
May 23, 2006
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.
View 6 Replies
View Related
Nov 5, 2007
Hello!!
i have two tables
scanner_input:
id | quantity | status
-----------------------------------
prod1 | 6 | OK
prod2 | 7 | OK
prod3 | 3 | P
prod4 | 3 | OK
prod5 | 3 | OK
prod7 | 3 | OK
and
sap_input:
id | quantity | status
-----------------------------------
prod1 | 6 | OK
prod2 | 7 | OK
prod3 | 3 | P
prod4 | 2 | OK
prod6 | 3 | OK
prod8 | 3 | OK
i need a query to join both tables and compare the quantities of an item in table SCANNER_INPUT with its
corresponding item in SAP_INPUT, so the result would be something like this:
sap_input
id(scanner)| qty(scan)| id(sap) | qty(sap) | difference
------------------------------------------------------------
prod1 | 6 | prod1 | 6 | 0
prod2 | 7 | prod2 | 7 | 0
prod3 | 3 | prod3 | 3 | 0
prod4 | 3 | prod4 | 2 | 1
prod5 | 3 | prod5 | | 3
prod6 | | prod6 | 3 | -3
prod7 | 3 | prod7 | | 3
prod8 | | prod8 | 3 | -3
I found i can use a full outer join... but i dont know how to use it.
any ideas??
thnx
View 15 Replies
View Related
Jul 20, 2005
I have the following query:SELECT dbo.tSymExch.exCode, dbo.tSymGrp.sgCode,dbo.tSymMain.smCodeFROM dbo.tSymExch FULL OUTER JOINdbo.tSymGrp ON dbo.tSymExch.exID =dbo.tSymGrp.sgexID FULL OUTER JOINdbo.tSymMain ON dbo.tSymGrp.sgID =dbo.tSymMain.smsgIDWhich produces:exCodesgCodesmCodeCBOTCBOTTRSCB2NCBOTCBOTTRSCB30BCBOTCBOTTRSCB10NCBOTCBOTTRSCB5NCBOTCBOTEQCMEI want it to produce:exCodesgCodesmCodeCBOTCBOTTRSCB2NCBOTCBOTTRSCB30BCBOTCBOTTRSCB10NCBOTCBOTTRSCB5NCBOTCBOTTRSCBOTCBOTEQCBOTCME
View 7 Replies
View Related
Oct 26, 2006
Hi,
i want to replace a
full outer join statement .
how do i?
i am more in control with '='
thanks/
View 8 Replies
View Related
Nov 28, 2007
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:
Sales Rep. Visits Vacation Sick
Bob Smith 2 5 0
Julie Troy 1 0 1
Stefani Gray 1 0 0
--------------
I want it to look like this
Sales Rep. Visits Vacation Sick Drive-with Seminar Office-day Meeting
Bob Smith 2 5 0 0 0 0 0
Tod Taylor 0 0 0 0 0 0 0
Matt Mathews 0 0 0 0 0 0 0
Julie troy 1 0 1 0 0 0 0
Stefani Gray 1 0 0 0 0 0 0
heres my code:
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
View 5 Replies
View Related
Mar 6, 2008
Hey all,
Anyone have a work around for using a TSQL FULL OUTER JOIN in CE? Something like a Right and a Left Outer Join for the below?
Thanks,
billb
Here's an example:
' Connection To SQL CE
Dim ConsqlShipTo As New SqlCeConnection
ConsqlShipTo.ConnectionString = "Data Source = " & Environment.GetFolderPath(Environment.SpecialFolder.ProgramFiles) & "CRM.sdf"
'
'TSQL STATEMENT......
Dim reqSQL As New SqlCeCommand
reqSQL.CommandText = "SELECT " & _
" COALESCE(ShipTo.ShipToCust, Prospect.ProspectCust) AS [Customer ID], " & _
" COALESCE(ShipTo.ShipToName, Prospect.ProspectName) AS Name, " & _
" COALESCE(ShipTo.ShipToCity, Prospect.ProspectCity) AS City " & _
" FROM ShipTo FULL OUTER JOIN " & _
" Prospect ON ShipTo.ShipToName LIKE Prospect.ProspectName AND Prospect.ProspectCust LIKE ShipTo.ShipToCust " & _
" WHERE " & _
" (Prospect.Alias = '" & reqGetUserName & "' AND Prospect.ProspectName LIKE '%" & reqSearch & "%') " & _
" OR (ShipTo.Alias > '%%' OR ShipTo.Alias IS NULL) " & _
" AND (ShipTo.ShipToName LIKE '%" & reqSearch & "%') " & _
" GROUP BY " & _
" ShipTo.ShipToCust, Prospect.ProspectCust, ShipTo.ShipToName, Prospect.ProspectName, " & _
" ShipTo.ShipToCity, Prospect.ProspectCity " & _
" ORDER BY Name "
reqSQL.CommandType = CommandType.Text
reqSQL.Connection = ConsqlShipTo
ConsqlShipTo.Open()
'
'CREATE NEW SQL DATA ADAPTER
Dim sqlda As New SqlCeDataAdapter(reqSQL)
'
' FILL DATASET
Dim sqlds As New DataSet
sqlda.Fill(sqlds, "Customer ID")
'
' FILL DATATABLE - lightweight
Dim sqldt As New DataTable
sqlda.Fill(sqldt)
View 4 Replies
View Related
Aug 3, 2006
I am writing a script to create a audit trigger on any table. I am getting duplicate rows inserted into my audit table, only for the primary key columns. Anybody see why?
Right now I am debugging an Insert, so I think you can ignore the "U" update part of the Where clause.
....starts with other code to determine columns and primary key fields for selected table....
--get number of columns
select
@rowId = min(RowId),
@MaxRowId = max(RowId)
from #tblFieldNames
-- Loop through fields and build Sql string
while @RowId <= @MaxRowId
BEGIN
SELECT @fieldname = colName FROM #tblFieldNames WHERE RowId = @RowId
SELECT @sql = 'insert tblAuditAdmin (TableAltered, [Action], FieldName, OldValue, NewValue, UpdateDate, UpdateNumber, UserName)'
SELECT @sql = @sql + ' select distinct''' + @TableName + ''''
SELECT @sql = @sql + ',''' + @TriggerType + ''''
SELECT @sql = @sql + ',''' + @fieldname + ''''
SELECT @sql = @sql + ',convert(varchar(1000),d.' + @fieldname + ')'
SELECT @sql = @sql + ',convert(varchar(1000),i.' + @fieldname + ')'
SELECT @sql = @sql + ',''' + @UpdateDate + ''''
SELECT @sql = @sql + ', 1'
SELECT @sql = @sql + ',''' + @UserName + ''''
SELECT @sql = @sql + ' FROM #ins i FULL OUTER JOIN #del d'
SELECT @sql = @sql + @pkJoinClause
SELECT @sql = @sql + ' WHERE (''' + @TriggerType + ''' = ''I'')'
SELECT @sql = @sql + ' OR (''' + @TriggerType + ''' = ''D'')'
SELECT @sql = @sql + ' OR (''' + @TriggerType + ''' = ''U'' AND '
SELECT @sql = @sql + '((i.' + @fieldname + ' <> d.' + @fieldname + ')'
SELECT @sql = @sql + ' OR (''' + @fieldname + ''' in (Select colName from #primaryKeyFields))'
SELECT @sql = @sql + ' OR (i.' + @fieldname + ' IS NULL AND d.' + @fieldname + ' is NOT null)'
SELECT @sql = @sql + ' OR (i.' + @fieldname + ' IS NOT NULL AND d.' + @fieldname + ' is null)))'
EXEC (@sql)
set @RowId = @RowId + 1
END
View 5 Replies
View Related
Mar 28, 2008
I have a series of tasks that end up with two record sets that are unrelated which I would like to join. The first record set contains a list of expense accounts and the second record set contains a list of offices. I would like to create a join between the two sets where the resulting record set is a list of every office having every expense account.
If the data were in tables i'd create a sql statement something like this
Select t1.Account, t2.Office
from Table1 t1
Full Outer Join Table2 t2
on 1 = 1
That would give me the results I'm looking for however I can't find how to do this when these data sets are from the results of two different flows of data flow tasks.
Any ideas?
Thanks
Bill Webster
View 4 Replies
View Related
Jul 29, 2007
Hi, I would appreciate help on the following query I got stuck with;
I've got 2 tables "AccountList" and "PeriodBalance";
I need to return all the accounts form "AccountList" and their balance form "PeriodBalance";
The user will select a period for which the balances should be returned, if there is no balance for the period... 0 should be returned. "PeriodBalance" would not have an entry if there is no balance for the period.
There is only 3 possible periods (1, 2 or 3)
And there is only one balance per period per account;
TABEL 1: AccountList:
AccNo
100
200
300
TABLE 2: PeriodBalance:
AccNo PerID PerBal
100 1 1000
100 2 1750
100 3 1800
300 1 3200
300 3 3500
This is what is what I need returned by the query, assuming we are selecting PerID 2: (WHERE PerID=2)
AccNo PerID PerBal
100 2 1750
200 2 0
300 2 0
I've included ISNULL(PerID,4) = 4 in my WHERE clause...this worked for returning AccNo 200.
My problem is AccNo 300 in not NULL....it has values, just not for the selected PerID...so how do I get AccNo 300 to be included in my Query result?
This is my SQL Query:
SELECT AccountList.AccNo, PeriodBalance.PerID, PeriodBalance.PerBal
FROM AccountList LEFT OUTER JOIN
PeriodBalance ON AccountList.AccNo = PeriodBalance.AccNo
WHERE PeriodBalance.PerID = 2 OR ISNULL(PerID,4) = 4
Thanks in advance;
View 4 Replies
View Related
Jan 20, 2005
This is what I want my results to look like
invoice wbs1 wbs2 amount
8060 000-333 500 0
8060 000-333 100 0
8060 000-333 140 0
8060 000-333 150 4335
8060 000-333 160 0
8267 000-333 500 0
8267 000-333 100 20500
8267 000-333 140 547.50
8267 000-333 150 2000
8267 000-333 160 5000
This is what I have so far:
SELECT PR.WBS1, PR.WBS2, ledgerar.invoice, SUM(CASE WHEN ledgerar.transtype = 'in' AND ledgerar.period = '200405' THEN ledgerar.amount * - 1 END) AS amount
FROM PR LEFT OUTER JOIN
LedgerAR ON LedgerAR.WBS1 = PR.WBS1 AND LedgerAR.WBS2 = PR.WBS2 AND LedgerAR.WBS3 = PR.WBS3
WHERE (LedgerAR.wbs1 = '001-333') AND ledgerar.period = '200405'
GROUP BY pr.wbs1, pr.wbs2, ledgerar.invoice
the above query gives me the following results:
invoice wbs1 wbs2 amount
8060 000-333 100 0
8060 000-333 140 0
8060 000-333 160 4335
8267 000-333 100 1320
8267 000-333 140 20912.5
8267 000-333 150 8363
8267 000-333 160 2650
But I don't know how to get the query to display all of wbs2 whether it is null or not. So, for each invoice number there should be five records according to the wbs2 codes (500, 100, 140, 150, 160)
Does anyone know how to do this?
Thanks,
Laura
View 1 Replies
View Related
Dec 31, 2007
Need some join help...
Table 1 - Modules:
ID | Name
1 | A
2 | B
3 | C
Table 2 - CompanyModules
ModuleID | CompanyID
1 | 1
2 | 1
3 | 1
1 | 2
I'd like to return the following result set:
CompanyModules.CompanyID | Modules.Name | Present
1 | A | True
1 | B | True
1 | C | True
2 | A | True
2 | B | False
2 | C | False
What would be the query for this? Thanks.
Edit: This is the query I have tried:
select CompanyModules.CompanyID, Modules.Name, count(Modules.ID) as Present from
CompanyModules RIGHT outer Join Modules on CompanyModules.ModuleID = Modules.ID
group By CompanyModules.CompanyID, Modules.Name
Order by CompanyID
However, it only returns a partial result set:
CompanyModules.CompanyID | Modules.Name | Present
1 | A | 1
1 | B | 1
1 | C | 1
2 | A | 1
View 1 Replies
View Related
Mar 30, 2015
In Outer join, I would like to add the outer columns that don't exist in the right table for each order number. So currently the columns that don't exist in the right table only appear once for the entire set. How can I go about adding PCity, PState to each order group, so that PCity and PState would be added as null rows to each group of orders?
if OBJECT_ID('tempdb..#left_table') is not null
drop table #left_table;
if OBJECT_ID('tempdb..#right_table') is not null
drop table #right_table;
create table #left_table
[Code]....
View 2 Replies
View Related
Apr 26, 2002
Take the following scenario:
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
insert child values (1, 'AAA')
insert child values (1, 'BBB')
insert child values (1, 'CCC')
insert child values (2, 'AAA')
insert child values (4, 'AAA')
insert child values (4, 'DEF')
insert child values (4, 'AAA')
insert child values (4, 'BBB')
insert child values (5, 'AAA')
insert child values (5, 'AAA')
insert child values (6, 'AAA')
insert child values (7, 'AAA')
insert child values (7, 'BBB')
insert child values (7, 'CCC')
insert child values (7, 'DDD')
insert child values (10, 'AAA')
insert child values (10, 'CCC')
go
select p.p_id, count(c.p_id) as num_rows
from parent p, child c
where p.p_id *= c.p_id
and c.c_type in ('AAA', 'BBB')
group by p.p_id
select p.p_id, count(c.p_id) as num_rows
from parent p left outer join child c on p.p_id = c.p_id
where c.c_type in ('AAA', 'BBB')
group by p.p_id
=========================================
Results:
(T-SQL *= outer join)
p_id num_rows
----------- -----------
1 2
2 1
3 0
4 3
5 2
6 1
7 2
8 0
9 0
10 1
(SQL-92 outer join)
Warning: Null value eliminated from aggregate.
p_id num_rows
----------- -----------
1 2
2 1
4 3
5 2
6 1
7 2
10 1
View 1 Replies
View Related
Jan 26, 2008
I have 4 tables (SqlServer2000/2005). In the select query, I have FULL JOINED all the four tables A,B,C,D as I want all the data. The result is as sorted by DDATE desc:-
AID BID BNAME DDATE DAUTHOR
1 1 abcxyz 2008-01-20 23:42:21.610 c@d.com
1 1 abcxyz 2008-01-20 23:41:52.970 a@b.com
1 2 xyzabc 2008-01-21 00:17:14.360 c@d.com
1 2 xyzabc 2008-01-20 23:43:17.110 a@b.com
1 2 xyzabc 2008-01-20 23:42:43.937 a@b.com
1 2 xyzabc NULL NULL
2 3 pqrlmn NULL NULL
2 4 cdefgh NULL NULL
Now, I want unique rows from the above result set like :-
AID BID BNAME DDATE DAUTHOR
1 1 abcxyz 2008-01-20 23:42:21.610 c@d.com
1 2 xyzabc 2008-01-21 00:17:14.360 c@d.com
2 3 pqrlmn NULL NULL
2 4 cdefgh NULL NULL
I want to remove the duplicate rows and show only the unique rows but contains all the data from the first table A. I have to bind this result set to a nested GridView.
View 8 Replies
View Related
May 10, 2010
what is difference between outer apply and outer join ,both return rows from left input as well as right input . isnt it?
View 3 Replies
View Related
Apr 30, 2008
Hello
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
View 1 Replies
View Related
Nov 4, 2003
Hello
I've a table with these values:
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)
How can I get the second result set?
Thanks for your support.
View 3 Replies
View Related
Oct 8, 2015
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.
View 5 Replies
View Related
Aug 10, 2007
Scenario:
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?
View 1 Replies
View Related
Oct 15, 2007
[SELECT coalesce(a4.key,a3.key,a2.key,a.key) as parent_key,
a.key as child_key,
a.comp_name
FROM .......lots of JOINS..........]
that's what I have, and I need to find all instances where parent_key=child_key.
I've been trying something similiar to...
[SELECT name, count(name), name2, count(name2)
FROM people
GROUP BY name, name2
HAVING (count(name)>1 AND count(name2)>1)]
The problem is that I keep getting error messages because I can't use
the alias' or sum(coalesce(.....)).
I was hoping you guys might have a suggestion for me. Thanks ahead of time.
View 10 Replies
View Related
Aug 18, 2007
When I execute this, it works ok but only one the first character of the request.form["d"] is stored to the db.I checked the sproc with another routine and it adds full data, and I've verified that value of request.form["d"] is longer than one chaacter by printing it to the page. Anyone got any ideas why only the first char is getting added to the db?? SqlConnection SqlConnection1 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString);
SqlCommand SqlCommand1 = new SqlCommand("addRoute", SqlConnection1);
SqlCommand1.CommandType = CommandType.StoredProcedure;
SqlParameter SqlParameter1 = SqlCommand1.Parameters.Add("@ReturnValue", SqlDbType.Int);
SqlParameter1.Direction = ParameterDirection.ReturnValue;
SqlCommand1.Parameters.Add("@xmlData", Request.Form["d"]);
SqlConnection1.Open();
SqlCommand1.ExecuteNonQuery();
Response.Write(SqlCommand1.Parameters["@ReturnValue"].Value);
//Response.Write(Request.Form["d"]);
View 2 Replies
View Related
Mar 1, 2008
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,
View 10 Replies
View Related
Oct 10, 2007
Given 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.
View 7 Replies
View Related
Jun 12, 2015
I am using 2014 SQL Express. How I can get the rows By first column named agentname) in a table to be listed alphabetically?
View 6 Replies
View Related
Apr 16, 2008
Anyone know why using
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".
Any enlightenment is very appreciated.
Thanks
View 5 Replies
View Related
Mar 12, 2007
Has anyone tried using a TSQL full outer join and managed to get it working? Any documentation for Full Outer Joins in CE?
Any ideas? Thanks, Bill
View 1 Replies
View Related
Jun 1, 2004
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)
FROM
GridLocations, Grids
WHERE
Grids.PlayerID = 1
View 5 Replies
View Related
Jul 14, 2004
Hello there,
I have 2 tables:
Table: Leads
------------------
ID LDate ClientID
1 04/02/2004 101
2 04/03/2004 103
3 04/04/2004 104
....
Table: Tracking
------------------
ID TDate ClientID Shown Clicked
1 04/02/2004 101 3 2
2 04/03/2004 103 5 4
3 04/04/2004 101 3 9
....
I need a query to display results for any Client ID like this:
Date Leads Shown Clicked
=============================
04/02/2004 1 3 2
04/04/2004 0 3 9
.....
=============================
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
Thanks a lot for your time and help in advance.
View 3 Replies
View Related
Aug 10, 2006
Here's the lookup table, tblLookup:
Task
SubTask
Subset
Superset
Description
And here's the more voluminous table, tblRecords, to which I need to join that:
Task
SubTask
Acct_cat
Actual_Amount
Budgeted_Amount
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").
How do I match them up?
Thanks.
View 2 Replies
View Related
Aug 31, 2006
Hello,
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 anybody can help me, I would be very happy.
Sven
View 2 Replies
View Related
