Basic MSSQL Question

Hello everyone,
Haven't used MSSQL in a while and I have a question about this query:
Here is what I need to achieve:
Basically I need to know if the same DataTableName exists for two suppliers.
In this table there are two fields of concern: DataTableName, SupplierID.
The process is that when requested I change the supplierId to another ID to
move the dataTable to another supplier. I need to check that it won't creat
e
a duplicate. There are no database constraints in place to check for this.
I've done it this way for now, but are there better more efficient ways?
Select DataTableName
from tbl_DataTableMaster
Where DataTableName in
(select DataTableName
from tbl_DataTablemaster
Where SupplierID = 3202) AND
DataTableName in
(select DataTableName
from tbl_DataTableMaster
Where SupplierID = 235)
This gives me the results I want, but what about outer joins, or are any
other ways? It's not a very large table & it's not a query that will be run
often. I'm just back to work recently and I want to learn the right way, no
t
just one that works.
Thanks for your help.Lesley

> Select DataTableName
> from tbl_DataTableMaster
> Where DataTableName in
> (select DataTableName
> from tbl_DataTablemaster
> Where SupplierID = 3202) AND
> DataTableName in
> (select DataTableName
> from tbl_DataTableMaster
> Where SupplierID = 235)
SELECT DataTableName FROM
DataTablemaster WHERE SupplierID = 235 OR SupplierID = 3202
"Lesley" <Lesley@.discussions.microsoft.com> wrote in message
news:B6F2D6DB-7F7C-4F38-8041-7123D1C70A56@.microsoft.com...
> Hello everyone,
> Haven't used MSSQL in a while and I have a question about this query:
> Here is what I need to achieve:
> Basically I need to know if the same DataTableName exists for two
> suppliers.
> In this table there are two fields of concern: DataTableName, SupplierID.
> The process is that when requested I change the supplierId to another ID
> to
> move the dataTable to another supplier. I need to check that it won't
> create
> a duplicate. There are no database constraints in place to check for
> this.
> I've done it this way for now, but are there better more efficient ways?
> Select DataTableName
> from tbl_DataTableMaster
> Where DataTableName in
> (select DataTableName
> from tbl_DataTablemaster
> Where SupplierID = 3202) AND
> DataTableName in
> (select DataTableName
> from tbl_DataTableMaster
> Where SupplierID = 235)
>
> This gives me the results I want, but what about outer joins, or are any
> other ways? It's not a very large table & it's not a query that will be
> run
> often. I'm just back to work recently and I want to learn the right way,
> not
> just one that works.
> Thanks for your help.|||DUH (that DUH was for me)
Thanks, apparently my brain isn't thinking in simple mode.
"Uri Dimant" wrote:

> Lesley
>
>
> SELECT DataTableName FROM
> DataTablemaster WHERE SupplierID = 235 OR SupplierID = 3202
>
>
> "Lesley" <Lesley@.discussions.microsoft.com> wrote in message
> news:B6F2D6DB-7F7C-4F38-8041-7123D1C70A56@.microsoft.com...
>
>|||If I understand you correctly you want to check for duplicates?
then do like this
SELECT COL1,COL2 FROM TABLE GROUP BY COL1,COL2 HAVING COUNT(*)>1
However
Now it is time to have constraints and design properly.
Regards
R.D
"Lesley" wrote:

> Hello everyone,
> Haven't used MSSQL in a while and I have a question about this query:
> Here is what I need to achieve:
> Basically I need to know if the same DataTableName exists for two supplier
s.
> In this table there are two fields of concern: DataTableName, SupplierID.
> The process is that when requested I change the supplierId to another ID t
o
> move the dataTable to another supplier. I need to check that it won't cre
ate
> a duplicate. There are no database constraints in place to check for thi
s.
> I've done it this way for now, but are there better more efficient ways?
> Select DataTableName
> from tbl_DataTableMaster
> Where DataTableName in
> (select DataTableName
> from tbl_DataTablemaster
> Where SupplierID = 3202) AND
> DataTableName in
> (select DataTableName
> from tbl_DataTableMaster
> Where SupplierID = 235)
>
> This gives me the results I want, but what about outer joins, or are any
> other ways? It's not a very large table & it's not a query that will be r
un
> often. I'm just back to work recently and I want to learn the right way,
not
> just one that works.
> Thanks for your help.|||Sorry, that won't solve my problem.
That will return every datatablename for either supplierID - not where the
Datatablenames are the same.
Any other thoughts?
"Uri Dimant" wrote:

> Lesley
>
>
> SELECT DataTableName FROM
> DataTablemaster WHERE SupplierID = 235 OR SupplierID = 3202
>
>
> "Lesley" <Lesley@.discussions.microsoft.com> wrote in message
> news:B6F2D6DB-7F7C-4F38-8041-7123D1C70A56@.microsoft.com...
>
>|||That's what I'm looking for.
Yes I agree, the database should check for constraints - but I'm not the DBA
& I don't think this constraint would always hold true. This application
has been around for a LONG time.
Thanks!
"R.D" wrote:
> If I understand you correctly you want to check for duplicates?
> then do like this
> SELECT COL1,COL2 FROM TABLE GROUP BY COL1,COL2 HAVING COUNT(*)>1
> However
> Now it is time to have constraints and design properly.
> Regards
> R.D
>
> "Lesley" wrote:
>|||OK I need to think before I type. Can you delete responses on this forum?
I don't think that method will work either. Yes it will find existing
duplicates. But I need to find determine if duplicates will occur after I
change the supplierID field.
Thanks.
"R.D" wrote:
> If I understand you correctly you want to check for duplicates?
> then do like this
> SELECT COL1,COL2 FROM TABLE GROUP BY COL1,COL2 HAVING COUNT(*)>1
> However
> Now it is time to have constraints and design properly.
> Regards
> R.D
>
> "Lesley" wrote:
>|||Lesley
can you be specific what exactly you want to do.
1. You want check for duplicates after inserting? But it should insert ?
that is what are you looking for then, you got to use triggers
CREATE TRIGGER <TRIGGERNAME>
ON <TABLE NAME>
AFTER INSERT, UPDATE
IF EXISTS (SELECT COL1,COL2 FROM TABLE GROUP BY COL1,COL2 HAVING COUNT(*)>1)
BEGIN
RAISE ERROR('DUPLICATES INSERTED',16,1)
-- OR YOU CAN INSERT THAT ROW INTO TEMP TABLE
END
"Lesley" wrote:
> OK I need to think before I type. Can you delete responses on this forum
?
> I don't think that method will work either. Yes it will find existing
> duplicates. But I need to find determine if duplicates will occur after I
> change the supplierID field.
> Thanks.
>
> "R.D" wrote:
>|||I'm not inserting I'm just changing the supplierID. I want to be sure that
when I change the supplierID it won't create a duplicate. If it will create
a duplicate I'm creating a message that give the potential duplicate
DataTableName to the User. This piece is a small part of a transaction, if
a
duplicate will be created I'm rolling back any changes that have occured
within the transaction. So the user can proceed as they wish.
Does that clear things up or make it worse?
Thanks for your help.
"R.D" wrote:
> Lesley
> can you be specific what exactly you want to do.
> 1. You want check for duplicates after inserting? But it should insert ?
> that is what are you looking for then, you got to use triggers
> CREATE TRIGGER <TRIGGERNAME>
> ON <TABLE NAME>
> AFTER INSERT, UPDATE
> IF EXISTS (SELECT COL1,COL2 FROM TABLE GROUP BY COL1,COL2 HAVING COUNT(*)>
1)
> BEGIN
> RAISE ERROR('DUPLICATES INSERTED',16,1)
> -- OR YOU CAN INSERT THAT ROW INTO TEMP TABLE
> END
>
>
> "Lesley" wrote:
>|||You can use check constriants and disable when ever you want. However
previous trigger still works for update.
Regards
R.D
"Lesley" wrote:
> I'm not inserting I'm just changing the supplierID. I want to be sure th
at
> when I change the supplierID it won't create a duplicate. If it will crea
te
> a duplicate I'm creating a message that give the potential duplicate
> DataTableName to the User. This piece is a small part of a transaction, i
f a
> duplicate will be created I'm rolling back any changes that have occured
> within the transaction. So the user can proceed as they wish.
> Does that clear things up or make it worse?
> Thanks for your help.
> "R.D" wrote:
>