mbishop1980
Posts: 31
|
| Posted: 07/23/2008, 12:47 PM |
|
It may be a bit complicated to explain what I'm trying to do but here goes.
I have two tables:
carts
orderDate
username
office
status
created
orderCopied
office
office_name
office_state
mailing_city
mailing_zip
phone
fax
Here is the fun part! The carts table handles carts that are created by users in a office. The carts have certain statuses (started & completed). there is another table that handles the actual items in that cart but it's not important right now.
I have two grids:
Grid one "carts"
This grid shows all the completed carts for a given orderDate.
Grid two "office"
It has a office_name label
Here is my problem:
I want this grid to show what offices haven't submitted an order. That is why I need to get the office_name from office table so that I will have a complete list of the offices. And then I need to only leave the offices that haven't submitted an order.
I have used the code below but apparently I'm way out in left field somewhere.
Any help would be appreciated.
global $DBIntranet;
$status_result = CCDLookUp("office", "carts", "status = completed AND orderDate = " . $DBIntranet->ToSQL(CCGetFromGet("orderDate"), ccsText), $DBIntranet);
$DBIntranet->query($status_result);
$office_result = CCDLookup("office_name", "office", "office_name <> " . $DBIntranet->ToSQL($status_result, ccsText), $DBIntranet);
$DBIntranet->query($office_result);
$Container->office_name->SetValue($office_result);
_________________
Thanks,
Eric |
 |
 |
mentecky
Posts: 321
|
| Posted: 07/23/2008, 1:06 PM |
|
Eric,
Build a query on Office. Add the Carts table and link them. Right click the link and select "Left Join".
In WHERE select any field in the Cart Table. Select Condition "Is Null".
That should return any office that doesn't have a linked cart
Rick
_________________
http://www.ccselite.com |
|
|
|
mbishop1980
Posts: 31
|
| Posted: 07/23/2008, 1:41 PM |
|
I tried that Rick and I'm getting the offices that have completed an order.
I linked the carts.item and the office.office_name fields with a LEFT JOIN
Is that what you wanted me to do.
_________________
Thanks,
Eric |
|
|
|
mentecky
Posts: 321
|
| Posted: 07/23/2008, 1:50 PM |
|
My guess from your description would be office.office_name -> carts.office. The purpose of the Left Join is all offices will display, but any of the cart fields will be null if there is no matching cart.
Rick
_________________
http://www.ccselite.com |
|
|
|
mbishop1980
Posts: 31
|
| Posted: 07/23/2008, 4:18 PM |
|
Shouldn't it created NULL values for all of the fields from the carts that don't have values?
I have tried choosing several fields in the WHERE value and making them NULL and nothing has happened either.
_________________
Thanks,
Eric |
|
|
|
mentecky
Posts: 321
|
| Posted: 07/23/2008, 4:22 PM |
|
What fields link the 2 tables?
If you choose a value in carts that can't be null in the DB, ie the primary key, a null would represent no link between the 2 tables.
Rick
_________________
http://www.ccselite.com |
|
|
|
mbishop1980
Posts: 31
|
| Posted: 07/23/2008, 4:43 PM |
|
I have used the office.office_name and cart.office to link the two together
But if the office hasn't submitted an order than there will be no record for that particular office in the carts.
Every office is listed in the office table.
I changed it to where carts.office can be NULL and still nothing.
I know I'm not that smart when it comes to the database work.
_________________
Thanks,
Eric |
|
|
|
mbishop1980
Posts: 31
|
| Posted: 07/23/2008, 4:56 PM |
|
I figured it out.
On my join this is the syntax that I used and it worked perfectly.
SELECT DISTINCT office.office_name
FROM office LEFT JOIN carts ON
office.office_name <> carts.office
WHERE carts.orderDate = '{orderDate}'
_________________
Thanks,
Eric |
|
|
|
mbishop1980
Posts: 31
|
| Posted: 07/23/2008, 4:59 PM |
|
Nevermind I take that back.
_________________
Thanks,
Eric |
|
|
|
mbishop1980
Posts: 31
|
| Posted: 07/23/2008, 5:16 PM |
|
This time I really got it.
The one thing I wasn't doing was selecting a field from the carts table
Here is the table that works.
SELECT office_name, carts.username
FROM office LEFT JOIN carts ON
office.office_name = carts.office
WHERE carts.username IS NULL
_________________
Thanks,
Eric |
|
|
|
mbishop1980
Posts: 31
|
| Posted: 07/23/2008, 7:07 PM |
|
I have it working somewhat.
But I now have another problem.
I only want to display the offices that haven't submitted an order for that specific date.
I can't figure out how to make that work.
Can you help me?
_________________
Thanks,
Eric |
|
|
|
