Clicky

Hello EE,

I am having trouble to think how I am going to do this.
By the way I dont mind that answer to that question would be in .NET as a String Query inside SQL (if possible)

I have a table like this :

ID
Key1
Key2
Key3
TableName

For example, lets say we have :

ID               Key1                 Key2                  Key3        TableName
1                  1                       null                    null              TableA

that means I need to build a String and say:   Select * From TableA WHERE TableA.Code NOT IN ( 1 )

if we had :

ID               Key1                 Key2                  Key3        TableName
2                  AA1                    1                        null           TableB
3                  AA1                    2                        null           TableB

(meaning TableB has 2 PrimaryKeys)
that means I need to build a string and say: Select * from TABLE B WHERE (TableB.Code <> AA1 AND TableB.Code2 <> 1)  AND (TableB.Code <> AA1 AND TableB.Code2 <> 2)

it can go max 3 primaryKeys..

any idea how to do that ?

asked 12/15/2011 01:12

PhilippeRenaud's gravatar image

PhilippeRenaud ♦♦


4 Answers:
You must explain better what you need.

But try this query :
TableA
id, name, flag

select * from tableA a where a.id not in(select id from tableA where flag = 0)

Or, if you have a second table,

TableA
id,name,flag
TableB
id,feature1,feature2, idTableA

select A.* from TableA A left outer join
  TableB on A.id = B.idtableA
where b.idtableA is null and A.flag = 1

I hope this help!
link

answered

DALSOM's gravatar image

DALSOM

Well ok :

in .NET I will create a  variable   myTable  as DataTable.

I will return in mYTable the results of my SQL table example.

I need to away to read myTable so that it build a new variable String that will Exclude in a Where clause all the primary keys.

But If the ID of the table has only one primary Key (key1 is filled and key2,key3 is NULL) I will do something like where not in ( data_of_key1)

if the ID has more than only key1, so If key1 and key2 are filled, or even key3 I cannot write Where NOT IN because it has more than 1 PK so I need to string to be like:

WHERE ColumnPrimaryKey1 <> data_of_key1  AND ColumnPrimaryKey2 <> data_of_key2  etc...


Hope its better...
link

answered 2011-12-15 at 10:22:14

PhilippeRenaud's gravatar image

PhilippeRenaud

Ok, I got the idea.

You can use both of queries, but first one is easiest to understand.
Check :

TableA ' This is the table of the selected values.
ID
Key1
Key2
Key3
TableName

TableB
ID               Key1                 Key2                  Key3        TableName
2                  AA1                    1                        null           TableB
3                  AA1                    2                        null           TableB
4 ..  fill the remaining fields by me please.
5 ..

select A.* from tableA A where A.id not in(select distinct id from tableB)

Then the full string you must build is like above!

This is it!!

Hope had help you!
link

answered 2011-12-15 at 10:28:17

DALSOM's gravatar image

DALSOM

thanks
link

answered 2011-12-15 at 10:38:47

PhilippeRenaud's gravatar image

PhilippeRenaud

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×132
×3
×19

Asked: 12/15/2011 01:12

Seen: 285 times

Last updated: 12/15/2011 03:03