|
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.
Table1
---------------
Field1 -Field2
3 -55
3 -55
4 -55
5 -55
3 -66
3 -66
3 -77
4 -77
3 -88
Table2
---------------
Field1 -Field2
3 -9
4 -10
5 -11
|
|
|
|
|
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.
That was useful info.
Is it something like this you need?
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
)
;
This query might be very slow if the tables are big.
which can be cured with composite indexes on both tables.My postings are a natural product. The slight variations in spelling and grammar enhance their individual character and beauty and are in no way to be considered flaws or defects.
|
|
|
|
|
Hy,
Supose we have this:
SELECT SUM(ProdGood) as TotalProduced
From ProductionData
Now if ProdGood is defined as int(eger) will SUM(ProdGood) be also integer
or will it go to BigInt if the SUM is lager than the largest int?
Thanks in advance.
Oh, I tried google but nothig relevant came out.modified on Sunday, February 28, 2010 2:14 AM
|
|
|
|
|
That's an excellent question!
I did some searching and couldn't find a definitive answer, either. Have you tried forcing a too large result using test data yet? It would be worthy of an article to explore this further."A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
I didn't have enough but i created a dummy test table and the output is
"Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int."
So no it does not modify the underling field automatically.
|
|
|
|
|
As you found out the data types do not change to meet the needs of the content. If you think about it this would be a disaster in any system that used even minimal data typing. VB could probably handle it
Another interesting one is trying to work out the result type when you multiple an integer by a decimal and divide it by a float and add a numeric Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I've done it!
The Result is of type System.Terror
|
|
|
|
|
I have the following Table Structure in a SQL 2008 database:
ProductCategory
ProdCat_ID Int PK
Name VarChar(30)
Product
Product_ID Int PK
ProdCat_ID Int FK
...
WorkOrder
WO_ID Int PK
Product_ID Int FK
...
Production
Prodn_ID Int PK
WO_ID Int FK
...
MaterialTransaction
MatTrans_ID Int PK,
TransDate Date,
Prodn_ID FK,
Qty Decimal(18,7)
...
I want to retrieve Qty data for specified Category Names on each date in the query.
If I do the following:
SELECT Distinct(mt.TransDate) As [Date],
CASE WHEN pc.Name IN('Hadware','Custom')
THEN SUM(mt.Qty)
ELSE 0 END AS 'Hardware',
CASE WHEN pc.Name LIKE 'DS %'
THEN SUM(mt.Qty)
ELSE 0 End As 'DataStrip'
From MaterialTransaction mt
JOIN Production pr
ON mt.Prodn_ID = pr.Prodn_ID
JOIN WorkOrder wo
ON pr.WO_ID = wo.WO_ID
JOIN Product p
ON wo.Product_ID = p.ID
JOIN ProductCategory pc
ON p.ProdCat_ID = pc.ID
WHERE Date Between '11/24/2009' And '11/25/2009'
GROUP BY mt.TransDate, pc.Name
I end up with results similar to:
OUTPUT:
Date Hardware DataStrip
2009-11-24 00:00:00 0.0000000000 560.4080000
2009-11-24 00:00:00 2786.3100000 0.0000000
2009-11-25 00:00:00 0.0000000 125.5415000
What I require however is:
Date Hardware DataStrip
2009-11-24 00:00:00 2786.310000000 560.4080000
2009-11-25 00:00:00 0.0000000 125.4150000
Any pointers are most appreciated.I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
I have used the following correlated query:
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
It works as required, but boy is it ugly. Can this be optimized further?
<edit> The final query (with 8 sub-queries) was running rather slowly - ~8 secs to retreive 30 rows from around 4000. An nonclustered index on MaterialTransaction.TransDate sped this up to ~1 sec <edit>I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
modified on Saturday, February 27, 2010 11:33 PM
|
|
|
|
|
AFAIK joins don't need explicit "ON" clauses if the corresponding fields carry the same names, so ON prr.WO_ID = woo.WO_ID could be omitted, and others after a field name change too.
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that. All Toronto weekends should be extremely wet until we get it automated in regular forums, not just QA.
|
|
|
|
|
Luc Pattyn wrote: AFAIK joins don't need explicit "ON" clauses if the corresponding fields carry the same names
Erk! what happens if you have fields of the same name and you don't want them joined? Or am I misunderstanding your point.Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Andy_L_J wrote: Can this be optimized further?
Probably but not by the query structure, I have these regularly, as long as it is a transactional type system (as opposed to a batch system)and you are not dealing with 1000s of results there should not be too much pain.
Ugly, Pfft, its less than 40 line, has only 2 sub selects, doesn't qualify for ugly.Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks Mycroft.
The final query has 8 subs and does the job. Initially a little slowly until I placed an index on the TransDate field. I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
SELECT mt.TransDate,
SUM(CASE WHEN pc.Name IN('Hadware','Custom')
THEN mt.Qty
ELSE 0 END) AS 'Hardware',
SUM(CASE WHEN pc.Name LIKE 'DS %'
THEN mt.Qty
ELSE 0 End) As 'DataStrip'
From MaterialTransaction mt
JOIN Production pr
ON mt.Prodn_ID = pr.Prodn_ID
JOIN WorkOrder wo
ON pr.WO_ID = wo.WO_ID
JOIN Product p
ON wo.Product_ID = p.ID
JOIN ProductCategory pc
ON p.ProdCat_ID = pc.ID
WHERE Date Between '11/24/2009' And '11/25/2009'
GROUP BY mt.TransDate
|
|
|
|
|
Cheers - Nice
I am testing for speed against the other query.I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
Would anyone happen to know of any public web pages or documents, by respected authorities in the field that would definitively convince a product owner that creating tens of thousands of databases in a single SQL Server is not a reasonable thing to do? In addition, having Joe-blow users creating and deleting databases is not a good idea, i.e. these are administrator only tasks.
The product in question is an update of a previous product where there was a single ‘database’ per profile. Only in the old version, the ‘database’ was just a single file with no constraint checking.
|
|
|
|
|
I ran across this type of system in the 90s, app was build on system5 or some such crap. The owner wanted exactly the same thing written in Access or SQL server, same functionality and everything. When I flatly refused to even discuss the solution and offered to walk away from the contract he saw the light.
Seriously if you cannot move him off this idiotic design, quit, move on your stress levels will appreciate it.Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Thanks for the link to the great article i.j.russell, I'm still reading and learning.
Ah, I see, my description of the question wasn't very good. I should expand a bit on what I mean by profile. A better description would be an item where multiple, think thousands or tens of thousands, of these items are associated with a single tenant.
The new software being developed copied a lot of code from the old version where an item, single file, was represented as a database using an API provided by an antiquated database product.
The new software is trying to use this same scheme in SQL Server, one database per item. Since there can be tens of thousands of items, there will be tens of thousands of databases. I can hear the murderous laughter of database administrators everywhere.
I think this scheme breaks a number of well-established guidelines but I’d like to give the product owner ammunition, concrete definitive documentation, to force the developers to change the product to something more reasonable. (Oh, and I’m one of the developers – just showing up late in the development cycle of the product.) The product owner is not very technical and so may have been convinced for some reason or other that this was an okay solution.
|
|
|
|
|
The other option to consider is going the NoSql route and using something like MongoDb, CouchDb or even cloud-based storage like Microsoft Azure Table Storage or Amazon SimpleDb.
|
|
|
|
|
Good options, though I'm pretty sure they are going to use SQL Server no matter what. The customers of the product are typically Microsoft shops so they really want SQL Server.
I just can't imagine how the developers convinced the product owner that creating tens of thousands of databases would be okay.
|
|
|
|
|
|
Exactly, 'technically possible' but a horrendous idea. I'm guessing this is how development 'convinced' the product owner.
Any good web pages to convince them that this is a ridiculous idea?
|
|
|
|
|
I can't find any sites that would help, but that in itself is helpfull because it means that the concept is either uber-cutting edge or plain stupid. Since Sql Server has been around for well over a decade, I am going for the latter.
From a technical perspective, I can think of a few issues;
1. Your hardware requirements are going to be well in excess of what your application data should need. Your data should be easily handled by a single Sql Server Express database.
2. Connection pooling is going to be interesting as each database will have a different connection string. Performance will be adversely affected as new connections are created.
3. Whilst you can carry out DDL in a stored procedure to create a database, table(s) and indexes, this will take a few seconds each time.
|
|
|
|
|
I'd go with i.j's plain stupid scenario, this is so far outside sensible that it would not be entertained by any reasonable designer.
Consider a code base change to the database, maintenance will be an absolute nightmare and the cost of maintaining an app is much higher than developing the app.Never underestimate the power of human stupidity
RAH
|
|
|
|