EMAIL PROTECTED]
Sent: Friday, January 11, 2008 10:51 AM
To: CF-Talk
Subject: Re: A Very Sophisticated Query
That is correct, the sorting essentially occurs on the dataset returned by
the query. You can sort on any of the columns in the select stat
That is correct, the sorting essentially occurs on the dataset returned by
the query. You can sort on any of the columns in the select statement.
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
11, 2008 10:02 AM
> To: CF-Talk
> Subject: Re: A Very Sophisticated Query
>
> *Correction on #2*
> SELECT BinD.Transaction_ID,
> BinD.Bin_ID,
> Bin.Bin_Name,
> BinD.Serial_ID,
> Serial.Serial_Name,
> BinD.Bin_Quantity
> FROM BinD
> INNER
AM
To: CF-Talk
Subject: Re: A Very Sophisticated Query
*Correction on #2*
SELECT BinD.Transaction_ID,
BinD.Bin_ID,
Bin.Bin_Name,
BinD.Serial_ID,
Serial.Serial_Name,
BinD.Bin_Quantity
FROM BinD
INNER JOIN Bin ON BinD.Bin_ID = Bin.Bin_ID
INNER JOIN Serial ON
*Correction on #2*
SELECT BinD.Transaction_ID,
BinD.Bin_ID,
Bin.Bin_Name,
BinD.Serial_ID,
Serial.Serial_Name,
BinD.Bin_Quantity
FROM BinD
INNER JOIN Bin ON BinD.Bin_ID = Bin.Bin_ID
INNER JOIN Serial ON BinD.Serial_ID = Serial.Serial_ID
WHERE BinD.Serial_ID = 2
ORDER BY B
Tell me if this is what you're looking for:
1) *BinD Records For a Transaction, most recent BinD first:*
SELECT BinD.BinD_ID,
BinD.Bin_ID,
Bin.Bin_Name,
BinD.Serial_ID,
Serial.Serial_Name,
BinD.Bin_Quantity
FROM BinD
INNER JOIN Bin ON BinD.Bin_ID = Bin.Bin_ID
INNER JOIN
For those follwoing this thread:
I was led to this solution by Matt... this is the final form used in the
application so the table names are not the same but close if you want to
inspect the answer. The JOIN (...)PBinD2 is on a temp result set. He
referred to this as an In-Line View that was joi
Yes!
http://dev.mysql.com/doc/refman/6.0/en/example-maximum-column-group-row.html
That is the sql that Matt Williams suggested to me offline earlier this
morning and I have been trying to wrap my head around it... as Brad
described earlier in this thread ;-) And studying what Dominic has
sugges
Gonzo Rock wrote:
> 1st task - Given a Lot_ID, query the most recent BinD_Quantities recorded
> for each of the Serial_ID's in the Lot.
The MySQL manual has a section named "Groupwise Maximum". It presents 2
solutions, one of which will also work on other databases.
Jochem
~
Task 2:
Assuming @SerialID holds the serial id in question,
SELECT Bin_ID, Serial_ID, Bin_Quantity
FROM BinD
WHERE EXISTS (SELECT BinD_ID FROM BinD bd2 WHERE bd2.transactionID >
BinD.transactionID)
Does this do what you want?
Dominic
~~~
>
> Task 2:
>
> Assuming @SerialID holds the serial id in question,
>
> SELECT Bin_ID, Serial_ID, Bin_Quantity
> FROM BinD
> WHERE EXISTS (SELECT BinD_ID FROM BinD bd2 WHERE bd2.transactionID >
> BinD.transactionID)
>
> Does this do what you want?
>
> Dominic
>
Woops, forgot the serialID bit!
SE
Ok, task 1:
SELECT bd.Serial_ID, s.Lot_ID, Sum(bd.Bin_Quantity) as qty
FROM BinD bd
INNER JOIN Serial s ON s.Serial_ID = bd.Serial_ID
INNER JOIN Transaction t ON t.Transaction_Id = bd.TransactionID
WHERE t.date BETWEEN date1 AND date2 -- This is made up, I assume it is
where the date is stored
GR
Gonzo, I've got to say I've had a time trying to wrap my head around
your data structure without really knowing how it is populated. I have
generated some SQL here, but I don't really know if I have interpreted
the data correctly. For starters are there only 4 bins total, or does
EACH serial have
13 matches
Mail list logo
