I need to join on a (new for me) where condition.  In one table it is all in one field, in the second table it is split and I need to combine it.  This syntax just gives me the generic, 'incorrect syntax near the keyword 'on'' which is not very helpful to me.  What do I need to do to get this as a functioning query?

Select pi.fullname, si.address, si.phone
From saleinfo si
Inner join personelinfo pi
ON si.[FirstName]+' '+si.[LastName] = pi.[fullname]'

The only error I can see is the single quote at the end of the line, is that a typo here or typo in your actual query?

• Edited by RyanAB 4 hours 18 minutes ago

I need to join on a (new for me) where condition.  In one table it is all in one field, in the second table it is split and I need to combine it.  This syntax just gives me the generic, 'incorrect syntax near the keyword 'on'' which is not very helpful to me.  What do I need to do to get this as a functioning query?

Select pi.fullname, si.address, si.phone
From saleinfo si
Inner join personelinfo pi
ON si.[FirstName]+' '+si.[LastName] = pi.[fullname]'

Assuming this is correct, do you have a setting on that allows for schema information hiding?

It is best practice in SQL to list the master database beforehand in what is called the BUS principle. Namely, in case you get hit by a bus, another developer should be able to come along and know what they are looking at. Therefore, in your scripts you should script out a USE <database> at the top of your query.

Part of the reason also is that are you sure that the master database you are calling the tables from knows that concatenating the columns together will match the same result for the right table column you are joining? So you should be doing a little data-mining to ensure the integrity of the data now and in the future to match.

Without really knowing what the data looks like, I can't be very specific, but definitely as RyanAB says get rid of that hyphen ( ' ) at the end.

USE <databasename>

GO

Select pi.fullname, si.address, si.phone
From dbo.saleinfo si
Inner join dbo.personelinfo pi ON si.[personID] + '' + [LastName] = pi.[fullname]

Note, the reason for verifying your data and the datasets you are using is because what if there was a discrepancy in the saleinfo data? Worse, what if you are referencing a table from another database or different schema than the master (dbo is the default un-named SCHEMA) Then the entire script will either not run as you currently have or it will return an empty set!

Hope this steers you in the right direction.

• Edited by Conquistador0 2 hours 45 minutes ago

Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI-ISO standards in your data. You are not even close. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data. We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI-ISO Standard SQL. 

[quote] I need to join on a (new for me) WHERE condition. In one table it is all in one field sic: columsn are not fields, in the second table it is split and I need to combine it. [/quote]

The short answer is a missing quote mark. The right answer is that you need to make the schema consistent. Back in the old mag tape and punch card days, we had no concept of a data model. Each island of data was independent and there were no data dictionaries. 

Decide if you want (first_name, last_name) as an atomic, but non-scalar value or full_name and fix the data. 

Good day,

Your query include small error (extra ' in the end) and without more information we can only guess what you meant. So firstly let's guess. Please check if this is what you are looking for:

Select pi.fullname, si.address, si.phone
From saleinfo si
Inner join personelinfo pi ON si.[FirstName] + ' ' +si.[LastName] = pi.[fullname]

No that you have a working query, if this query do not fit your needs then please provide us more information. We need to understand the table structure and get some sample data. Please post queries to create the relevant tables and queries to insert some sample data to each table (by the way, these queries named DDL

Here are different ways by which you can do the same, including the one which you have mentioned

DECLARE @Person TABLE
(
 First_Name VARCHAR(20),
 Last_Name VARCHAR(20)
)
INSERT INTO @Person VALUES ('Abc','pqr'),(NULL,'pqr'),(NULL,'xyz'),('abc',NULL), ('xyz',NULL) , (NULL,NULL), ('ab','pqr')

DECLARE @SearchItem VARCHAR(20) = 'Abc pqr'

-- METHOD 1 

SELECT * FROM @Person 
WHERE First_Name +' ' + Last_Name = @SearchItem

-- METHOD 2 

SELECT * FROM @Person
WHERE CONCAT(First_Name,' ', Last_Name) = @SearchItem

-- METHOD 3

; WITH CTE AS
( SELECT CONCAT(First_Name,' ', Last_Name) AS 'Full Name' FROM @Person)
SELECT * FROM CTE WHERE [Full Name] = @SearchItem

Coming to your original query, it has a syntax error in the end which, I believe, is beautifully explained by other community members.

hope this will help