Database

-- Table
SELECT A.Town, B.Region, C.Attribute AS AttribCode, D.Attribute 
FROM TableA A 
INNER JOIN TableB B 
ON A.ID = B.ID 
INNER JOIN TableC C ........

--or 
-- Concatenate
SELECT A.Town + ISNULL(B.Region, '') + ISNULL(C.Attribute, '') + ISNULL(D.Attribute, '') AS TownInfo
FROM TableA A 
INNER JOIN TableB B 
ON A.ID = B.ID 
INNER JOIN TableC C ........

pebrian27 wrote:

but I will limit the result set to 10K max

pebrian27 wrote:

Query can take minutes even hours

Mycroft Holmes wrote:

pebrian27 wrote:
Query can take minutes even hours
This does not make sense when servicing a UI. A select statement that takes hours, ridiculous, your design is screwed.

SELECT Field2 FROM TABLE1<br />
WHERE Field1 = (SELECT Field1 FROM Table2 WHERE Field2 = 9)<br />
AND Field2 IN(66,88)

Mike654321 wrote:

55 is excluded because it has a link of 4 and 5...and 77 is excluded because it has a link of 4 in it.

SELECT  Table1.field2
FROM    Table1 join Table2
    ON  Table1.field1 = Table2.field1
WHERE   Table2.field2 = 9
AND Table1.field2 NOT IN 
    (
    SELECT  DISTINCT Table1.field2
    FROM    Table1 join Table2
        ON  Table1.field1 = Table2.field1
    WHERE   Table2.field2 <> 9
    )
;

"Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int."

SELECT mt.TransDate, CASE ISNULL(h.Hardware,0)
                      WHEN 0 Then 0
                      ELSE h.Hardware
                      End As Hardware, 
                     CASE ISNULL(d.Datastrip, 0)
                      WHEN 0 Then 0
                      ELSE d.Datastrip
                      END AS Datastrip
  FROM MaterialTransaction mt
    LEFT JOIN (SELECT mtt.TransDate,SUM(mtt.Qty) As Hardware
            FROM MaterialTransaction mtt
              JOIN Production prr
                ON mtt.Prodn_ID = prr.Prodn_ID
              JOIN WorkOrder woo
                ON prr.WO_ID = woo.WO_ID
              JOIN Product pp
                ON woo.Product_ID = pp.ID
              JOIN ProductCategory pcc
                ON pp.ProdCat_ID = pcc.ID
            WHERE pcc.Name In ('Hardware','Custom', 'Cellular')    
            GROUP BY mtt.TransDate) h
      ON mt.TransDate = h.TransDate
    LEFT JOIN (SELECT mtt.TransDate, SUM(mtt.Qty) As Datastrip
            FROM MaterialTransaction mtt
              JOIN Production prr
                ON mtt.Prodn_ID = prr.Prodn_ID
              JOIN WorkOrder woo
                ON prr.WO_ID = woo.WO_ID
              JOIN Product pp
                ON woo.Product_ID = pp.ID
              JOIN ProductCategory pcc
                ON pp.ProdCat_ID = pcc.ID
            WHERE pcc.Name In ('DS PVC','DS PETG')    
            GROUP BY mtt.TransDate) d
          ON h.TransDate = d.TransDate
    WHERE mt.TransDate BETWEEN '11/24/2009' And '11/25/2009'
    GROUP BY mt.TransDate, h.Hardware, d.Datastrip