|
Gaaaah, I finally found the cause of the problem. I apparently created my original sproc with "SET ANSI_NULLS OFF". By default queries in management studio runs with ANSI_NULLS ON. DOH![^]
For a simple example of the sproc I was playing with:
create PROCEDURE [dbo].[Bob_Test]
@MaterialNumber varchar(18)
AS
DECLARE @Replace varchar(18)
SELECT @MaterialNumber
IF (@Replace != '')
SET @MaterialNumber = @Replace
SELECT @MaterialNumber
When I call this with ANSI_NULLS OFF
Bob_Test '123456' my results show 123456 from the first select in the sproc, and NULL for the select after the if statement.
With ANSI_NULLS ON both selects return 123456. My faith SQL Server has been restored. Tune in next week for more inept coding... 
|
|
|
|
|
More of a theory question than anything. I have a website where people who register fall under two different user types, say attorney and client for example.
If a person registers as a lawyer, they fill out a lawyer profile. If they register as a client, they fill out a client profile. The two profiles are different.
So I am trying to figure this out from a database standpoint. Do you think the best bet is a three table setup?
User
------
ID
Email
Password
User_Type (1 for lawyer, 2 for client)
Lawyer
-------
ID (AI field)
User_ID
First
Last
etc
Client
--------
ID (AI field)
User_id
First
Last
Violation
etc...
Or I could do a two table with just Lawyer and Client and check both tables at login time? It would be more code intensive but less data. What is a good approach here? Open to suggestions.
Cheers, --EA
|
|
|
|
|
Turn it on it's ear, create a person table and attach an attribute for role, use a many to many link table to cater for a lawyer who is also a client!
Person
PersonID
lnkPersonRole
LinkID
PersonID
RoleID
Role
RoleID
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
That is a good solution, thank you. In regards to the profile aspect, how would you handle role specific attributes? Say, I want to store a lawyer's license number, or a client's height and weight?
|
|
|
|
|
You have 2 real choices, add each attribute as a column in the person table (accepting that a lot will be blank) or add another table of person attributes and only link the valid attributes to each person record.
AttrType
AttrTypeID
Attr [Licence No]
PersonAttr
AttrID
AttrTypeID
AttrValue [123456A]
The attributed solution is technically the most correct method, it is also the most complex and that complexity can cause issues. The column method means that every time you need to add an attribute you have to touch the code. It then becomes a business decision which way to go.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Seems like another good answer. What is the purpose of the AttrId column in the PersonAttr table? A simple primary key? Or is it foreign key for another table?
|
|
|
|
|
EVERY table needs a primary key (in an OLTP database) so the AttrID is an IDENTITY field and is the primary key.
Note the format (int) is a convention, some like GUIDs but I can't stand them. I also insist the primary key field is the FIRST field added to the table and is the table name + 'ID' so it should have been 'PersonAttrID'
When you have 100s of table you do not want to have to chasing down the name of the PK field for each.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I am with you on the guid issue, unnecessarily complicated. I see no reason to go further than an identity field. This project is MySql and CakePHP, so it dictates it's own naming convention for tables and columns and such. Well, you can get around it but it requires unnecessary code to get around.
Anyways, thank you for the guidance I am going to go with person table, attribute table and then third table that has id, person_id, attr_id, and attr_value. Is this really a true many to many relationship, adding the attr_value column to the joining table?
|
|
|
|
|
One more question about this: for fields that are common to all role types and mandatory, would you encourage adding those to the person table? For example, email address, age?
|
|
|
|
|
Normally yes, at least for data that's directly connected to the person, such as age, weight and length and such.
But while an email address might be personal, it might also be connected to the work role, and a person might also have more than one email address. And so on.
It depends on the purpose of your application, and how it might change in the future. Always plan ahead.
<edit>fixed typo</edit>
modified 21-Jan-12 17:09pm.
|
|
|
|
|
Mycroft Holmes wrote: When you have 100s of table you do not want to have to chasing down the name of the PK field for each.
Well perhaps if you bill by the hour you might.
|
|
|
|
|
I would use you solution but with a slight change. I would include all columns common to Client and lawyer in the user class. i.e First, Last. If there are few columns that are different for Client and Lawyer, I would just role everything up to User, and allow nulls for those columns which don't apply to both client and lawyer. You will need some business rules to determine when those null fields should have a value.
|
|
|
|
|
I currently have an application that connects to a local Access 2007 database. The application itself is written within Access (VBA) and uses DAO connectivity.
I connect to the data source and work with linked tables (queries done in similar manner) so that the user interface and VBA code is separate from the data to promote sw maintainability.
What is now desired is the ability for my user to quickly change between data sources. That is, the user can browse his local machine, select an .ACCDB file, and then change his data source for my app - not exiting out of the app.
I currently have a kludgy way to do this (still all in DAO) that requires exiting out of the application for the change to take effect.
Would an ODBC configuration provide an easier solution? In ODBC, you establish the connectivity ahead of time, BUT you have the ability to change the Database (data source) under User DSN, Configure, Select Database.
FURTHERMORE, the data may migrate to a server-based repository (SQL Server, Oracle, etc...) This may/WILL dictate me switching to ADO.
Any suggestion on how to simplify the selecting of databases?
Thank you.
|
|
|
|
|
I would migrate to .net and make it DB agnostic using DBProviderFactories[^].
Then you can choose your poison in the app.config <connectionstrings> section.
|
|
|
|
|
I have purchased Visual Studio 2010 Professional.
Along with it SQL server express 2008 R2 has been installed, but I cannot find "SQL Server Management Studio". May be it may not be needed also.But for learning purpose I need it. If I download
from MSDN ,I will get SQL express 2005 and may be I will land in conflict. Please help me on this issue. Also I want to know is there
ADO.NET connectivity for VC++ also ?
Be Happy
|
|
|
|
|
adityarao31 wrote: I cannot find "SQL Server Management Studio".
Try here[^].
adityarao31 wrote: is there
ADO.NET connectivity for VC++ also
It depends. If you are coding in CLI/CLR, then yes. If it's MFC, etc, no.
|
|
|
|
|
Thank you very much
Be Happy
|
|
|
|
|
I have two tables. I want to get all the rows from first table but want the where clause from another table. Here is my query. In following query I don't want any row from table tbTasks1 but the following query showing me rows from tbTasks1 also. Could u please modify this query.
select * from tbDocument,tbTasks1
where
tbTasks1.IsDelete = 0
|
|
|
|
|
There must be something that joins the two tables together... what is it? Use a join to attach the tables together like this:
select tbDocument.*
from tbDocument inner join tbTasks1 on tbDocument.FOREIGNKEY = tbTasks1.PRIMARYKEY
where tbTasks1.IsDelete = 0
|
|
|
|
|
This will give same result as op's Query...
The op's Query is using implicit inner join.
Rather than inner join, Left Or Right Join will work for op's requirement.
"Insanity is doing the same thing over and over again but expecting different results.” — Rita Mae Brown
"If you tell the truth, you don't need to remember anything" - Mark Twain
modified 19-Jan-12 3:59am.
|
|
|
|
|
V@rsh@ wrote: This will give same result as op's Query
No, it won't. There is a big difference between these two queries:
select * from tbDocument,tbTasks1
where
tbTasks1.IsDelete = 0
select tbDocument.*
from tbDocument inner join tbTasks1 on tbDocument.FOREIGNKEY = tbTasks1.PRIMARYKEY
where tbTasks1.IsDelete = 0
The first one is called a "cartesian join" and is almost always a mistake.
|
|
|
|
|
Agreed op has not used any condition to join both tables in the "Where" clause because of which it uses Cartesian product rather than Inner Join... My fault 
But it will be same as
Select * from tab1
Inner Join tab2 on 1=1
where tab2.somefield=somevalue
So I think that was actually representing implicit inner join which uses cartesian product 
"Insanity is doing the same thing over and over again but expecting different results.” — Rita Mae Brown
"If you tell the truth, you don't need to remember anything" - Mark Twain
|
|
|
|
|
Try this Query :
Select * from tbDocument
Left join tbTasks1 On tbDocument.Column=tbTasks1.Column and tbTasks1.IsDelete = 0
Your Query is using implicit inner join & inner Join by default applies condition (Filters) on Both tables. You need to use Left Join Or Right Join for you requirement.
"Insanity is doing the same thing over and over again but expecting different results.” — Rita Mae Brown
"If you tell the truth, you don't need to remember anything" - Mark Twain
|
|
|
|
|
I'm not sure what you mean when you say that you don't want any rows from tbTasks1. I assume that you mean that you don't want to see the tbTasks1 columns in your result set. In which case you should specify which columns you want, something like:
select tbDocument.col1, tbDocument.col2, tbDocument.col3
from tbDocument inner join tbTasks1
on tbDocument.aKey = tbTasks.aKey
where tbTasks1.IsDelete = 0
Edit: which I've just realised is exactly the same answer Damian gave earlier. Doh!
|
|
|
|
|
if both the tables having same structure
u can use
select * from tbDocument
union all
select * from tbTasks1
where
tbTasks1.IsDelete = 0
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
