May 27, 2014 at 2:02 pm
Dear All
I have two similar tables in different database and need to make query to select only the data from the first table that is not available in the second table and there is no unique record for each record , but each row is having different records for example:
CREATE TABLE table1(
[PNR] [nvarchar](10) NOT NULL,
[Tkt_Number] [nvarchar](10) NOT NULL,
[DepaCityName] [nvarchar](50) NULL,
[ArriCityCode] [nvarchar](3) NULL,
[name][nvarchar] (40) not null
)
INSERT INTO table1
VALUES ('fnzer',10229,'sah','ade','jason');
('fnzer',10224,'sah','ade','mike');
('fdfdd',12329,'tai','ade','karim')
second table
CREATE TABLE table2(
[PNR] [nvarchar](10) NOT NULL,
[Tkt_Number] [nvarchar](10) NOT NULL,
[DepaCityName] [nvarchar](50) NULL,
[ArriCityCode] [nvarchar](3) NULL,
[name][nvarchar] (40) not null
)
INSERT INTO table2
VALUES ('fnzer',10229,'sah','ade','jason');
('fnzer',10224,'sah','ade','mike');
('fdfdd',12329,'tai','ade','karim');
('fdf44',12669,'Sah','DXB','lamees')
)
output should be like this
PNR | TKT_NUMBER | DEPACITYNAME | ARRCITYCODE | NAME
fdf44 12669 Sah DXB lamees
THANK YOU
REGARDS
Rashed
May 27, 2014 at 2:11 pm
select only the data from the first table that is not available in the second table
SELECT A.*
FROM A LEFT JOIN B ON
(several joins - on the columns that match)
WHERE B.ForeignKey IS NULL
May 27, 2014 at 2:14 pm
Excellent job posting ddl and sample data. Your description is good too. Thanks!!! This makes it a lot easier to work on your issue.
Your description of desired output and the sample output appear to be backwards from each other though. In your sample you show rows from table2 that are not in table1.
You could do something like this.
select t2.*
from table2 t2
left join table1 t1 on t2.PNR = t1.PNR
and t2.Tkt_Number = t1.Tkt_Number
and t2.DepaCityName = t1.DepaCityName
and t2.ArriCityCode = t1.ArriCityCode
and t2.name = t1.name
where t1.PNR is null
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 27, 2014 at 2:15 pm
You could also use EXCEPT for this.
select *
from table2
EXCEPT
select *
from table1
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 27, 2014 at 3:14 pm
Dear friend
Thank you for your quick response, i tried that , but two points still need your assistant with is those two tables are from two different databases , secondly they have different collations so i get this error message:
"Cannot resolve the collation conflict between "Arabic_CI_AS" and "Arabic_CI_AI" in the EXCEPT operation."
thank you and Regards
Rashed
May 27, 2014 at 3:29 pm
alhakimi (5/27/2014)
Dear friendThank you for your quick response, i tried that , but two points still need your assistant with is those two tables are from two different databases , secondly they have different collations so i get this error message:
"Cannot resolve the collation conflict between "Arabic_CI_AS" and "Arabic_CI_AI" in the EXCEPT operation."
thank you and Regards
Rashed
Then you will need to force the collation in your select statement. Not sure if you want accent sensitive or accent insensitive so you will have to decide which collation to use. But here is an example of what will work with your sample data.
select PNR collate Arabic_CI_AS,
Tkt_Number collate Arabic_CI_AS,
DepaCityName collate Arabic_CI_AS,
ArriCityCode collate Arabic_CI_AS,
name collate Arabic_CI_AS
from table2
EXCEPT
select PNR collate Arabic_CI_AS,
Tkt_Number collate Arabic_CI_AS,
DepaCityName collate Arabic_CI_AS,
ArriCityCode collate Arabic_CI_AS,
name collate Arabic_CI_AS
from table1
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 30, 2014 at 4:01 pm
Hi friends ,
I run the script mentioned and finally i got one of the tables giving the results and i have run the same script on other table that has some other data but i got the following error message:
Msg 421, Level 16, State 1, Line 1
The ntext data type cannot be selected as DISTINCT because it is not comparable.
Msg 421, Level 16, State 1, Line 1
The ntext data type cannot be selected as DISTINCT because it is not comparable.
appreciates your kind assistant..
Regards
Rashed
May 30, 2014 at 4:04 pm
The best option would be to change the column to nvarchar(max).
The other option is to cast/convert your column during the query to nvarchar(max).
May 30, 2014 at 4:19 pm
you are really experts.. 😀
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply