This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. When this option is set, it is possible to read uncommitted modifications, which are called dirty reads. READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions. Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. WITH (NOLOCK) should be avoided like the plague it is unless you fully understand the consequences of reading rows that have been modified by other transactions but not yet committed.
This eliminates confusion -> does the Address table actually contain a single address or multiple addresses? So, Person becomes People, and Address becomes Addresses. Instead of naming tables like Person, they should be named after the collection of items they contain, in plural. This prevents confusion (read bugs) in joins such as dbo.Person LEFT JOIN dbo.Address ON Person.ID = Address.Person. So for instance, instead of the ID column in the People table being named ID, and it being named Person in the Address table, I'd name it PersonID in both tables.
FROM Person should be FROM dbo.Person -> this eliminates any confusion if you introduce new schemas in the future, and prevents the query optimizer from having to look for the default schema for your user.įor maintainability in the future, you probably want to name columns the same regardless of which table they are in. This should prevent duplicate rows being displayed in your results.Īlways use the schema qualifier on the FROM clause. The GROUP BY clause at the end ensures only a single row is returned for each unique combination of columns in the GROUP BY clause. GROUP BY (person.FirstName + ' ' + person.LastName) LEFT JOIN dbo.Email WITH (NOLOCK) ON Person.ID = Email.Person LEFT JOIN dbo.Phone ON PersonPhone.Person = Phone.ID LEFT JOIN dbo.PersonPhone ON Person.ID = PersonPhone.Person LEFT JOIN dbo.PersonAddress ON Person.ID = PersonAddress.Person If so, try this: SELECT (person.FirstName + ' ' + person.LastName) as FullName Presumably, you want to see a single entry for each unique person/address/email/website combination. I need the results to only contain a single unique row for each person. 3 of the copies have one email and three have another email.įor "Mike Pamstein" I get two duplicate rows with the same email. Left join Email with (nolock) on Person.ID = Email.Personįor this example, I get 6 rows of "Mike Worths". Left join Phone on PersonPhone.Person = Phone.ID Left join PersonPhone on Person.ID = PersonPhone.Person Left join Address on PersonAddress.Address = Address.ID Left join PersonAddress on Person.ID = PersonAddress.Person SELECT (person.FirstName + ' ' + person.LastName) as FullName This is because the original designer wanted the table to be able to hold multiple phone numbers and emails and addresses. Person holds the primary information about the person but not the address, or phone, or email. There are multiple tables that have different information in them. I believe it has something to do with my join statements. If we grouped the employee_id and attendance_date, then A001 and A002 become duplicates.I am getting more rows then I expect returned by my query. ( ' A003', CONVERT( DATETIME, ' 01-01-11', 5))Īfter inserting the data, check the data of the below table. INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE) VALUES
0 kommentar(er)
