October 1, 2012 at 7:40 am
Dear all,
I have the following tables:
CREATE TABLE [dbo].[deal_2](
[cusip] [varchar](100) NULL,
[isin] [varchar](100) NULL,
[name] [varchar](100) NOT NULL,
[deal_type] [varchar](100) NOT NULL
ON [PRIMARY]
CREATE TABLE [dbo].[price_2](
[price_id] [int] NOT NULL,
[cusip] [varchar](9) NULL,
[isin] [varchar](12) NULL,
[orig_price] [varchar](50) NOT NULL,
[price] [decimal](18, 2) NULL,
[cmbs_spread] [varchar](50) NULL,
[cmbs_price] [decimal](18, 2) NULL,
[int] NOT NULL,
[price_type] [varchar](50) NOT NULL,
[date] [date] NOT NULL
ON [PRIMARY]
I have 109 records in both tables. Column "cusip" has null property and the dataset in that column is also null.
I am trying to execute this query below, which is resulting in no data output in SQL Server 2012.
SELECT d.name, d.cusip, d.isin, p.orig_price, p.price, p.cmbs_spread, p.cmbs_price, p.size, p.price_type, d.deal_type, p.date
FROM dbo.price_2 p
INNER JOIN dbo.deal_2 d
ON d.isin = p.isin
WHERE p.isin IS not NULL AND d.isin IS not NULL
ORDER BY p.date
Any help would be very much appreciated. Thank you for your time and response.
October 1, 2012 at 7:50 am
From what you posted I can tell you that you don't need to check for NULLs in columns you INNER JOIN on. So the following query will produce exactly the same results as yours one:
SELECT d.name, d.cusip, d.isin, p.orig_price, p.price, p.cmbs_spread, p.cmbs_price, p.size, p.price_type, d.deal_type, p.date
FROM dbo.price_2 p
INNER JOIN dbo.deal_2 d
ON d.isin = p.isin
ORDER BY p.date
However the only thing you forgot to mention in your post is: what do you really want your query to return!
I can only guess that you need to use OUTER JOIN instead of INNER JOIN, I'm not sure which one LEFT or RIGHT, so you can try both:
SELECT d.name, d.cusip, d.isin, p.orig_price, p.price, p.cmbs_spread, p.cmbs_price, p.size, p.price_type, d.deal_type, p.date
FROM dbo.price_2 p
LEFT JOIN dbo.deal_2 d
ON d.isin = p.isin
ORDER BY p.date
OR
SELECT d.name, d.cusip, d.isin, p.orig_price, p.price, p.cmbs_spread, p.cmbs_price, p.size, p.price_type, d.deal_type, p.date
FROM dbo.deal_2 d
LEFT JOIN dbo.price_2 p -- see here instead of using RIGHT I've just changed table places
ON d.isin = p.isin
ORDER BY p.date
October 1, 2012 at 8:09 am
Thank you so much for your response and suggestion to my query.
I tired using the "RIGHT JOIN" & "LEFT JOIN" but i get 1139 instances of duplicate rows of data.
The desired output:
from deals-2 table:
d.name, d.cusip, d.isin, d.deal_type
from price_2 table:
p.orig_price, p.price, p.cmbs_spread, p.cmbs_price, p.size, p.price_type, p.date
OUTPUT:
[ deals-2 table + price_2 table] --> filed names d.name, d.cusip, d.isin, p.orig_price, p.price, p.cmbs_spread, p.cmbs_price, p.size, p.price_type, d.deal_type, p.date
Thank you so much for your help and time.
October 1, 2012 at 8:17 am
Can you give us some sample data - it sounds as though you haven't got any data in your keys.
Is this specifically a 2012 problem - does it work in other versions?
October 1, 2012 at 8:55 am
...
OUTPUT:
[ deals-2 table + price_2 table] --> filed names
d.name, d.cusip, d.isin, p.orig_price, p.price, p.cmbs_spread, p.cmbs_price, p.size, p.price_type, d.deal_type, p.dateThank you so much for your help and time.
The question "what is really required?" was not about the list of columns in resultset, as it was quite obvious from your query...
Question is about "business rules" you are trying to apply. What do you want to see in output from you JOIN query? Something like:
I need all records from table 1 and records from table 2 where [something?]...
Some sample of data would also help. Please supply it as INSERT statements so we can use it easily (link at the bottom of my signature explains how it should be done).
October 1, 2012 at 8:56 am
Thank you for response.
I tired posting the sample data with my original post but I was not sure how to format the data so it can be readable.
I have tired my best to represent sample of the data inside both of the tables.
price_2 table data:
INSERT INTO [dbo].[price_2] VALUES
(1, 'NULL', 'NULL','M70S', '75.00', 'NULL', 'NULL', '47300000', 'TALK', '17/09/2012'),
(2, 'NULL', 'NULL', 'MH90S', '97.00', 'NULL', 'NULL', '2000000', 'TALK', '17/09/2012'),
(3, 'NULL', 'NULL', '60A', '60.00', 'NULL', 'NULL','29800000', 'TALK', '17/09/2012'),
(4, 'NULL', 'NULL', 'M90S', '95.00', 'NULL', 'NULL', '16100000', 'TALK', '17/09/2012'),
(5, 'NULL', 'FR0010912949', 'Hteens', '18.00', 'NULL', 'NULL', '2600000', 'TALK', '17/09/2012'),
(6, 'NULL', 'US04541GNA66', 'LM80S', '83.50', 'NULL', 'NULL', '340000', 'TALK', '17/09/2012'),
(7, 'NULL', 'US05530MAA7', 'M60S', '65.00', 'NULL',' NULL', '7510000', 'TALK','17/09/2012');
deal_2 table data:
INSERT [dbo].[deal_2] VALUES
(1, 'NULL', 'NULL', 'ABSHE 2004-HE2 M2', 'RMBS'),
(2,' NULL', 'NULL', 'ACE 2002-HE3 M1', 'RMBS'),
(3, 'NULL', 'NULL',' ACE 2004-OP1 M2', 'RMBS'),
(4, 'NULL', 'NULL', 'ACE 2005-WF1 M3', 'RMBS'),
(5, 'NULL', 'FR0010912949', 'AHMA 2006-5 A1', 'RMBS'),
(6, 'NULL', 'US04541GNA66', 'ARMT 2005-7 2A21',' RMBS'),
(7, 'NULL', 'US05530MAA7', 'BSARM 2005-9 A1','RMBS');
I only have the sql server 2012 version, so I am unsure, if its only 2012 server bug. Also, I have a duplication in deal_2 table. When I run the query i get 1329 instances of records instead of 109 record:
SELECT *
FROM dbo.deal_2 d
LEFT OUTER JOIN dbo.price_2 p
ON d.isin = p.isin
ORDER BY p.date
Thank you so much for your time and help. I really appreciate your time and suggestions.
October 1, 2012 at 9:17 am
There's a problem with the test data. Using your original table layouts, the data doesn't match:
On price_2, price_id doesn't accept null.
Also, cuisp is too small (9) for the data (12).
It looks as though I've got the columns/data wrong somewhere too...
Can you correct it & repost it.
--======== TEST DATA =============
IF OBJECT_ID('dbo.deal_2') IS NOT NULL
DROP TABLE dbo.deal_2;
CREATE TABLE [dbo].[deal_2](
[cusip] [varchar](100) NULL,
[isin] [varchar](100) NULL,
[name] [varchar](100) NOT NULL,
[deal_type] [varchar](100) NOT NULL
)
ON [PRIMARY];
insert into dbo.deal_2 values ( NULL, NULL, 'ABSHE 2004-HE2 M2', 'RMBS' ) ;
insert into dbo.deal_2 values ( NULL, NULL, 'ACE 2002-HE3 M1', 'RMBS' ) ;
insert into dbo.deal_2 values ( NULL, NULL, 'ACE 2004-OP1 M2', 'RMBS' ) ;
insert into dbo.deal_2 values ( NULL, NULL, 'ACE 2005-WF1 M3', 'RMBS' ) ;
insert into dbo.deal_2 values ( NULL, 'FR0010912949', ' AHMA 2006-5 A1', 'RMBS' ) ;
insert into dbo.deal_2 values ( NULL, 'US04541GNA66', 'ARMT 2005-7 2A21', 'RMBS' ) ;
insert into dbo.deal_2 values ( NULL, 'US05530MAA7', 'BSARM 2005-9 A1', 'RMBS' ) ;
select * from dbo.deal_2;
IF OBJECT_ID('dbo.price_2') IS NOT NULL
DROP TABLE dbo.price_2;
CREATE TABLE [dbo].[price_2](
[price_id] [int] NULL, -- Changed from NOT NULL
[cusip] [varchar](12) NULL, -- 9 is too small
[isin] [varchar](12) NULL,
[orig_price] [varchar](50) NOT NULL,
[price] [decimal](18, 2) NULL,
[cmbs_spread] [varchar](50) NULL,
[cmbs_price] [decimal](18, 2) NULL,
[int] NOT NULL,
[price_type] [varchar](50) NOT NULL,
[date] [date] NOT NULL
)
ON [PRIMARY];
set dateformat dmy;
insert into dbo.price_2 values ( NULL, NULL, 'M70S', 75.00, NULL, NULL, 0, 47300000, 'TALK', '17/09/2012' ) ;
insert into dbo.price_2 values ( NULL, NULL, 'MH90S', 97.00, NULL, NULL, 0, 2000000, 'TALK', '17/09/2012' ) ;
insert into dbo.price_2 values ( NULL, NULL, '60A', 60.00, NULL, NULL, 0, 29800000, 'TALK', '17/09/2012' ) ;
insert into dbo.price_2 values ( NULL, NULL, 'M90S', 95.00, NULL, NULL, 0, 16100000, 'TALK', '17/09/2012' ) ;
insert into dbo.price_2 values ( NULL, 'FR0010912949', 'Hteens', 18.00, NULL, NULL, 0, 2600000, 'TALK', '17/09/2012' ) ;
insert into dbo.price_2 values ( NULL, 'US04541GNA66', 'LM80S', 83.50, NULL, NULL, 0, 340000, 'TALK', '17/09/2012' ) ;
insert into dbo.price_2 values ( NULL, 'US05530MAA7', 'M60S', 65.00, NULL, NULL, 0, 7510000, 'TALK', '17/09/2012' ) ;
select * from dbo.price_2;
-- ====== ORIGINAL QUERY ========
SELECT *
FROM dbo.deal_2 d
LEFT OUTER JOIN dbo.price_2 p
ON d.isin = p.isin
ORDER BY p.date
October 1, 2012 at 9:28 am
Dear laurie, Thank you so much for your response and time. I am not sure what you mean by data not matching.
I apologies for the inaccurate data types for the field names, but other then that, there is no mistake in the data sample provided.
Maybe I am using the wrong query clause to join the two tables?
thank you for your help and suggestions.
October 1, 2012 at 9:34 am
The join is on isin, but the data in isin from table deal_2 is in cusip in table price_2.
Isin in price_2 is data like M70S.
October 1, 2012 at 9:46 am
Dear Laurie,
Apology for the mistype query, as this is the original query:
SELECT d.name, d.cusip, d.isin, p.orig_price, p.price, p.cmbs_spread, p.cmbs_price, p.size, p.price_type, d.deal_type, p.date
FROM dbo.price_2 p
LEFT OUTER JOIN dbo.deal_2 d
ON d.isin = p.isin
ORDER BY p.date
The isin column has the following values from record 5 onward "FR0010912949".
INSERT INTO [dbo].[price_2] VALUES
(1, 'NULL', 'NULL','M70S', '75.00', 'NULL', 'NULL', '47300000', 'TALK', '17/09/2012'),
(2, 'NULL', 'NULL', 'MH90S', '97.00', 'NULL', 'NULL', '2000000', 'TALK', '17/09/2012'),
(3, 'NULL', 'NULL', '60A', '60.00', 'NULL', 'NULL','29800000', 'TALK', '17/09/2012'),
(4, 'NULL', 'NULL', 'M90S', '95.00', 'NULL', 'NULL', '16100000', 'TALK', '17/09/2012'),
(5, 'NULL', 'FR0010912949', 'Hteens', '18.00', 'NULL', 'NULL', '2600000', 'TALK', '17/09/2012'),
(6, 'NULL', 'US04541GNA66', 'LM80S', '83.50', 'NULL', 'NULL', '340000', 'TALK', '17/09/2012'),
(7, 'NULL', 'US05530MAA7', 'M60S', '65.00', 'NULL',' NULL', '7510000', 'TALK','17/09/2012');
The "price_id" is not null data type (i.e 1,2,3,4..), which you do seem to have in your insert clause.
Thank you for your time and help.
October 1, 2012 at 10:09 am
Dear Eugene Elutin Sir,
I apologies for misunderstanding your post message and for the inadequate message.
I would like to display all data from deal_2 (table 1) and price_2 (table_2) in date order. I assumed the left join clause would be easy to implement with null data types, however i can not get the query to display correct result.
I have posted the data sample in insert statements in above post.
Thank you for response and suggestions.
October 1, 2012 at 10:16 am
OK - Using the code below, I'm only getting 7 rows - the rows from price_2, with deal_2 joined in with data or nulls where the join key is null.
Can you say again what's wrong & what results you want?
I'm running SQL 2008, so there may be an issue with 2012 - but I wouldn't think so with something basic like this...
Is the problem joining with null? You can't join on null.
--======== TEST DATA =============
IF OBJECT_ID('dbo.deal_2') IS NOT NULL
DROP TABLE dbo.deal_2;
CREATE TABLE [dbo].[deal_2](
[cusip] [varchar](100) NULL,
[isin] [varchar](100) NULL,
[name] [varchar](100) NOT NULL,
[deal_type] [varchar](100) NOT NULL
)
ON [PRIMARY];
insert into dbo.deal_2 values ( NULL, NULL, 'ABSHE 2004-HE2 M2', 'RMBS' ) ;
insert into dbo.deal_2 values ( NULL, NULL, 'ACE 2002-HE3 M1', 'RMBS' ) ;
insert into dbo.deal_2 values ( NULL, NULL, 'ACE 2004-OP1 M2', 'RMBS' ) ;
insert into dbo.deal_2 values ( NULL, NULL, 'ACE 2005-WF1 M3', 'RMBS' ) ;
insert into dbo.deal_2 values ( NULL, 'FR0010912949', 'AHMA 2006-5 A1', 'RMBS' ) ;
insert into dbo.deal_2 values ( NULL, 'US04541GNA66', 'ARMT 2005-7 2A21', 'RMBS' ) ;
insert into dbo.deal_2 values ( NULL, 'US05530MAA7', 'BSARM 2005-9 A1', 'RMBS' ) ;
select * from dbo.deal_2;
IF OBJECT_ID('dbo.price_2') IS NOT NULL
DROP TABLE dbo.price_2;
CREATE TABLE [dbo].[price_2](
[price_id] [int] NULL, -- Changed from NOT NULL
[cusip] [varchar](12) NULL, -- 9 is too small
[isin] [varchar](12) NULL,
[orig_price] [varchar](50) NOT NULL,
[price] [decimal](18, 2) NULL,
[cmbs_spread] [varchar](50) NULL,
[cmbs_price] [decimal](18, 2) NULL,
[int] NOT NULL,
[price_type] [varchar](50) NOT NULL,
[date] [date] NOT NULL
)
ON [PRIMARY];
set dateformat dmy;
insert into dbo.price_2 values ( 1, NULL, NULL, 'M70S', 75.00, NULL, NULL, 47300000, 'TALK', '17/09/2012' ) ;
insert into dbo.price_2 values ( 2, NULL, NULL, 'MH90S', 97.00, NULL, NULL, 2000000, 'TALK', '17/09/2012' ) ;
insert into dbo.price_2 values ( 3, NULL, NULL, '60A', 60.00, NULL, NULL, 29800000, 'TALK', '17/09/2012' ) ;
insert into dbo.price_2 values ( 4, NULL, NULL, 'M90S', 95.00, NULL, NULL, 16100000, 'TALK', '17/09/2012' ) ;
insert into dbo.price_2 values ( 5, NULL, 'FR0010912949', 'Hteens', 18.00, NULL, NULL, 2600000, 'TALK', '17/09/2012' ) ;
insert into dbo.price_2 values ( 6, NULL, 'US04541GNA66', 'LM80S', 83.50, NULL, NULL, 340000, 'TALK', '17/09/2012' ) ;
insert into dbo.price_2 values ( 7, NULL, 'US05530MAA7', 'M60S', 65.00, NULL, NULL, 7510000, 'TALK', '17/09/2012' ) ;
select * from dbo.price_2;
-- ====== ORIGINAL QUERY ========
--select * from dbo.deal_2;
--select * from dbo.price_2;
SELECT d.name, d.cusip, d.isin, p.orig_price, p.price, p.cmbs_spread, p.cmbs_price, p.size, p.price_type, d.deal_type, p.date
FROM dbo.price_2 p
LEFT OUTER JOIN dbo.deal_2 d
ON d.isin = p.isin
ORDER BY p.date
October 1, 2012 at 10:36 am
...
I would like to display all data from deal_2 (table 1) and price_2 (table_2) in date order. I assumed the left join clause would be easy to implement with null data types, however i can not get the query to display correct result.
I have posted the data sample in insert statements in above post.
Thank you for response and suggestions.
Hi! We are really trying to help you, but it's nearly impossible based on the details you have posted:
1. The data insert scripts fail to insert into tables created by DDL provided in your first post. Please test your full setup (DDL and data insert script) and ensure we can use it.
2. The sample data for Price_2 table contain NULL [cusip]'s in every row. You will not be able to join to such rows at all on cusip.
However based on the rule you gave here looks like your query should be based on LEFT JOIN between deal_2 and price_2 tables as per following:
SELECT d.* -- or specify whatever columns you want from deal_2 table
,p.* -- or specify whatever columns you want from price_2 table
FROM dbo.deal_2 AS d
JOIN dbo.price_2 AS p
ON -- that is the place for main question here! What do you really need to join on?
p.[cusip] = d.[cusip] -- if [cusip] is a key to join your tables together
Now, your deal_2 table doesn't look like one which has a KEY at all (or at least you didn't mentioned it)
About duplicates: If for a single CUSIP in deal_2 table there are multiple records with this CUSIP in price_2 tables, you will get duplicates! If you don't want them, you need to provide some de-duplication rule eg. the latest price for CUSIP by date or something like that.
Also, as CUSIP is not the key in deal_2 table you may have multiple records there with the same CUSIP there as well. For each of such deal_2 records every record from price_2 with matching CUSIP will also be returned, so you will have even more price duplications per CUSIP.
October 1, 2012 at 10:36 am
Dear laurie, Thank you so much for your response and help. I assumed you could join null columns using either right join or full outer join, but I am so confused why is it not working for my dataset.
I will keep trying to get the query to work but for very strange reason it works on yours but not mine.
Thank you so much for all your help.
October 1, 2012 at 10:46 am
There must be a difference somewhere, so you'll need to have a think about it & look carefully.
You can't join on null, because null means 'no value'.
FROM a LEFT OUTER JOIN b: This will include all rows from a, & join in any rows from b where the ON condition matches. RIGHT OUTER JOIN is the reverse.
If you want to join rows where the key is null, you really need to find another key if possible.
Good luck! 🙂
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply