I want to join 2 tables by a unique ID field, but the ID field also has
multiple NULLS which I do not want to ignore and I fear they will cause
duplication.
Using TableA and TableB below i will demonstrate the problem.
TableA
TableA.ID Field1 Field2
1 Paul 1
Null John 1
2 John 1
I am getting VERY poor performance on a join even though I have indexes. Instead of using the indexes, SQL Server does a table scan on one of the tables in the join. After I force the index in the query, I can see why. The index doesn't give any better results than a table scan. I suspect it may be because the table has a huge number of rows with null values for one column of a two-column key used in the join. However, the same tables use the indexes and give good performance when queried independently. It seems the problem is specific to join processing. I notice that the index I created appears to have pages referencing the null rows. I know that Oracle disregards nulls when creating indexes, but does SQL Server not do so? Is there any way to create a better index? And if I can't solve the join problem, any suggestions about workarounds?
The details: -I have TABLE A with columns X, Y, and Z. In TABLE A there are 380,000 rows, and X and Y yield a unique key. The index is on X and Y. This table has 240,000 rows where X is null but there is a non-null value for Y. -I have TABLE B (60,000 rows) with columns X,Y, and Q, where X,Y, and Q yield the unique key and have an index. -Here is my query: SELECT A.Z FROM A, B WHERE B.Q=1 AND B.X = A.X AND B.Y = A.Y
I need to pull all records from the Item table and then I need to populate the most recent OrderNo and O.DateCreated. I got this far but if there is a part in the item table that does not have an order against it, I do not get a value and my goal is to see any parts that have not been ordered in the last year. Something like this:
SELECT I.PartNumber, I.Description, I.DateCreated FROM item I CROSS APPLY (SELECT TOP 1 O.OrderNo, O.DateCreated FROM Orders O WHERE O.PartNumber = I.PartNumber ORDER BY O.DateCreated DESC) O PartNumberOrderNoO.DateCreated 1A1XXX 1CHXX1 1/8/2014 2A2XXX 1CHXX3 1/20/2014 3A3XXX NULL NULL 1B1XXX 2CHXX1 2/10/2014 2B2XXX 2CHXX3 2/22/2014 3B3XXX NULL NULL
I have a query which is returning a different result set when it is run against identical tables in 2 different environments.
The query is like:
Select F.LicenseeID, IsSpecialLicensee from FactTable F left join View_SpecialLicensee SL on F.LicenseeID = SL.LicenseeID
The Create Statement for the view is like
Create View [dbo].[View_SpecialLicensee] as Select LicenseeID, LicenseeName, IsSpecialLicensee = 1 from DimensionLicensee where LicenseeName like '%ibm%' or LicenseeName like '%cisco%' or LicenseeName like '%hp%'
In my test environment, I get the query result I expected: LicenseeID, IsSpecialLicensee 1 , 1 - (where LicenseeName = 'IBM') 2, null - (where LicenseeName = 'Juniper') 3, 1 - (where LicenseeName = 'Cisco') 4, null - (where LicenseeName = 'Microsoft') 5, null - (where LicenseeName = 'Oracle') 6, null - (where LicenseeName = 'Apple')
In my production environment, I get the following query result: 1 , 1 - (where LicenseeName = 'IBM') 2, 1 - (where LicenseeName = 'Juniper') 3, 1 - (where LicenseeName = 'Cisco') 4, 1 - (where LicenseeName = 'Microsoft') 5, 1 - (where LicenseeName = 'Oracle') 6, 1 - (where LicenseeName = 'Apple')
Ideas as to what changed gratefully received.
FYI the production environment which returned the 2nd dataset is SQL2000, I have got the result I expected in both SQL2000 and SQL2005 development environments.
I have 3 columns that I'd like to set as a key, however I do have multiple instances of NULLs for all 3 columns. Thus when I try to create the key, it stops because there are multiple instances of NULLs. I looked at using procedure that check each entry made into the table, but I couldn't figure out how to deny an INSERT/UPDATE request. Anyone have any better methods of implimenting the unique key?
To make the long story short, we have a situation where there can be different datatypes for a column. For example, 'AttributeValue' can be a datetime, int, float etc. My DBA is suggesting the following design,
For any AttributeId, only one of the value columns will have a value and the rest will be NULLs. I feel that this is a bad design. (FYI, We decided against SQLVariant for specific reasons). I have suggested, we create a seperate table for each datatype, like
tblAttribute: AttributeId, AttributeDataTypeId
tblAttributeValueFloat: AttributeId INT, AttributeValue FLOAT tblAttibuteValueDateTime: AttributeId INT, AttributeValue DATETIME etc for each datatype.
Depending on the AttributeDataTypeId in the Attribute table go to the specific table and pick up the value. This way, we don't need to have too many NULLs in a row. Which of the 2 do you think is a better design? And if you agree with me, what would be the points to support it.
Hi, I have a non-cluster index on the filed, I need to make it non-cluster index and unique which allows multiple nulls, how should I do this with an sql statement?
Our database stores vehicle data in one table, but 3 different types of data are stored in the one table. The table contains all the columns for all 3 types so when you query the table you get at least 3 rows back with null values for all the columns that don't apply to that record. The data is imported to the table when it's updates so there's a possibility that they're updated at different times so they have a different BATCH like:
BATCH TYPE ID RATING INSURANCE SAFETY 300 SAFE 123 NULL NULL A 300 INS 123 NULL YES NULL 250 RATE 123 A NULL NULL
What I'd like returned is: ID, RATING, INSURANCE, SAFETY 123 A YES A
I'm trying to do a case statement to pull the data down, but I keep ending up with multiple rows because of all the nulls. I tried doing a SUM of the case statement with an ISNULL(SAFETY,0) but I can't SUM char values. I can probably do this with 3 temp tables to load the data that I want for each TYPE into them and then select and join them together, but is there a better way to do this?
I'm trying to unpivot a table in SSIS: The pivoted table basically looks like
ID DATE1 TEXT1 DATE2 TEXT2
----------------------------------
ID1 D1 T1 D2 T2
...
The unpivoted result looks like
ID DATE TEXT
-------------------
ID1 D1 T1
ID1 D2 T2
€¦
It works, but the one problem I am facing is: If D1 IS NULL in the pivoted table then D1 in the unpivoted table contains some strange value that is neither NULL nor a valid datetime. In a data viewer I get the in the DATE field "Fehler: Die Parameter Year, Month and Day beschreiben eine nicht darstellbare Datetime." (i. e. "Error: The parameters Year, Month and Day form an invalid datetime."). The loading of the OLE DB target yields an error because the DATE value cannot be converted to a valid datetime.
When i do a select on my emplee table for rows with null idCompany i dont get any records
I then try to modify the table to not allow a null idCompany and i get this error message:
'Employee (aMgmt)' table - Unable to modify table. Cannot insert the value NULL into column 'idCompany', table 'D2.aMgmt.Tmp_Employee'; column does not allow nulls. INSERT fails. The statement has been terminated.
Hello, I am in the progress of designing a new section of my database and was thinking of creating a hole new database instead of just creating tables inside the database. My question is can you JOIN multiple tables in an SQL Statement from multiple databases. Ie, In the Management program I have a database called 'Convention' and another one called 'Services', inside the two databases there are many tables. Can I link say tblRegister from Convention to tblUser in Services? Thanks
is there an elegant way to use one equals sign in a where clause that returns true when both arguments are null, and returns true when neither is null but both are equal and returns false when only one is null?
I have two SSIS packages that import from the same flat file into the same SQL 2005 table. I have one flat file connection (to a comma delimited file) and one OLE DB connection (to a SQL 2005 Database). Both packages use these same two Connection Managers. The SQL table allows NULL values for all fields. The flat file has "empty values" (i.e., ,"", ) for certain columns.
The first package uses the Data Flow Task with the "Keep nulls" property of the OLE DB Destination Editor unchecked. The columns in the source and destination are identically named thus the mapping is automatically assigned and is mapped based on ordinal position (which is equivalent to the mapping using Bulk Insert). When this task is executed no null values are inserted into the SQL table for the "empty values" from the flat file. Empty string values are inserted instead of NULL.
The second package uses the Bulk Insert Task with the "KeepNulls" property for the task (shown in the Properties pane when the task in selected in the Control Flow window) set to "False". When the task is executed NULL values are inserted into the SQL table for the "empty values" from the flat file.
So using the Data Flow Task " " (i.e., blank) is inserted. Using the Bulk Insert Task NULL is inserted (i.e., nothing is inserted, the field is skipped, the value for the record is omitted).
I want to have the exact same behavior on my data in the Bulk Insert Task as I do with the Data Flow Task.
Using the Bulk Insert Task, what must I do to have the Empty String values inserted into the SQL table where there is an "empty value" in the flat file? Why & how does this occur automatically in the Data Flow Task?
From a SQL Profile Trace comparison of the two methods I do not see where the syntax of the insert command nor the statements for the preceeding captured steps has dictated this change in the behavior of the inserted "" value for the recordset. Please help me understand what is going on here and how to accomplish this using the Bulk Insert Task.
I get a wo_ID and want to query company data. I have the following tables, work_orders (has wo_ID, wo_name and install_id), install_ids (has install_id, comp_id) and customers (comp_id, company_name). The query process goes like this: I get a wo_ID and I need to find the install_id that corresponds with that wo_ID in that same work_orders table. Then take that install_id and find out the comp_id that corresponds with that install_id from the install_ids table. Finally take that customer_id and link it to comp_id in the customers table to find out company data. I need to join it multiple times to get my final data. I have a simple one that works right now with one join and using the install_id, but I don't know how to expand it with multiple joins. Thanks.
I have two tables, one of which is a key table with a subaccount number and a set of attributes that define that subaccount. I am trying to join this key table with the table with all the attributes and come up with one table of subaccounts. The Subaccounts should only lookup the attributes associated with them, not all of the attributes, so I put OR [attribute] IS NULL in the WHERE clause so it only matched on the appropriate columns. This worked great in an initial test with two attributes but when I put all 9 attributes in it crashed with this message "Msg 415, Level 16, State 1 The current query would require an index on a work table to be built with 15 keys. The maximum allowable number of keys is 16" like this CREATE VIEW subaccounts (Subacct_no, balance) AS SELECT (s.Account_No + "." + r.Subacct_Ext, S.Balance) FROM Acct_Rcd r, Subacct_Key s WHERE (s.attr1 = r.attr1 OR s.attr1 IS NULL) AND (s.attr2 = r.attr2 OR s.attr2 IS NULL)
I'm trying to eliminate all records that do not have one of two conditions. I'm using INNER JOIN on a derived "table", not a table in my database. The code below summarizes what I'm trying to do. Please note that this is an extremely simplified query.
---------------------------
SELECT * FROM jobs INNER JOIN ( SELECT contact_id FROM contacts WHERE deleted = 0 )AS ValidContacts ON (jobs.owner = ValidContacts.contact_id OR jobs.assignee = ValidContacts.contact_id)
---------------------------
This works fine when the the "SELECT contact_id FROM contacts WHERE deleted = 0" part returns a small number of records, however when that part returns a very large number of records, the query hangs and never completes. If I remove one of the conditions for the JOIN, it works fine, but I need both. Why doesn't this work?
Another possible solution is if I were to use "WHERE/IN" like this:
---------------------------
SELECT * FROM jobs WHERE owner IN (SELECT contact_id FROM contacts WHERE deleted = 0) OR assignee IN (SELECT contact_id FROM contacts WHERE deleted = 0)
---------------------------
This would work fine, but I don't want to have to run the "SELECT contact_id FROM contacts WHERE deleted = 0" part twice (since in my real code, it is much more complicated and performance is a big issue". Any help would be greatly appreceated.
select scheme.opheadm.order_no, scheme.porecpm.order_no, delivery_no, invoice_no, scheme.opheadm.customer, qty_received from scheme.opheadm join scheme.porecpm on (ltrim(rtrim(scheme.porecpm.commnt)) like (ltrim(rtrim(scheme.opheadm.order_no)) + '/%')) where effective_date between '2011-10-01 00:00:00.000' and '2011-10-08 00:00:00.000'
It gives me the 5 rows that I need to work with, one column is customer (which is giving me customer code) that I want to replace with customer name from another table
So I tried:
select scheme.opheadm.order_no, scheme.porecpm.order_no, delivery_no, invoice_no, scheme.jcmastm.name, qty_received from scheme.opheadm join scheme.porecpm on (ltrim(rtrim(scheme.porecpm.commnt)) like (ltrim(rtrim(scheme.opheadm.order_no)) + '/%')) join scheme.jcmastm on scheme.opheadm.customer = scheme.opheadm.customer here effective_date between '2011-10-01 00:00:00.000' and '2011-10-08 00:00:00.000'
this works with the same 5 rows that i need but loops them through every customer from the table scheme.jcmastm giving me a total of 960 rows not just the 5 that i want to work with. why this is looping?
The code is the name of the test and the result is just that. Not all patients will have the same set of results. What I'd now like to do is pull out a CSV file of all results from 2013 including where the result is null. The format I am looking for is along the following lines:
Sex, Age, AKIN2, AKIN7, RIFLE,
The AKIN2, AKIN7 and RIFLE are the codes in the result table. I have tried OUTER JOINS but it seems to only pull out those records that exist (example below). I need to specify the result codes I am interested in otherwise the output could be enormous but this then does not pull out null values (a NULL value is an important as an actual value).
SELECT Samples.Patient_ID AS ID, Samples.LabNo As LabNo, Patients.Sex As Sex, DATEDIFF(year, Patients.DoB, Samples.Sampled) AS Age, Samples.Source as Source,
Ok, this is what I am trying to do:, 1) I am trying to get the number of employees that has completed all their online training within 10 days of hire 2) All the employees that are has no exception(no pre-service training) and has completed their checklist within 10 days 3) all the exception(pre-service) employees that has completed their training within 70 days.
I have these tables, hipaa2006, hipaa101, hipaa201_inputs, domesticviolence, securityawareness, securityawareness2006, civilrights_input, and people_first_data. People_first_data contains all the employees we have in our database, it has empl_pfid col, but does not have last_modified col which all the other tables has.
I have this queries:
1) select distinct(count(empl_pfid)) from people_first_data left outer join tbl_domesticviolence on people_first_data.empl_pfid = tbl_domesticviolence.employee_pfid left outer join tbl_hipaa101 on people_first_data.empl_pfid=tbl_hipaa101.employee_pfid left outer join tbl_hipaa201_input on people_first_data.empl_pfid= tbl_hipaa201_input.employee_pfid left outer join tbl_hipaa2006 on people_first_data.empl_pfid=tbl_hipaa2006.employee_pfid left outer join tbl_securityawareness on people_first_data.empl_pfid=tbl_securityawareness.employee_pfid left outer join tbl_securityawareness2006 on people_first_data.empl_pfid= tbl_securityawareness2006.employee_pfid where people_first_data.empl_pfid = '639846' and tbl_domesticviolence.last_modified is not null and tbl_hipaa101.last_modified is not null and tbl_hipaa201_input.last_modified is not null and tbl_hipaa2006.last_modified is not null and tbl_securityawareness.last_modified is not null and tbl_securityawareness2006.last_modified is not null
2) ( I tried union, but i only wanted one number) select count(last_modified) as date1 from tbl_hipaa2006 union select count(last_modified) as date2 from tbl_hipaa101
hi i user this join and i have the answer like this"
select u.userid, u.user_name, u.password, c.code_description as role_code, convert(varchar, u.expiry_date,101) as expiry_date, u.created_date, u.active from [usermaster] u inner join [codeMaster] c on 'SP'=c.code where u.userid = '2'
select u.userid, u.user_name, u.password, c.code_description as role_code, convert(varchar, u.expiry_date,101) as expiry_date, u.created_date, u.active, v.user_date from [usermaster] u inner join [codeMaster] c inner join [HRUser_developerlog] v on 'SP'=c.code or u.userid=v.inserted_id and v.operation='delete' where u.userid = '2'
but i am getting error.can any onre please help me and please give me query please
I have two tables, let's say "Main" and "Dictionary".
The Main table has several fields that point to records in the same dictionary table. Because of the multiple joins I couldn't get any results if I use an expression like:
SELECT Main.ID, Dictionary.Text AS Data1, Dictionary.Text AS Data2
FROM Main, Dictionary
WHERE Main.Data1 = Dictionary.ID AND Main.Data2 = Dictionary.ID
What kind of join expression should I use? I have to generate this expression programmatically, so it's quite important to keep it as simple as possible!
An EMPLOYEE will always have at least 1 SKILL but each SKILL may or may not have any SKILLOPTIONS. I do an INNER JOIN:
EMPLOYEE->SKILL->SKILLOPTIONS but I only get a record if there is actually a SKILLOPTION. I want a record with EMPLOYEE and SKILL even if there are no SKILLOPTIONS. In Oracle it is the (+) symbol in the WHERE statement in conjunction with the JOIN. Am new to this so I'm sure the answer is simple.
I have a left Join problem - Appreciate any suggestions This is the error message C:InetpubwwwrootFTDecAdminFinishedAdminBeta2.aspx(47) : error BC30201: Expression expected. "tered] FROM [Colleges] "& _ ~ C:InetpubwwwrootFTDecAdminFinishedAdminBeta2.aspx(49) : error BC30035: Syntax error. " Left Join [PIDO] ON ([Colleges].[CollegeID] = ([PIDO].[CollegeID]) "& _ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ C:InetpubwwwrootFTDecAdminFinishedAdminBeta2.aspx(53) : error BC30451: Name 'queryString' is not declared. dbCommand.CommandText = queryString ~~~~~~~~~~~This is the Code >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> FROM [Colleges] "& _ '" Left Join [PIDO] ON ([Colleges].[CollegeID] = ([PIDO].[CollegeID]) "& _ " left join [GroupPA] ON ([Colleges].[CollegeID] = [GroupPA].[CollegeID])ORDER BY "& sortBy >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Can anyone give me some guidelines as to when to chose JOINS over returning multiple resultsets in a strored procedure.. For eample, I have two tables, Orders and OrderDetails, which are linked by a primary key field. There can be orders w/o a corresponding record in orderdetails. 1.) I can return all orders and their details using a stored preocedure that has: SELECT o.order_id as OrderId, o.customername, od.order_id, od.orderdate FROM orders AS o LEFT OUTER JOIN orderdetails AS od ON (o.order_id=od.order_id) 2.) I can do the same by returning two results sets in a different stored procedure: SELECT order_id, customername FROM orders SELECT order_id, orderdate FROM orderdetails I think the client processing time for the second option will be slightly less, because the resultset I need to filter will only be as big as the orederdetails table (it will not include records for orders that have no details). Regardless, I think this would only make for a small performance gain, so if Option 1 is better in Database performace, I would probably go with that. I assume the method to choose also depends on table size and # of JOINS. Any guidance would be appreciated. Thanks, Al
I have a table with a large number of records that I need to delete, before attempt to perform the delete I also archived the records to another table.
So I need to delete all of these selected records stored in the archive table from the main table. I can now reference all the records that qualify for the delete in the main table by performing a join on the archive table like so:
select * from my_main_table a join my_archive_table b on a.distinct_id=b.distinct_id and a.surrogate_key=b.surrogate_key and a.identifier=b.indentifier
So all the records check out to be the ones I'd like to perform a delete on but I just can't figure out how to perform a delete of the records with little or no change to the existing query.
Obviously something like this won't work:
delete from my_main_table a join my_archive_table b on a.distinct_id=b.distinct_id and a.surrogate_key=b.surrogate_key and a.identifier=b.indentifier
Though it would be nice if it did.:D
So my question is how would I use the existing query with some modification to delete only the records that this query returns. I've tried selection of records in the main table based on the existing records in the archive table but it can return a higher number of records than what I know is expected. I actually need the join specified to be in place to do it.
I have three tables, Users, DocType and Docs. In the DocType table there are multiple entries for allowed document types, the descriptions and other pertinent data. In the Docs table, there are all manner of documents. In the User table are the users.
The DocType and Docs tables are relational. DocType.ID = Docs.tID The Users and Docs tables are relational. Users.ID = Docs.uID
Every user is allowed to have exactly one document of each type. Therefore if there are 10 document types in the DocType table, there may be as many as 10 matching documents in the Docs table.
What I need is a single record for each user returning a boolean for each document type, whether or not there is a matching record in the Docs table.
For example, there are 5 document types defined in the DocType table (types 1 - 5), so the DocType table has 5 rows. In the Docs table, there are 23 rows, and in the User table there are 10 rows. Given that each user may have only one of each DocType, there could be a maximum of 50 rows in the Docs table, but there are 23, meaning that on the average each user is missing one document.Now the challenge is to return a table of all the users (10 rows) with a boolean value for each of the rows in DocType (as columns) based on whether there is a value in the Docs table that matches both the DocType and User.
I have 3 tables , Customer , Sales Cost Charge and Sales Price , i have join the customer table to the sales price table with a left outer join into a new table.
i now need to join the data in the new table to sales cost charge. However please note that there is data that is in the sales price table that is not in the sales cost charge table and there is data in the sales cost charge table that is not in the sales price table ,but i need to get all the data. e.g. if on our application it shows 15 records , the sales price table will maybe have 7 records and the sales cost charge table will have 8 which makes it 15 records
I am struggling to match the records , i have also tried a left outer join to the sales cost charge table however i only get the 7 records which is in the sales price table. see code below
I have two tables a and b, where I want to add columns from b to a with a criteria. The columns will be added by month criteria. There is a column in b table called stat_month which ranges from 1 (Jan) to 12 (Dec). I want to keep all the records in a, and join columns from b for each month. I do not want to loose any row from a if there is no data for that row in b.
I do not know how to have the multiple joins for 12 different months and what join I have to use. I used left join but still I am loosing not all but few rows in a, I would also like to know how in one script I can columns separately from stat_mont =’01’ to stat_month =’12’
/****** Script for SelectTopNRows command from SSMS ******/ SELECT a.[naics] ,a.[ust_code] ,a.[port] ,a.[all_qty_1_yr] ,a.[all_qty_2_yr]
[Code] ....
Output should have all columns from a and join columns from b when the months = '01' (for Jan) , '02' (for FEB), ...'12' (for Dec): Output table should be something like
* columns from a AND JAN_Cum_qty_1_mo JAN_Cum_qty_2_mo JAN_Cum_all_val_mo JAN_Cum_air_val_mo JAN_Cum_air_wgt_mo JAN_Cum_ves_val_mo FEB_Cum_qty_1_mo FEB_Cum_qty_2_mo FEB_Cum_all_val_mo FEB_Cum_air_val_mo FEB_Cum_air_wgt_mo FEB_Cum_ves_val_mo .....DEC_Cum_qty_1_mo DEC_Cum_qty_2_mo DEC_Cum_all_val_mo DEC_Cum_air_val_mo DEC_Cum_air_wgt_mo DEC_Cum_ves_val_mo (FROM TABLE b)
I am trying to build a report that access data from multiple cubes. Is it possible to join multiple cubes based on their shared dimensions? Or is it possible for report to display data from multiple cubes properly aligned based on the dimension.
Environment: SQL Server Analysis Services 2005 and Reporting Services 2005
Example: We have multiple cubes with “Year� as a shared dimension and each one has different Rate info. The cubes have other set of shared dimensions which I am planning to set it up as a parameter. I would like to display the report as Year Rate1 Rate2 Rate3…
