Databases
StackOverflow2013
Postsdbo
POVery complicated TSQL with INNER JOIN the same table twice
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POVery complicated TSQL with INNER JOIN the same table twice
    primarykey
    data
    text
    <p>Let's say I have 2 tables (in fact there are more in this command, but only those affect question).</p> <p>Table A have unique Accounts and Table C have Unique Materials.</p> <p>Now lets say Unique Account with ID 11 can have few Unique Materials (ex.: 10, 18, 19, 24).</p> <p>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).</p> <p>Every Material have id of Account in dedicated column.</p> <p>Now I need to select some of the values into DataTable based on the input Strings criteria:</p> <pre><code>String1 = Afst; //some value from tableA //... String2 = Ctype; // SomeType of Material from table C String3 = dn; // number of Material from table C </code></pre> <p>Afst (tableA) |...| dn (tableC number of Ctype) | dn2 (table C number of Type2)</p> <p>Where dn2 have specified type.</p> <p>The problem here is that I need to select all materials with Ctype even if Type2 doesn't exist for this Account.</p> <p>For example this will give all results when both Ctype and Type2 exists in :</p> <pre><code>("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'"); </code></pre> <p>Notice: I'm not allowed to change database tables in any way, so I can't split Material table into tables by type.</p> <p>This should also be within single TSQL command.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAlt
    1. USAlt
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POVery complicated TSQL with INNER JOIN the same table twice
      1. This table or related slice is empty.
      1. VTDownMod
    1. CODo 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.)
      singulars
      1. POVery complicated TSQL with INNER JOIN the same table twice
      1. USMichael Kjörling
    2. COI'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
      singulars
      1. POVery complicated TSQL with INNER JOIN the same table twice
      1. USrs'
    3. COPut some effort in the question. Even with edits this is hard to follow. Put the actual strings in the query.
      singulars
      1. POVery complicated TSQL with INNER JOIN the same table twice
      1. USpaparazzo
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload