-1

Let's say I have 2 tables (in fact there are more in this command, but only those affect question).

Table A have unique Accounts and Table C have Unique Materials.

Now lets say Unique Account with ID 11 can have few Unique Materials (ex.: 10, 18, 19, 24).

Materials can be of the same type or different (ex.: 10 - type2, 18 - type2, 19 type1, 24 type3) and they have their numbers (ex.: 10 - A24, 18 - A25, 19 - A24, 24 - B55) and many other values (but for example 1 is enough).

Every Material have id of Account in dedicated column.

Now I need to select some of the values into DataTable based on the input Strings criteria:

String1 = Afst; //some value from tableA
//...
String2 = Ctype; // SomeType of Material from table C
String3 = dn; // number of Material from table C

Afst (tableA) |...| dn (tableC number of Ctype) | dn2 (table C number of Type2)

Where dn2 have specified type.

The problem here is that I need to select all materials with Ctype even if Type2 doesn't exist for this Account.

For example this will give all results when both Ctype and Type2 exists in :

("SELECT a.id, a.Afst, c.dn, d.dn as 'dn2' FROM sometableA a INNER JOIN sometableC c ON a.id=c.idofa INNER JOIN sometableC d ON a.id=d.idofa WHERE a.Afst like '%" + String1 + "%' and c.MaterialType='" + String2 + "' and c.dn like '%" + String3 + "%' and d.MaterialType='2'");

Notice: I'm not allowed to change database tables in any way, so I can't split Material table into tables by type.

This should also be within single TSQL command.

Alt
  • 58
  • 7
  • Do correlated subqueries or joins against selects against the same table count as a "single TSQL command" (actually it's called query)? They are expensive performance-wise, but this sounds like the kind of problem they do solve nicely. (And given that you are already using both `LIKE` with wildcards and dynamic SQL, it doesn't seem that performance is top among your requirements.) – user Jun 01 '13 at 13:06
  • I'm not quite clear what you need based on your post but it looks like you want to return rows that don't match on the join? In that case look at LEFT / RIGHT joins instead of INNER also the example query you posted doesn't really correspond to your detailed explanation, if you can post the schemas that would help – rs' Jun 01 '13 at 13:14
  • Put some effort in the question. Even with edits this is hard to follow. Put the actual strings in the query. – paparazzo Jun 01 '13 at 13:19

1 Answers1

1

I'm not shure having understood correct, perhaps the first join has to be inner

SELECT a.id, a.Afst, c.dn, d.dn as 'dn2'
  FROM sometableA a
  LEFT JOIN sometableC c ON a.id=c.idofa  and c.MaterialType = 'String2' and c.dn like '%String3%'
  LEFT JOIN sometableC d ON a.id=d.idofa  and d.MaterialType= '2'
  WHERE a.Afst like '%String1%'
bummi
  • 27,123
  • 14
  • 62
  • 101
  • Thanks. For my case in your statement first LEFT JOIN should be INNER and only second one LEFT as you predicted :D – Alt Jun 01 '13 at 13:55