asked
Problem using joins when use multiple tables
I have two types of table called **Organizations** and **OrgLogin**. **Organizations**: - ID - Name **OrgLogin**: - LoginId - UserName - Password I want to make select query with username and password in Organizations Table. So I used Joins. My query is, select top(55)'Insert into Organizations(Id,Username,Password)values(' + Cast(o.organizationId as varchar(50))+',''' + IsNull(''''+ol.UserName+'''', 'NULL')+','+ isnull(convert(nvarchar(max),HASHBYTES('MD5',ol.Password),1),'NULL')+ ')' FROM Organization AS o left join OrgLogin As ol ON ol.LoginId=o.OrganizationID I have the result set following, Insert into Organizations(Id,CreateDate,Username,Password)values(1,'NULL,NULL) Insert into Organizations(Id,CreateDate,Username,Password)values(2,'NULL,NULL) Insert into Organizations(Id,CreateDate,Username,Password)values(3,'NULL,NULL) Insert into Organizations(Id,CreateDate,Username,Password)values(4,'NULL,NULL) Insert into Organizations(Id,CreateDate,Username,Password)values(5,''V EX Electronics Systems Pvt.Ltd.Chennai',0xACB3BB721E1EC47C4CB569331ACC4E8E) Insert into Organizations(Id,CreateDate,Username,Password)values(5,''V EX Electronics Systems Pvt.Ltd',0x7748E2810E54C6C6EE376E37B786BCD8) Id's are repeating see '5' Id. Two times created. why it is happening? any idea?
If you have to values for 5 in the Organizations table, then you're going to return both values for 5 in any select statement. You could use the DISTINCT keyword, but that's just a crutch. The real question is, if the Id column in the Organizations table is your primary key, why are there two values? You should have a constraint to prevent that.
You're able to insert multiple ID values even though there is a unique constraint in place? That's not possible. Either the unique constraint is not there, or it's on more than one column and the data you're inserting isn't unique across those columns.
14 People are following this question.
