|
Thread Rules
1. This is not a "do my homework for me" thread. If you have specific questions, ask, but don't post an assignment or homework problem and expect an exact solution.
2. No recruiting for your cockamamie projects (you won't replace facebook with 3 dudes you found on the internet and $20)
3. If you can't articulate why a language is bad, don't start slinging shit about it. Just remember that nothing is worse than making CSS IE6 compatible.
4. Use [code] tags to format code blocks. |
On December 30 2010 06:23 mmp wrote:Show nested quote +On December 30 2010 06:15 Craton wrote:On December 30 2010 06:01 mmp wrote:
Craton, do you just want to pull out the first row after the ordering? It's a little unclear from what you've presented what your query is intending to do (I'm just a PHP amateur). Right now I get something like:
item_id | item_name | misc. columns | Checked Out By
1 | C# Reference Book | < etc etc etc > | Joe Smith
1 | C# Reference Book | < etc etc etc > | Billy Bob
It's the same book, but it has been checked out (and back in) by Billy Bob and then checked out again by Joe Smith. Actually now that I think about I probably just need a where clause about the check-in date being null. Of course I can't test that because someone broke the DB server again, but it should be sufficient. You want the latest checkout, yes? Why don't you keep a key column that auto-increments the transaction number, so you can order by the last transaction? Or does your check-out date effectively do something like that? Correct me if I'm misreading your intent.
The checkout date should do what I need. The checkout table holds the checkouts (or ins) for every item, as well. Seems unnecessary to add anything to the existing table structure.
|
On December 30 2010 06:32 Craton wrote:Show nested quote +On December 30 2010 06:23 mmp wrote:On December 30 2010 06:15 Craton wrote:On December 30 2010 06:01 mmp wrote:
Craton, do you just want to pull out the first row after the ordering? It's a little unclear from what you've presented what your query is intending to do (I'm just a PHP amateur). Right now I get something like:
item_id | item_name | misc. columns | Checked Out By
1 | C# Reference Book | < etc etc etc > | Joe Smith
1 | C# Reference Book | < etc etc etc > | Billy Bob
It's the same book, but it has been checked out (and back in) by Billy Bob and then checked out again by Joe Smith. Actually now that I think about I probably just need a where clause about the check-in date being null. Of course I can't test that because someone broke the DB server again, but it should be sufficient. You want the latest checkout, yes? Why don't you keep a key column that auto-increments the transaction number, so you can order by the last transaction? Or does your check-out date effectively do something like that? Correct me if I'm misreading your intent. The checkout date should do what I need. The checkout table holds the checkouts (or ins) for every item, as well. Seems unnecessary to add anything to the existing table structure.
So you're retrieving from one table and sorting it with another? Sounds doable but messy to hand code in your language of choice, but I'm going to dodge this since SQL is not my area of expertise. Maybe someone else can help produce a simple query.
|
On December 30 2010 06:26 Qzy wrote:
Anyone want to discuss Project euler problems? Not solutions, but rather discuss a problem in general. I'm level 2 euler, near question 60-80 I believe.
Which problems? I haven't done very many but I do lots of topcoder contests and almost went to world finals for ICPC this year, so I like wasting my time on these weird algorithms :D
Edit 1: Went ahead and did a few problems, almost to level 1 I think? Skipped ahead a lot though. 122 looks interesting and the solution isn't jumping out at me right now so I'm gonna work on it for a little bit.
http://projecteuler.net/index.php?section=problems&id=122 For those of you interested
|
On December 30 2010 05:54 Craton wrote:Is there way to select the 'top 1' of a particular category in a complex join? SELECT DISTINCT itm.*, ua.lastName + ', ' + ua.firstName as responsible_party, itmstatref.item_status_ref_name as item_status_ref_name,
itmtyperef.item_type_name as item_type_name, uac.lastName + ', ' + uac.firstName as checked_out_by
FROM ITEM itm INNER JOIN USERACCOUNTS ua ON ua.userUID=itm.userUID
INNER JOIN ITEM_STATUS itmstat ON itm.item_id=itmstat.item_id
INNER JOIN ITEM_STATUS_REF itmstatref ON itmstat.item_status_ref_id=itmstatref.item_status_ref_id
INNER JOIN ITEM_TYPE_REF itmtyperef ON itm.item_type_id=itmtyperef.item_type_id
INNER JOIN ITEM_CHECKOUT itmcheckout on itm.item_id=itmcheckout.item_id
INNER JOIN USERACCOUNTS uac on itmcheckout.userUID=uac.userUID
ORDER BY itm.item_id, itm.item_inv_no
I basically only want to get the latest checkout of a given item, if existing, rather than a separate record for each time it was checked out (which is what I'm getting). I feel like I'm missing something obvious.
It would help to see the table structure.
What might help you in this case is a subselect, something like this:
(This query is in no way related to your query and i didn't check the syntax)
SELECT item.item_id,
(
SELECT checkout.item_status
FROM checkout
WHERE checkout.item_id = item.item_id
ORDER BY checkout.date
LIMIT 1
)
FROM item
ORDER BY item.item_id
If you only want items that are not checked back in you can use EXISTS
SELECT item.item_id
FROM item
WHERE EXISTS(
SELECT 1 /* note: whatever you select here is ignored, only the WHERE counts */
FROM checkout
WHERE item.item_id=checkout.item_id
AND checkout.return_date IS NULL
)
http://dev.mysql.com/doc/refman/5.1/en/exists-and-not-exists-subqueries.html
http://dev.mysql.com/doc/refman/5.1/en/subqueries.html
and
http://dev.mysql.com/doc/refman/5.1/en/optimizing-subqueries.html
http://dev.mysql.com/doc/refman/5.1/en/rewriting-subqueries.html
EDIT:
After looking at your query, it looks like Microsofts T-SQL. Shouldn't matter much though as it supports subqueries and EXISTS, too, AFAIR. There might be slight syntax differences, but i can't check atm.
You might also want to test performance with multiple small queries instead of a big join, as in some cases running even 20 small queries can be faster than 1 big query, especially since they are easier to optimize.
|
Well, I think you're right about needing a subquery to do this. Simply looking for nulls ends up skipping all of the items that have been checked back in. I knew that seemed too easy.
I tried
SELECT DISTINCT itm.*, ua.lastName + ', ' + ua.firstName as Responsible_Party,
itmstatref.item_status_ref_name as item_status_ref_name,
itmtyperef.item_type_name as item_type_name,
uac.lastName + ', ' + uac.firstName as checked_out_by,
itmcheckout.item_checkout_dt, itmcheckout.item_checkin_dt
FROM ITEM itm
INNER JOIN USERACCOUNTS ua ON ua.userUID=itm.userUID
INNER JOIN ITEM_STATUS itmstat ON itm.item_id=itmstat.item_id
INNER JOIN ITEM_STATUS_REF itmstatref ON itmstat.item_status_ref_id=itmstatref.item_status_ref_id
INNER JOIN ITEM_TYPE_REF itmtyperef ON itm.item_type_id=itmtyperef.item_type_id
INNER JOIN ITEM_CHECKOUT itmcheckout on itm.item_id=itmcheckout.item_id
INNER JOIN USERACCOUNTS uac on itmcheckout.userUID=uac.userUID
WHERE EXISTS
(
SELECT TOP 1 item_checkin_dt
FROM item_checkout
WHERE itmcheckout.item_id=itm.item_id
ORDER BY item_checkin_dt DESC
)
ORDER BY itm.item_id, itm.item_inv_no
but that doesn't seem to change anything, presumably because of the fact that the earlier joins already grabbed them.
If I change it to:
SELECT DISTINCT itm.*, ua.lastName + ', ' + ua.firstName as Responsible_Party,
itmstatref.item_status_ref_name as item_status_ref_name,
itmtyperef.item_type_name as item_type_name
FROM ITEM itm
INNER JOIN USERACCOUNTS ua ON ua.userUID=itm.userUID
INNER JOIN ITEM_STATUS itmstat ON itm.item_id=itmstat.item_id
INNER JOIN ITEM_STATUS_REF itmstatref ON itmstat.item_status_ref_id=itmstatref.item_status_ref_id
INNER JOIN ITEM_TYPE_REF itmtyperef ON itm.item_type_id=itmtyperef.item_type_id
WHERE EXISTS
(
SELECT TOP 1 item_checkin_dt
FROM item_checkout itmcheckout INNER JOIN USERACCOUNTS uac on itmcheckout.userUID=uac.userUID
WHERE itmcheckout.item_id=itm.item_id
ORDER BY item_checkin_dt desc
)
ORDER BY itm.item_id, itm.item_inv_no
then I go from 77 rows to 43 rows (which seems right), but then I don't seem to have access to the fields inside the nested query. This, ofc, still doesn't address the null problem.
Error:
SELECT DISTINCT itm.*, ua.lastName + ', ' + ua.firstName as Responsible_Party,
itmstatref.item_status_ref_name as item_status_ref_name,
itmtyperef.item_type_name as item_type_name,
itmcheckout.item_checkout_dt, itmcheckout.item_checkin_dt, uac.userUID
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "itmcheckout.item_checkout_dt" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "itmcheckout.item_checkin_dt" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "uac.userUID" could not be bound.
The last three fields are members of the subquery. There seems to be a scope issue, but I'm struggling to find the answer via Google.
Now, this works more closely to what I want, but still not quite there:
SELECT DISTINCT itm.*, ua.lastName + ', ' + ua.firstName as responsible_party,
itmstatref.item_status_ref_name as item_status_ref_name,
itmtyperef.item_type_name as item_type_name, (
SELECT TOP 1 item_checkout_id
FROM item_checkout itmcheckout INNER JOIN USERACCOUNTS uac on itmcheckout.userUID=uac.userUID
WHERE itmcheckout.item_id=itm.item_id
ORDER BY item_checkin_dt desc
) as item_checkout_id
FROM ITEM itm
INNER JOIN USERACCOUNTS ua ON ua.userUID=itm.userUID
INNER JOIN ITEM_STATUS itmstat ON itm.item_id=itmstat.item_id
INNER JOIN ITEM_STATUS_REF itmstatref ON itmstat.item_status_ref_id=itmstatref.item_status_ref_id
INNER JOIN ITEM_TYPE_REF itmtyperef ON itm.item_type_id=itmtyperef.item_type_id
ORDER BY itm.item_id, itm.item_inv_no
http://codepad.org/GGe1QAqh
If I could get the item_checkout_dt and item_checkin_dt instead of just the item_checkout_id I'd be set.
|
On December 30 2010 12:12 huameng wrote:Show nested quote +On December 30 2010 06:26 Qzy wrote:
Anyone want to discuss Project euler problems? Not solutions, but rather discuss a problem in general. I'm level 2 euler, near question 60-80 I believe. Which problems? I haven't done very many but I do lots of topcoder contests and almost went to world finals for ICPC this year, so I like wasting my time on these weird algorithms :D Edit 1: Went ahead and did a few problems, almost to level 1 I think? Skipped ahead a lot though. 122 looks interesting and the solution isn't jumping out at me right now so I'm gonna work on it for a little bit. http://projecteuler.net/index.php?section=problems&id=122 For those of you interested
Send me a message on skype, then we can throw each other messages there  . I always like to do a problem once in a while. PM me for username.
|
EDIT: nvm, past this is old and solved now.
|
+ Show Spoiler +#include<stdlib.h>
#include<stdio.h>
struct node* BuildOneTwoThree() {
struct node* head = NULL;
struct node* second = NULL;
struct node* third = NULL;
head = malloc(sizeof(struct node));
second = malloc(sizeof(struct node));
third = malloc(sizeof(struct node));
head->data = 1;
head->next = second;
second->data = 2;
second->next = third;
third->data = 3;
third->next = NULL;
return head;
}
im studying about linked lists right now. I found this example and tried compiling it but it won't.
"invalid application of 'sizeof' to incomplete type 'node' "
"invalid conversion from 'void*' to 'node*' "
i dont get it. is the example wrong or was it me? (probably the latter lol)
|
On January 03 2011 22:56 icystorage wrote:+ Show Spoiler +#include<stdlib.h>
#include<stdio.h>
struct node* BuildOneTwoThree() {
struct node* head = NULL;
struct node* second = NULL;
struct node* third = NULL;
head = malloc(sizeof(struct node));
second = malloc(sizeof(struct node));
third = malloc(sizeof(struct node));
head->data = 1;
head->next = second;
second->data = 2;
second->next = third;
third->data = 3;
third->next = NULL;
return head;
}
im studying about linked lists right now. I found this example and tried compiling it but it won't. "invalid application of 'sizeof' to incomplete type 'node' " "invalid conversion from 'void*' to 'node*' " i dont get it. is the example wrong or was it me? (probably the latter lol)
You have to include(or write) the definition of struct node before ever using it. Look around the example for code that includes struct and typedef.
|
On January 03 2011 22:56 icystorage wrote:+ Show Spoiler +#include<stdlib.h>
#include<stdio.h>
struct node* BuildOneTwoThree() {
struct node* head = NULL;
struct node* second = NULL;
struct node* third = NULL;
head = malloc(sizeof(struct node));
second = malloc(sizeof(struct node));
third = malloc(sizeof(struct node));
head->data = 1;
head->next = second;
second->data = 2;
second->next = third;
third->data = 3;
third->next = NULL;
return head;
}
im studying about linked lists right now. I found this example and tried compiling it but it won't. "invalid application of 'sizeof' to incomplete type 'node' " "invalid conversion from 'void*' to 'node*' " i dont get it. is the example wrong or was it me? (probably the latter lol)
like r4sc said, define node.
this compiles fine:
+ Show Spoiler +
#include<stdlib.h>
#include<stdio.h>
struct node{
int data;
void *next;
};
struct node* BuildOneTwoThree() {
struct node* head = NULL;
struct node* second = NULL;
struct node* third = NULL;
head = malloc(sizeof(struct node));
second = malloc(sizeof(struct node));
third = malloc(sizeof(struct node));
head->data = 1;
head->next = second;
second->data = 2;
second->next = third;
third->data = 3;
third->next = NULL;
return head;
}
int main(int argc, char** argv){
BuildOneTwoThree();
}
you probably just need to include a node.h from somewhere
|
wow thanks for the replies, i understand now what was missing, thank you very very much.
why would i need node.h?
can i do
+ Show Spoiler +struct node{
int data;
struct node* next;
};
?
|
icystorage:
You shouldn't need to include "Node.h" for a struct definition and for this to compile. Your latest snippet will work. However, if you wish to use the 'node' struct outside of the current file it would be better practice to move the definition elsewhere- such that you can use it in multiple places easier.
For c++ you don't need to put 'struct' every time you use your 'node'. If you're getting errors relating to how 'node' is used, then you may be compiling as c instead of c++. My snippet has a solution that works for both.
If I may, I would like to recommend that you work towards a better variable naming conventions. Taking your example, mixed with bigpet's, this is how we would like to see code in the game industry:
+ Show Spoiler +
typedef struct _IcyNode
{
int m_data;
_IcyNode* m_next;
} IcyNode;
IcyNode* BuildOneTwoThree()
{
const int icyNodeSize = sizeof(IcyNode);
IcyNode* oneNode = static_cast< IcyNode* >(malloc(icyNodeSize));
oneNode->m_data = 1;
IcyNode* twoNode = static_cast< IcyNode* >(malloc(icyNodeSize));
twoNode ->m_data = 2;
IcyNode* threeNode = static_cast< IcyNode* >(malloc(icyNodeSize));
threeNode ->m_data = 3;
oneNode->m_next = twoNode;
twoNode->m_next = threeNode;
threeNode->m_next = NULL;
return oneNode;
}
int main()
{
BuildOneTwoThree();
return 0;
}
|
On January 04 2011 03:25 Phunkapotamus wrote:icystorage: You shouldn't need to include "Node.h" for a struct definition and for this to compile. Your latest snippet will work. However, if you wish to use the 'node' struct outside of the current file it would be better practice to move the definition elsewhere- such that you can use it in multiple places easier. For c++ you don't need to put 'struct' every time you use your 'node'. If you're getting errors relating to how 'node' is used, then you may be compiling as c instead of c++. My snippet has a solution that works for both. If I may, I would like to recommend that you work towards a better variable naming conventions. Taking your example, mixed with bigpet's, this is how we would like to see code in the game industry: + Show Spoiler +
typedef struct _IcyNode
{
int m_data;
_IcyNode* m_next;
} IcyNode;
IcyNode* BuildOneTwoThree()
{
const int icyNodeSize = sizeof(IcyNode);
IcyNode* oneNode = static_cast< IcyNode* >(malloc(icyNodeSize));
oneNode->m_data = 1;
IcyNode* twoNode = static_cast< IcyNode* >(malloc(icyNodeSize));
twoNode ->m_data = 2;
IcyNode* threeNode = static_cast< IcyNode* >(malloc(icyNodeSize));
threeNode ->m_data = 3;
oneNode->m_next = twoNode;
twoNode->m_next = threeNode;
threeNode->m_next = NULL;
return oneNode;
}
int main()
{
BuildOneTwoThree();
return 0;
}
I have a question: why do you use
IcyNode* oneNode = static_cast< IcyNode* >(malloc(icyNodeSize));
instead of
IcyNode* oneNode = (IcyNode*)malloc(icyNodeSize);
|
On January 04 2011 03:25 Phunkapotamus wrote:icystorage: You shouldn't need to include "Node.h" for a struct definition and for this to compile. Your latest snippet will work. However, if you wish to use the 'node' struct outside of the current file it would be better practice to move the definition elsewhere- such that you can use it in multiple places easier. For c++ you don't need to put 'struct' every time you use your 'node'. If you're getting errors relating to how 'node' is used, then you may be compiling as c instead of c++. My snippet has a solution that works for both. If I may, I would like to recommend that you work towards a better variable naming conventions. Taking your example, mixed with bigpet's, this is how we would like to see code in the game industry: + Show Spoiler +
typedef struct _IcyNode
{
int m_data;
_IcyNode* m_next;
} IcyNode;
IcyNode* BuildOneTwoThree()
{
const int icyNodeSize = sizeof(IcyNode);
IcyNode* oneNode = static_cast< IcyNode* >(malloc(icyNodeSize));
oneNode->m_data = 1;
IcyNode* twoNode = static_cast< IcyNode* >(malloc(icyNodeSize));
twoNode ->m_data = 2;
IcyNode* threeNode = static_cast< IcyNode* >(malloc(icyNodeSize));
threeNode ->m_data = 3;
oneNode->m_next = twoNode;
twoNode->m_next = threeNode;
threeNode->m_next = NULL;
return oneNode;
}
int main()
{
BuildOneTwoThree();
return 0;
}
im using C and im still on the basics and still trying to understand how to make linked lists
|
icystorage:
You'll want to define the struct the way that I wrote then. It's easier than having to write "struct node" every time.
uNiGNoRe:
I only use c-style casts if I'm writing only c. In icy's case, since he's using c, and not c++ I shouldn't have used them- and instead just used (IcyNode*) as you showed. In c++ I would still use static_cast because:
1) c-style casting is equivalent to reinterpret_cast, which can create nasty bugs to hunt down.
2) static_cast will align the pointer to the class-type that you define. If IcyNode were a derived class, then any alignment will be maintained. Using a c-style cast in that situation would leave you to the mercy of your compiler to figure out what you wanted.
3) If the return-type for malloc happened to change, you'd be safe knowing that your casts are compile-time checked. Granted, malloc is something you can always count on. However, since you can't count on any other function's return type, it's a convention that should still be followed. There's no sense in writing static_cast for everything that returns "void*" except malloc.
4) c++ style casting should be used if you're writing c++, and c style casting should be used if you're writing c. It's nicer to read and clearly states your intent for your future self, and others.
|
On January 04 2011 10:23 Phunkapotamus wrote:
icystorage:
You'll want to define the struct the way that I wrote then. It's easier than having to write "struct node" every time.
uNiGNoRe:
I only use c-style casts if I'm writing only c. In icy's case, since he's using c, and not c++ I shouldn't have used them- and instead just used (IcyNode*) as you showed. In c++ I would still use static_cast because:
1) c-style casting is equivalent to reinterpret_cast, which can create nasty bugs to hunt down.
2) static_cast will align the pointer to the class-type that you define. If IcyNode were a derived class, then any alignment will be maintained. Using a c-style cast in that situation would leave you to the mercy of your compiler to figure out what you wanted.
3) If the return-type for malloc happened to change, you'd be safe knowing that your casts are compile-time checked. Granted, malloc is something you can always count on. However, since you can't count on any other function's return type, it's a convention that should still be followed. There's no sense in writing static_cast for everything that returns "void*" except malloc.
4) c++ style casting should be used if you're writing c++, and c style casting should be used if you're writing c. It's nicer to read and clearly states your intent for your future self, and others.
i tried it and its better but i dont understand why
EDIT: okay so i added a new function and made it like how you made the list can you check if i did it correctly? the compilation was a succes
this determines the length of the list
+ Show Spoiler +int Length(IcyNode* oneNode) {
IcyNode* current = oneNode;
int count = 0;
while (current != NULL) {
count++;
current = current->m_next;
}
return count;
}
|
On December 30 2010 23:54 Craton wrote:Well, I think you're right about needing a subquery to do this. Simply looking for nulls ends up skipping all of the items that have been checked back in. I knew that seemed too easy. I tried
SELECT DISTINCT itm.*, ua.lastName + ', ' + ua.firstName as Responsible_Party,
itmstatref.item_status_ref_name as item_status_ref_name,
itmtyperef.item_type_name as item_type_name,
uac.lastName + ', ' + uac.firstName as checked_out_by,
itmcheckout.item_checkout_dt, itmcheckout.item_checkin_dt
FROM ITEM itm
INNER JOIN USERACCOUNTS ua ON ua.userUID=itm.userUID
INNER JOIN ITEM_STATUS itmstat ON itm.item_id=itmstat.item_id
INNER JOIN ITEM_STATUS_REF itmstatref ON itmstat.item_status_ref_id=itmstatref.item_status_ref_id
INNER JOIN ITEM_TYPE_REF itmtyperef ON itm.item_type_id=itmtyperef.item_type_id
INNER JOIN ITEM_CHECKOUT itmcheckout on itm.item_id=itmcheckout.item_id
INNER JOIN USERACCOUNTS uac on itmcheckout.userUID=uac.userUID
WHERE EXISTS
(
SELECT TOP 1 item_checkin_dt
FROM item_checkout
WHERE itmcheckout.item_id=itm.item_id
ORDER BY item_checkin_dt DESC
)
ORDER BY itm.item_id, itm.item_inv_no
but that doesn't seem to change anything, presumably because of the fact that the earlier joins already grabbed them. If I change it to:
SELECT DISTINCT itm.*, ua.lastName + ', ' + ua.firstName as Responsible_Party,
itmstatref.item_status_ref_name as item_status_ref_name,
itmtyperef.item_type_name as item_type_name
FROM ITEM itm
INNER JOIN USERACCOUNTS ua ON ua.userUID=itm.userUID
INNER JOIN ITEM_STATUS itmstat ON itm.item_id=itmstat.item_id
INNER JOIN ITEM_STATUS_REF itmstatref ON itmstat.item_status_ref_id=itmstatref.item_status_ref_id
INNER JOIN ITEM_TYPE_REF itmtyperef ON itm.item_type_id=itmtyperef.item_type_id
WHERE EXISTS
(
SELECT TOP 1 item_checkin_dt
FROM item_checkout itmcheckout INNER JOIN USERACCOUNTS uac on itmcheckout.userUID=uac.userUID
WHERE itmcheckout.item_id=itm.item_id
ORDER BY item_checkin_dt desc
)
ORDER BY itm.item_id, itm.item_inv_no
then I go from 77 rows to 43 rows (which seems right), but then I don't seem to have access to the fields inside the nested query. This, ofc, still doesn't address the null problem. Error:
SELECT DISTINCT itm.*, ua.lastName + ', ' + ua.firstName as Responsible_Party,
itmstatref.item_status_ref_name as item_status_ref_name,
itmtyperef.item_type_name as item_type_name,
itmcheckout.item_checkout_dt, itmcheckout.item_checkin_dt, uac.userUID
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "itmcheckout.item_checkout_dt" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "itmcheckout.item_checkin_dt" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "uac.userUID" could not be bound. The last three fields are members of the subquery. There seems to be a scope issue, but I'm struggling to find the answer via Google. Now, this works more closely to what I want, but still not quite there:
SELECT DISTINCT itm.*, ua.lastName + ', ' + ua.firstName as responsible_party,
itmstatref.item_status_ref_name as item_status_ref_name,
itmtyperef.item_type_name as item_type_name, (
SELECT TOP 1 item_checkout_id
FROM item_checkout itmcheckout INNER JOIN USERACCOUNTS uac on itmcheckout.userUID=uac.userUID
WHERE itmcheckout.item_id=itm.item_id
ORDER BY item_checkin_dt desc
) as item_checkout_id
FROM ITEM itm
INNER JOIN USERACCOUNTS ua ON ua.userUID=itm.userUID
INNER JOIN ITEM_STATUS itmstat ON itm.item_id=itmstat.item_id
INNER JOIN ITEM_STATUS_REF itmstatref ON itmstat.item_status_ref_id=itmstatref.item_status_ref_id
INNER JOIN ITEM_TYPE_REF itmtyperef ON itm.item_type_id=itmtyperef.item_type_id
ORDER BY itm.item_id, itm.item_inv_no
http://codepad.org/GGe1QAqhIf I could get the item_checkout_dt and item_checkin_dt instead of just the item_checkout_id I'd be set.
If you are still having issues with this and you are using MS SQL Server 05 (or higher), you could probably do this with a OUTER APPLY. I'm not sure what your schema looks like and I don't have anything here at home to check the syntax with but something like this would let you access the itmcheckout record for the given item id.
SELECT DISTINCT itm.*, ua.lastName + ', ' + ua.firstName as responsible_party,
itmstatref.item_status_ref_name as item_status_ref_name,
itmtyperef.item_type_name as item_type_name,
FROM ITEM itm
INNER JOIN USERACCOUNTS ua ON ua.userUID=itm.userUID
INNER JOIN ITEM_STATUS itmstat ON itm.item_id=itmstat.item_id
INNER JOIN ITEM_STATUS_REF itmstatref ON itmstat.item_status_ref_id=itmstatref.item_status_ref_id
INNER JOIN ITEM_TYPE_REF itmtyperef ON itm.item_type_id=itmtyperef.item_type_id
OUTER APPLY
(
SELECT ISNULL(MAX(itmcheckout.item_checkin_dt), 'somevalueyouwantifthereisnocheckin')
FROM item_checkout itmcheckout INNER JOIN USERACCOUNTS uac on itmcheckout.userUID=uac.userUID
WHERE itmcheckout.item_id=itm.item_id
GROUP BY itmcheckout.item_checkin_dt
) as item_checkout
ORDER BY itm.item_id, itm.item_inv_no
Again, I'm not sure if this will get you exactly what you want but playing around with OUTER APPLY (or possibly CROSS APPLY) might end up giving you the answer.
|
On January 04 2011 10:37 icystorage wrote:
i tried it and its better but i dont understand why
Don't worry about understanding it for now. There's so much syntax to learn that it will just come in time. For now just be happy that you don't have to write as much :D If you want to understand though:
+ Show Spoiler +Basically, typedefs take the left term, and make it the right term. They do this at compile-time and not run-time. Example: typedef int IcyInt;
Imagine that wherever you see "IcyInt", "int" is instead cut and pasted. Therefore you can write: IcyInt x = 1;
int y = 4;
int z = x + y; // z is 5 because both x and y are of type "int"
IcyInt test = z + z + x; // test is 11
You can extend this to whole terms: // this is what it does: typedef LEFT RIGHT
// here's with an int:
typedef int IcyInt;
// here's a chain of it
typedef IcyInt AnotherIcyIntIsAlsoStillAnInt;
AnotherIcyIntIsAlsoStillAnInt x = 1; // this is still an int :)
// you can do it with structs too
struct IcyNode { int m_data; }; // define your struct. this is the struct definition
typedef struct IcyNode ThisIsAnIcyNode; // create a typedef for it. this is the typedef definition
ThisIsAnIcyNode testNode;
testNode.m_Data = 13;
// the way I wrote combines the above struct and typedef definition. you can do any way. this is less writing :)
struct IcyNode { int m_data; } ThisIsAnIcyNode;
Therefore, from my previous post, you can see the struct is actually called "_IcyNode", and you've "typedef-ed" and used it as "IcyNode". http://en.wikipedia.org/wiki/Typedef
On January 04 2011 10:37 icystorage wrote:EDIT: okay so i added a new function and made it like how you made the list can you check if i did it correctly? the compilation was a succes this determines the length of the list + Show Spoiler +int Length(IcyNode* oneNode) {
IcyNode* current = oneNode;
int count = 0;
while (current != NULL) {
count++;
current = current->m_next;
}
return count;
}
Yep! that works! 
|
On January 04 2011 11:06 Phunkapotamus wrote:Show nested quote +On January 04 2011 10:37 icystorage wrote:
i tried it and its better but i dont understand why
Don't worry about understanding it for now. There's so much syntax to learn that it will just come in time. For now just be happy that you don't have to write as much :D If you want to understand though: + Show Spoiler +Basically, typedefs take the left term, and make it the right term. They do this at compile-time and not run-time. Example: typedef int IcyInt;
Imagine that wherever you see "IcyInt", "int" is instead cut and pasted. Therefore you can write: IcyInt x = 1;
int y = 4;
int z = x + y; // z is 5 because both x and y are of type "int"
IcyInt test = z + z + x; // test is 11
You can extend this to whole terms: // this is what it does: typedef LEFT RIGHT
// here's with an int:
typedef int IcyInt;
// here's a chain of it
typedef IcyInt AnotherIcyIntIsAlsoStillAnInt;
AnotherIcyIntIsAlsoStillAnInt x = 1; // this is still an int
// you can do it with structs too
struct IcyNode { int m_data; }; // define your struct. this is the struct definition
typedef struct IcyNode ThisIsAnIcyNode; // create a typedef for it. this is the typedef definition
ThisIsAnIcyNode testNode;
testNode.m_Data = 13;
// the way I wrote combines the above struct and typedef definition. you can do any way. this is less writing
struct IcyNode { int m_data; } ThisIsAnIcyNode;
Therefore, from my previous post, you can see the struct is actually called "_IcyNode", and you've "typedef-ed" and used it as "IcyNode". http://en.wikipedia.org/wiki/TypedefShow nested quote +On January 04 2011 10:37 icystorage wrote:EDIT: okay so i added a new function and made it like how you made the list can you check if i did it correctly? the compilation was a succes this determines the length of the list + Show Spoiler +int Length(IcyNode* oneNode) {
IcyNode* current = oneNode;
int count = 0;
while (current != NULL) {
count++;
current = current->m_next;
}
return count;
}
Yep! that works!
thank you so much for the time in explaining typedef, i have read it and it helps me in understanding more thank you
|
Minor question: What sorting algorithm would you generally use and why? So far I've got the following:
- Shell sort: I don't personally like this since you'll still sort it when you get to h = 1.
- Heap sort: Pretty ok imo, but rearranging the heap sounds a bit complicated and space consuming.
- Quick sort: Pretty ingenious, but might take time if the pivot/bound values aren't picked properly.
- Radix sort: Pretty cool, it's like the way we were asked to sort things alphabetically during grade school. The only problem I have is implementing it on code.
I need it for my C++ algo class, so far I think I understood the essence of the sorting algorithms to a good degree. What would you guys personally prefer and why? I just need opinions/inputs. Thanks.
|
|
|
|
|
|
EPT
