Help Typical Error Inner Join
Apr 25, 2004
hi, all
i am having what is considered to be typical error, i have two tables in sql2000 database
(options) orderid, productid, productname,optionvalue
(orderitems) orderid, quantity, unitprice,productcode,productid
I'd like to join these 2 tables and be able to display in a repeater. the problem is that i keep getting some duplicates with sql statement
"SELECT DISTINCT
options.orderid,options.productname,options.optionvalue,orderitems.quantity,orderitems.productcode
FROM options INNER JOIN on orderitems on
options.[orderid] = orderitems.[orderid] "
PLEEEASE HELP ME SLEEP AT NIGHT:)
thanks
View 1 Replies
ADVERTISEMENT
Aug 8, 2006
I'm not sure which forum to post this problem in, since it almost seems like a network issue with our SQL Server. We have SQL Server 2000 SP4 (8.00.2039) installed on our enterprise database server. I've been using ADO (through VB, VBA, VBScript, C++) to connect to it without any problems. INSERT's, UPDATE's, SELECT's, no problems whatsoever... EXCEPT: After performing a number of repetative statements, my ADO connection object will error with the dreaded "SQL Server does not exist or access denied" message.
By repetative statements, I mean something like this:
cn.Open "..."
Set rs = cn.Execute(...)
Do Until rs.EOF
cn.Execute "Insert into ..."
rs.MoveNext
Loop
I may be doing 1,000 inserts within the loop, sometimes more. Without fail, though, my program will error after a certain number. I display a progress indicator showing which record (i.e., "10 of 1,000") I'm currently processing and it seems to error at the same number everytime. For example, it may get to record 564 before displaying the error. If I end the program and run it again, it gets to about the same spot (often it's EXACTLY the same spot) and errors again. So, it's almost like a timeout condition.
I know why the error occurs -- my machine can no longer "see" the SQL server. I have tried pinging the server after I get this message and it's unreachable. If I wait five minutes, though, pinging works and my program continues to run. I have coded around it (if error, wait, and try again) but it's very annoying.
Does this sound like a problem with our SQL Server, Network, ADO, my workstation, or a combination of sorts?
View 1 Replies
View Related
Jul 23, 2005
I tried to implement triggers for filling audit-trail table on this way.Everything works fine as long as I don't update the primary key field value.When I try to update PK value, an error occures.The code is the following:CREATE TRIGGER NameOfTheTriggerON dbo.TableName FOR DELETE, INSERT, UPDATEAS BEGINdeclare@type varchar(10) ,@UpdateDate datetime ,@UserName varchar(128)if exists (select * from inserted) and exists (select * from deleted)select @type = 'UPDATE'else if exists (select * from inserted)select @type = 'INSERT'elseselect @type = 'DELETE'select @UpdateDate = getdate() ,@UserName = system_user/* this code is repeting for every field in the table*/if update (TableName) or @type = 'DELETE'insert dbo.AUDIT_TRAIL (TableName, FieldName, OldValue, NewValue,UpdateDate, UserName, type)select 'TableName', convert(varchar(20), 'FieldName'),convert(varchar(1000),d.FieldName), convert(varchar(1000),i.FieldName),@UpdateDate, @UserName, @typefrom inserted ifull outer join deleted don i.PrimaryKeyFieldName = d.PrimaryKeyFieldNamewhere (i.FieldName<> d.FieldName or (i.FieldName is null and d.FieldName isnot null) or (i.FieldName is not null and d.FieldName is null))ENDHow to slve the problem with updated (changed) primary key values?What is the typical code for audit-trail triggers?Thanks.
View 2 Replies
View Related
Sep 30, 2006
I need to import typically 5000 - 50000 rows of data to OLTP database from a flat file. Flat file contains all active information - new & existing rows. About 10% of the rows are new. Each row has about 30 columns.
I have created a Data Flow which divides the data to new records (for insert) and existing records(for update) via the Lookup transformation (as described in Jaime Thompson blog - thanks).
The question is - in performance perspective - should I check if the existing records have been changed or not?
Currently I do a full refresh of all data - (my opinion is that in SQL side - if you update a row with exact same data - it will not cause SQL server to "perform row update") - however I'm concerned that SSIS performing between 4000 - 40000 update statements each time in OLE DB command may cause issues.
Your thoughts on this appreciated
View 7 Replies
View Related
Aug 22, 2007
In a large organization who would typically be tasked with support ing SSIS - the database team, an application team, etc... SSIS is new to our Organization, we are attempting to put it into the correct organization. We current have SSRS that is supported by an application team, the SS DB is supported by the DB team.
Thanks
View 4 Replies
View Related
Jul 31, 2007
I have been working on a project to evaluate transactional replication in SQL Server 2005 to find out if it will be suitable to provide real time reporting without impacting the primary database server. Thus far I have not seen latency better than several seconds replicating a change to a table or by utilizing the "tracer tokens" troubleshooting functionality. It usually takes a couple seconds from the publisher to the distributor and a couple more from the distributor to the subscriber.
I guess my question is actually whether it is possible to achieve sub-second (milliseconds) latency for transactional replication under any circumstances. Someone on another forum told me it was not, so I thought I would ask a separate community to confirm. Regarding the hardware, the latency on ping is less than 1ms between the servers and the servers themselves are decently powered quad processor boxes w/ 4GB of ram each. It seems to have the same latency of 3-5 seconds regardless of whether I publish a single table or all of the tables in the database. I currently have it configured with the publisher on one server and the distributor/subscriber on the second with the log reader and distributor agents set to run continuously. If it is possible, please recommend configuration of the agents or the topology of the PDS to achieve this. Thanks in advance for any insight you provide.
Dan
View 2 Replies
View Related
Sep 7, 2007
Nothing difficult, I just need a way to generate a new datetime column based on the column [PostedDate], datetime. So basically I want to truncate the time. Thanks a lot.
View 5 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
Jan 23, 2007
The following query gives a list of users who have completed 0 modules. Code below:
SELECTpps_principals.name AS pname,
COUNT(PPS_SCOS.SCO_ID) AS coursecount
FROMPPS_PRINCIPALS
LEFT JOINPPS_TRANSCRIPTS ON PPS_TRANSCRIPTS.PRINCIPAL_ID = PPS_PRINCIPALS.PRINCIPAL_ID
AND PPS_TRANSCRIPTS.STATUS like '[PCF]'
AND PPS_TRANSCRIPTS.TICKET not like 'l-%'
AND pps_transcripts.date_created between '2006-10-01' and '2007-09-30'
LEFT JOINPPS_SCOS ON PPS_SCOS.SCO_ID = PPS_TRANSCRIPTS.SCO_ID
AND pps_scos.name like 'MT%'
WHEREpps_principals.login like '%score%'
AND dbo.PPS_PRINCIPALS.DISABLED IS NULL
GROUP BYPPS_PRINCIPALS.NAME
HAVINGCOUNT(PPS_SCOS.SCO_ID) = 0
ORDER BYpps_principals.name asc
This query works fine.
I want to be able to do another join to a table called EXT_USER_GROUPS to the query. This ties in the names to a group based on location. I have added this (see code below):
SELECTpps_principals.name AS pname, EXT_USER_GROUPS.LOGIN,, EXT_USER_GROUPS.NAME,
COUNT(PPS_SCOS.SCO_ID) AS coursecount
FROMPPS_PRINCIPALS
LEFT JOINPPS_TRANSCRIPTS ON PPS_TRANSCRIPTS.PRINCIPAL_ID = PPS_PRINCIPALS.PRINCIPAL_ID
AND PPS_TRANSCRIPTS.STATUS like '[PCF]'
AND PPS_TRANSCRIPTS.TICKET not like 'l-%'
AND pps_transcripts.date_created between '2006-10-01' and '2007-09-30'
LEFT JOINPPS_SCOS ON PPS_SCOS.SCO_ID = PPS_TRANSCRIPTS.SCO_ID
LEFT JOINEXT_USER_GROUPS ON EXT_USER_GROUPS.LOGIN = PPS_PRINCIPALS.LOGIN
AND pps_scos.name like 'MT%'
WHEREpps_principals.login like '%TEST%'
AND dbo.PPS_PRINCIPALS.DISABLED IS NULL
GROUP BYPPS_PRINCIPALS.NAME
HAVINGCOUNT(PPS_SCOS.SCO_ID) = 0
ORDER BYpps_principals.name asc
With this I get the following error:
Server: Msg 8120, Level 16, State 1, Line 1
Column 'EXT_USER_GROUPS.LOGIN' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Any help appreciated.
View 2 Replies
View Related
Jun 10, 2008
I am getting the error message:
Msg 156, Level 15, State 1, Line 62
Incorrect syntax near the keyword 'inner'.
Msg 102, Level 15, State 1, Line 62
Incorrect syntax near 'Code'.
When I attempt to run the following query:
select NewUsed, VehicleYear, VehicleMake, VehicleModel, VehicleTrim,
AppZip, ltrim(newtrademake) AS TradeMake, ltrim(trademodel)TradeModel,
TradeYear, NcodeL AS TradeNcodeL, NcodeM AS TradeNcodeM, OwingOnTrade, TradeAllowance, NetTradeIn
from
(select *,
Case
When TradeMake = ' Ford' and (Trademodel like '%Aerostar%' or Trademodel like '%Bronco%')
Then 'FORD TRUCKS'
Else ltrim(TradeMake)
End
AS NewTradeMake
from dbo.vw_dds)
inner join (select distinct make, model, ncodel, ncodem, modelyear from us.dbo.algmaster) Code
on code.make = ltrim(newtrademake) and code.model= ltrim(trademodel) and code.modelyear = tradeyear
The error is related to the inner join but I can't seem to figure out what I am doing wrong.
Thank you for your help,
Tasha
View 5 Replies
View Related
Dec 1, 2006
OK I have tried just about every combination over the past 3 days to get this working and have gotten close but just can't seem to get past this.
Truncate Table [SecurityEvents_Temp]
Insert Into SecurityEvents_Temp (
[DepartmentNumber],
[EventLog],
[RecordNumber],
[TimeGenerated],
[TimeWritten],
[EventID],
[EventType],
[EventTypeName],
[EventCategory],
[EventCategoryName],
[SourceName],
[Strings],
[ComputerName],
[SID],
[Message],
[Data],
[UserName],
[DomainName]
)
Select
[DepartmentNumber] = '001',
[UserName] = (Select [Value] From [dbo].[fn_Split]([Strings],'|') where idx = 3),
[DomainName] = (Select [Value] From [dbo].[fn_Split]([Strings],'|') where idx = 4),
[Events].*
from [Events]
join EventstoLog ON dbo.Events.EventID = dbo.EventsToLog.EventID
quote:Msg 257, Level 16, State 3, Line 3
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.
The Select with the Join at the bottom work great by itself.
The whole script without the Join works.
But add the Join to the Insert Into script and it fails.
Can someone look at this and point me in the right direction??
Also, below is most of the table strustures just in case.
USE [EventLogs]
GO
/****** Object: Table [dbo].[Events] Script Date: 12/01/2006 18:15:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Events](
[EventLog] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RecordNumber] [int] NULL,
[TimeGenerated] [datetime] NULL,
[TimeWritten] [datetime] NULL,
[EventID] [int] NULL,
[EventType] [int] NULL,
[EventTypeName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EventCategory] [int] NULL,
[EventCategoryName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SourceName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Strings] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ComputerName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SID] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Message] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Data] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
USE [EventLogs]
GO
/****** Object: Table [dbo].[SecurityEvents_Temp] Script Date: 12/01/2006 18:15:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SecurityEvents_Temp](
[DepartmentNumber] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[UserName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DomainName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EventLog] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RecordNumber] [int] NULL,
[TimeGenerated] [datetime] NULL,
[TimeWritten] [datetime] NULL,
[EventID] [int] NULL,
[EventType] [int] NULL,
[EventTypeName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EventCategory] [int] NULL,
[EventCategoryName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SourceName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Strings] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ComputerName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SID] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Message] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Data] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
USE [EventLogs]
GO
/****** Object: Table [dbo].[EventsToLog] Script Date: 12/01/2006 18:16:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EventsToLog](
[EventID] [int] NULL,
[EventDescription] [char](120) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Thank You,
John Fuhrman
http://www.titangs.com
View 2 Replies
View Related
Apr 27, 2007
I am trying to do a left join from one table a to table B on a column CEO_ID which is of datatype varchar(5) in table A and of datatye nvarchar(20) in table b.The resultset gives me all the rows that are in both the tables except a few..So when I looked at the records that are missing, it is stored as 00392 in table A and 392 in table b and that is the reason it is not showing that record in the result.How can I show these records?
Query
Select p.CEO_ID,p.Part_ID,ct.CEO_ID__c
from
Contact ct
left join
Main5.dbo.Part p
on
p.CEO_ID = Convert(varchar(5),ct.CEO_ID__c) collate database_default
where
ct.CEO_ID__c = 392
ResultSet
CEO_ID CEO_ID__c
------ ---------
NULL 392
Please help me !
View 2 Replies
View Related
Mar 10, 2008
HelloI am seeing the following syntax error: Error on Primary Select: Line 4: Incorrect syntax near '='.When I try to join two tables see my code below:------------------------------------------------------------------------------------------------------------------------ SELECT a.ProdName as [Product Name], b.price,b.link FROMProd_CatA a LEFT JOIN (SELECT ProdName, Price, '<a href="' + url + '">' + Title + '</a>' as Link FROM ProdNames WHERE = url like 'http:%' ) b ON a.ProdName=b.ProdNameWhere a.Red = 'yes'ORDER BY a.ProdName ASC
------------------------------------------------------------------------------------------------------------------------------ I unable to find a solution. I would be grateful if somebody could please advise.ThanksLynn
View 3 Replies
View Related
Jul 11, 2005
I want to insert into a table the result of a select which contains a join. Is this possible in any way on mysql 3.23.58?
I tryed the following code (in both ansi 92 and non ansi 92 forms)
insert book_edition (ISBN, book_title, publisher, book_image, author_name)
select i.isbn, i.imageurl, i.author, i.title, i.publisher
from ImportDataUniqueISBN i
left outer join book_edition b
on i.isbn = b.ISBN
where b.ISBN is null
I get the following error:
INSERT TABLE 'book_edition' isn't allowed in FROM table list
If executed without the join statement it works well.
Tks!
View 5 Replies
View Related
Feb 6, 2015
Trying to run a query to get total number of direct mail campaigns by company. Would like the totals to be (1) total number of direct mail campaigns (there are 2 types) (2) total number of direct mail campaigns type direct mail (3) total number of direct mail campaigns type publication
Below is the query I put together with INNER JOIN:
SELECT Agency.PrimaryAgencyName, COUNT(Distinct Campaigns.DirectMailID) AS TotalCampaigns, COUNT(Distinct DirectMail.Type) AS TotalDirectMail
FROM Agency
INNER JOIN (Clients
INNER JOIN (Campaigns
INNER JOIN DirectMail
ON DirectMail.DirectMailID = Campaigns.DirectMailID)
ON Clients.ClientID = Campaigns.CampaignID)
ON Agency.AgencyID = Clients.AgencyID
GROUP BY Agency.PrimaryAgencyName, DirectMail.Month, DirectMail.Type
ORDER BY Agency.PrimaryAgencyName
When I run the query, I recent the below error:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value '1683 1-15017' to data type int.
The value mentioned in the above error message is the ClientID.
Below are the tables for reference (bold for entity name and underline for PK) and datatypes
Agency
AgencyID - nvarchar(255)
PrimaryAgencyName - nvarchar(255)
Campaigns
CampaignID- int
ClientID- nvarchar(255)
DirectMailID- nvarchar(255)
Clients
ClientID - nvarchar(255)
AgencyID - nvarchar(255)
DirectMail
DirectMailID - nvarchar(255)
Campaign - nvarchar(255)
Type - nvarchar(255)
Month - float
View 1 Replies
View Related
Apr 12, 2007
Hi!
I've a big problem by using the following query :
Code Snippet
public SqlCeResultSet selectRSQuery(String query)
{
SqlCeResultSet resultSet = initializeCommand(query).ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable);
return resultSet;
}
SqlCeResultSet resultSet = sgb.selectRSQuery(
"SELECT p.pId, p.pLogin FROM Profiles p, ProfilesGroups pg, Groups g " +
"WHERE g.gId = pg.tpGroupId " +
"AND p.pId = pg.tpProfileId " +
"AND g.gProfileID = '" + app.Settings.Default.id + "'");
It return me this error :
Cannot generate an updatable cursor for the query because there is a non-standard join expression.
What can I do??
Thxx
View 9 Replies
View Related
Jun 15, 2006
Why would this error? The join works in Query Analyzer.
GridViewShowA.Visible = true;String objConnection = ConfigurationManager.ConnectionStrings["MyConnection"].ToString();String strSQL = "SELECT x.office as Office, x.email as Email, z.fname as 'First Name', z.lname as 'Last Name', ";strSQL += "z.title as Title ";strSQL += "FROM db1.dbo.tblA X ";strSQL += "JOIN db2.dbo.tblA Z ";strSQL += " ON x.email = z.email";strSQL += "WHERE x.office = '" + DropDownListoffice.SelectedValue.ToString() + "' ";strSQL += "AND z.email = x.email";SqlDataAdapter objAdapter = new SqlDataAdapter(strSQL, objConnection);DataSet dataSet = new DataSet();objAdapter.Fill(dataSet, "myData");DataTable dataTable = dataSet.Tables[0];GridViewShowA.DataSource = dataTable.DefaultView;GridViewShowA.DataBind();
error:
Line 1: Incorrect syntax near 'x'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'x'.Source Error: Line 101: objAdapter.Fill(dataSet, "myData");
View 1 Replies
View Related
Sep 22, 2014
I am trying to pull in columns from multiple tables but am getting an error when I run the code:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "a.BOC" could not be bound.
I am guessing that my syntax is completely off.
SELECT
b.[PBCat]
,c.[VISN] --- I am trying to pull in the Column [VISN] from the Table [DIMSTA]. Current Status: --Failure
,a.[Station]
,a.[Facility]
,a.[CC]
,a.[Office]
[Code] ....
View 2 Replies
View Related
May 8, 2008
I have been working on this for a little bit and have gotten to this point. Below is the query in blue, the error in red. Now, from what I gather the error is telling me I can't insert a duplicate product code into tblProduct. Isn't that what the left join is exactly not doing, as in, the left join is inserting all records from Complete_records into tblProduct where the code is null(does not exist) in tblProduct? Or, is this an issue where the identity number, productID in tblProduct, isn't starting at the next number in turn?
Thanks.
Set Identity_Insert tblProduct on
DECLARE @MaxId int
SELECT @MaxID=MAX(productID)
FROM tblProduct
SELECT IDENTITY(int,1,1) AS ID,Complete_products.APNum, Complete_products.Title, Complete_products.CategoryID, Complete_products.Mountable, Complete_products.price,
Complete_products.Height, Complete_products.Width, Complete_products.IRank, Complete_products.frameable, Complete_products.Typ INTO #Temp
FROM Complete_products LEFT OUTER JOIN
tblProduct AS tblProduct_1 ON Complete_products.APNum = tblProduct_1.productCode
where tblProduct_1.productCode IS NULL
INSERT INTO tblProduct
(productID,productCode, productName, productNavID, CanBeMounted, productRetailPrice, productHeight, productWidth, Rank, CanBeFramed, ProductType)
SELECT @MaxID + ID, APNum, Title, CategoryID, Mountable, price,
Height, Width, IRank, frameable, Typ
FROM #Temp
(236752 row(s) affected)
Msg 2601, Level 14, State 1, Line 13
Cannot insert duplicate key row in object 'dbo.tblProduct' with unique index 'IX_tblProductt_productCode'.
The statement has been terminated.
View 18 Replies
View Related
Jul 22, 2014
I'm writing a query where I have multiple left-outer joins but I keep getting multi-part identifier error. See the query below?
SELECT gl.seg5 Natural
,gl.seg2 Office
,gl.seg3 Dept
,gl.seg4 Team
,gl.seg6 Sub
,gl.seg7 Tkpr
,gl.seg1 Comp
,'CHK' Source
[Code] ....
Errors
Msg 4104, Level 16, State 1, Line 68
The multi-part identifier "csddt.baid" could not be bound.
Msg 4104, Level 16, State 1, Line 68
The multi-part identifier "csddt.cknum" could not be bound.
Msg 4104, Level 16, State 1, Line 68
The multi-part identifier "csddt.ckline" could not be bound.
View 5 Replies
View Related
May 13, 2015
I have a few tables I am trying to join to create a report. Everything was working fine until I tried to add an aggregate Sum function to a column (MaxCap) in table ctfBarn.
select
x.*, y.division, y.department, y.location
,(right(z.SvcMgrName,len(z.SvcMgrName)-len(left(z.SvcMgrName,CHARINDEX(', ',z.SvcMgrName)-1))-2)+' '+
left(z.SvcMgrName,CHARINDEX(', ',z.SvcMgrName)-1))AS SvcMgrName
,(right(z.SrSvcName,len(z.SrSvcName)-len(left(z.SrSvcName,CHARINDEX(', ',z.SrSvcName)-1))-2)+' '+
[Code] .....
I think I probable need to include a group by but can't figure out the correct syntax.
View 12 Replies
View Related
May 22, 2006
Hi all,
I got an error message 156, when I executed the following code:
////--SQLQueryParent&Child.sql---////////
Use newDB
GO
----Creating dbo.Person as a Parent Table----
CREATE TABLE dbo.Person
(PersonID int PRIMARY KEY NOT NULL,
FirstName varchar(25) NOT NULL,
LastName varchar(25) NOT NULL,
City varchar(25) NOT NULL,
State varchar(25) NOT NULL,
Phone varchar(25) NOT NULL)
INSERT dbo.Person (PersonID, FirstName, LastName, City, State, Phone)
SELECT 1, "George", "Washington", "Washington", "DC", "1-000-1234567"
UNION ALL
SELECT 2, "Abe", "Lincoln", "Chicago", "IL", "1-111-2223333"
UNION ALL
SELECT 3, "Thomas", "Jefferson", "Charlottesville", "VA", "1-222-4445555"
GO
----Creating dbo.Book as a Child table----
CREATE TABLE dbo.Book
(BookID int PRIMARY KEY NOT NULL,
BookTitle varchar(25) NOT NULL,
AuthorID int FOREIGN KEY NOT NULL)
INSERT dbo.Book (BookID, BookTitle, AuthorID)
SELECT 1, "How to Chop a Cherry Tree", 1
UNION ALL
SELECT 2, "Valley Forge Snow Angels", 1
UNION ALL
SELECT 3, "Marsha and ME", 1
UNION ALL
SELECT 4, "Summer Job Surveying Viginia", 1
UNION ALL
SELECT 5, "Log Chopping in Illinois", 2
UNION ALL
SELECT 6, "Registry of Visitors to the White House", 2
UNION ALL
SELECT 7, "My Favorite Inventions", 3
UNION ALL
SELECT 8, "More Favorite Inventions", 3
UNION ALL
SELECT 9, "Inventions for Which the World is Not Ready", 3
UNION ALL
SELECT 10, "The Path to the White House", 2
UNION ALL
SELECT 11, "Why I Do not Believe in Polls", 2
UNION ALL
SELECT 12, "Doing the Right Thing is Hard", 2
GO
---Try to obtain the LEFT OUTER JOIN Results for the Parent-Child Table
SELECT * FROM Person AS I LEFT OUTER JOIN Book ON I.ID=P.ID
GO
////---Results---//////
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'NOT'.
////////////////////////////////////////////////////
(1) Where did I do wrong and cause the Error Message 156?
(2) I try to get a Parent-Child table by using the LEFT OUTER JOIN via the following code statement:
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'NOT'.
Can I get a Parent-Child table after the error 156 is resolved?
Please help and advise.
Thanks,
Scott Chang
View 9 Replies
View Related
May 22, 2015
I have two xml source and i need only left restricted data.
how can i perform left restricted join?
View 2 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
Dec 23, 2014
I 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.
View 9 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
Apr 14, 2015
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.
View 2 Replies
View Related
Aug 9, 2013
Why would I use a left join instead of a inner join when the columns entered within the SELECT command determine what is displayed from the query results?
View 4 Replies
View Related
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
Jul 25, 2007
I'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
View 1 Replies
View Related
Apr 24, 2007
Awesome! 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
View 2 Replies
View Related
Dec 24, 2013
We are trying to migrate from sql 2005 to 2012. I am changing one of the implicit join to explicit join. As soon as I change the join, the number of rows returned are fewer than before.
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.
View 7 Replies
View Related
Feb 5, 2015
Why 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]
View 3 Replies
View Related
