SubSubquery?

subquery in a subquery

[Twisted Mind]

Hi i mad this script (with help from webmasterworld):
$query =
"SELECT order_id,
order_status_it,
order_status_manager,
order_status_finance
FROM $table
WHERE order_id NOT IN (select order_id FROM mos_pshop_order_user_info WHERE country!='$mycountry')";
$result = mysql_db_query("$Database",$query);

This script works perfectly to get the country of the user but now i want to make a subquery in this one so it FIRST selects the country and then the departement not both thats all i could make. Dont know what i mean just ask it ill reply pretty fast..

[Twisted Mind]

please if u know how answer me :)

[Twisted Mind]

it looks like no one is here to help but if ua re here help me!

[Twisted Mind]

:)

[mcibor]

I don't see any department? Where do you want to ask that?
Michal Cibor

[Twisted Mind]

Well i want to add something more my script was looking like this :
$query = "SELECT
order_id,
order_status_manager,
order_status_finance,
order_status_it
FROM $table
WHERE order_id NOT IN (select order_id FROM mos_pshop_order_user_info WHERE country!='$mycountry')";
$result = mysql_db_query("$Database",$query);
print (mysql_error());

But it doesnt work well
actually it shows all records in the database

[Twisted Mind]

Well it shows now both all wich are from the same country and all wich are in the same departement,
but i want to show a list of orders from
the logged in user's department of the country he came from...

[mcibor]

I think this will do what you want

$query = "SELECT
order_id,
order_status_manager,
order_status_finance,
order_status_it
FROM $table
WHERE order_id NOT IN (select order_id FROM mos_pshop_order_user_info WHERE country!='$mycountry' OR department!='$department')";
$result = mysql_db_query("$Database",$query) or die ("Error: ".mysql_error());

What is in the variable $table?
Michal Cibor

[Twisted Mind]

//Select Database
$Database ="fluke";

//Get database table
$table ="mos_pshop_orders";

//Get database table
$orderinfotable ="mos_pshop_order_user_info";

//get 2cond table
$sessiontable ="mos_session";

//get usertable
$usertable = "mos_users";
this might explain why i used $table :P
i short them this way its way easyer i find
ill check it out thanks btw my var for departement is $mydepartement ;)

[Twisted Mind]

Ah damn it does exactly the same as my script does

Example

Logged in--> username : Manager-IT-NLD
country : NLD
departement : mydept

usertable
username--country--departement.
user1,----NLD------mydept-----.<-- OK <-- Gets posted
user2-----USA------mydept-----.<-- Not OK <-- Gets posted
user3-----NLD------notmydept--.<-- Not OK <-- Gets posted
user4-----USA------notmydept--.<-- Not OK <-- not posted

but now it will post all people who have mydept or nld
but i want them to have nld and mydept.
is this clear enough?

[mcibor]

Maybe it would be better to invert the logic?

As I understand, you want to select something that is in your country and in your department?

$query = "SELECT
order_id,
order_status_manager,
order_status_finance,
order_status_it
FROM $table
WHERE order_id IN (SELECT order_id FROM mos_pshop_order_user_info WHERE country='$mycountry' AND mydepartment='$mydepartment')";
$result = mysql_db_query("$Database",$query) or die ("Error: ".mysql_error());

Is that true?
Michal

[Twisted Mind]

Yes it it true and the script you just posted is the way my script is atm :)

[Twisted Mind]

Well it wasnt the same as my script departement was mydepartment in your script (mysql column) and i changed my search mos_pshop_orders to mos_users cuz pshop orders doesnt show departement but then at the end changin it all to the right ones it still does the same here is my up to date script:

$query =
"SELECT order_id,
order_status_it,
order_status_manager,
order_status_finance
FROM $table
WHERE order_id IN (select order_id FROM mos_users WHERE country='$mycountry' AND departement='$mydepartement')";

[Twisted Mind]

If u want to know what the whole script does (this is just a part)
it takes fields from the database where the manager from a departement in some country has to accept the order they placed and now i dont want him to see orders from people from other countrys that would not be good if he will be accepting them :)

[mcibor]

So one manager can see orders from his department and from his country only?

Can you show your db structure?
eg. users (id, name, email, country, department)... etc

Michal

[Twisted Mind]

If this would help solving the problem making my script more clear:
This is my sql query:
SELECTorder_id,
order_status_it,
order_status_manager,
order_status_finance
FROM mos_pshop_orders

WHERE user_id IN (select id FROM mos_users WHERE country = 'NLD' or departement ='test')

[Twisted Mind]

how do i post my structure?

[Twisted Mind]

this is order table:

Field         Type      Collation     Null  Key   Default Extra      Privileges            Comment
-------------------- ------------- ----------------- ------ ------ ------- -------------- ------------------------------- -------
order_id       int(11)    NULL            PRI   (NULL)  auto_increment select,insert,update,references     
user_id        varchar(32)  latin1_swedish_ci     MUL               select,insert,update,references     
vendor_id       int(11)    NULL            MUL   0            select,insert,update,references     
order_number     varchar(32)  latin1_swedish_ci YES   MUL   (NULL)          select,insert,update,references     
user_info_id     varchar(32)  latin1_swedish_ci YES   MUL   (NULL)          select,insert,update,references     
order_total      decimal(10,2) NULL                0.00           select,insert,update,references     
order_subtotal    decimal(10,2) NULL        YES       (NULL)          select,insert,update,references     
order_tax       decimal(10,2) NULL        YES       (NULL)          select,insert,update,references     
order_shipping    decimal(10,2) NULL        YES       (NULL)          select,insert,update,references     
order_shipping_tax  decimal(10,2) NULL        YES       (NULL)          select,insert,update,references     
coupon_discount    decimal(10,2) NULL                0.00           select,insert,update,references     
order_discount    decimal(10,2) NULL                0.00           select,insert,update,references     
order_currency    varchar(16)  latin1_swedish_ci YES       (NULL)          select,insert,update,references     
order_status     char(1)    latin1_swedish_ci YES       (NULL)          select,insert,update,references     
cdate         int(11)    NULL        YES       (NULL)          select,insert,update,references     
mdate         int(11)    NULL        YES       (NULL)          select,insert,update,references     
ship_method_id    varchar(255)  latin1_swedish_ci YES   MUL   (NULL)          select,insert,update,references     
customer_note     text      latin1_swedish_ci                      select,insert,update,references     
ip_address      varchar(15)  latin1_swedish_ci                      select,insert,update,references     
order_status_it    varchar(10)  latin1_swedish_ci         Pending         select,insert,update,references     
order_status_finance varchar(10)  latin1_swedish_ci         Pending         select,insert,update,references     
order_status_manager varchar(10)  latin1_swedish_ci         Pending         select,insert,update,references     

[Twisted Mind]

User table

Field        Type                      Collation     Null  Key   Default       Extra      Privileges            Comment
------------------- ---------------------------------------------- ----------------- ------ ------ ------------------- -------------- ------------------------------- -------
id          int(11)                     NULL            PRI   (NULL)        auto_increment select,insert,update,references     
name         varchar(50)                   latin1_swedish_ci     MUL                     select,insert,update,references     
username       varchar(25)                   latin1_swedish_ci                            select,insert,update,references     
email        varchar(100)                  latin1_swedish_ci                            select,insert,update,references     
password       varchar(100)                  latin1_swedish_ci                            select,insert,update,references     
usertype       varchar(25)                   latin1_swedish_ci     MUL                     select,insert,update,references     
block        tinyint(4)                   NULL                0                  select,insert,update,references     
sendEmail      tinyint(4)                   NULL        YES       0                  select,insert,update,references     
gid         tinyint(3) unsigned               NULL                1                  select,insert,update,references     
registerDate     datetime                    NULL                0000-00-00 00:00:00         select,insert,update,references     
lastvisitDate    datetime                    NULL                0000-00-00 00:00:00         select,insert,update,references     
activation      varchar(100)                  latin1_swedish_ci                            select,insert,update,references     
params        text                      latin1_swedish_ci                            select,insert,update,references     
user_info_id     varchar(32)                   latin1_swedish_ci YES       (NULL)                select,insert,update,references     
address_type     char(2)                     latin1_swedish_ci YES       BT                  select,insert,update,references     
address_type_name  varchar(32)                   latin1_swedish_ci YES       -default-              select,insert,update,references     
company       varchar(64)                   latin1_swedish_ci YES       (NULL)                select,insert,update,references     
title        varchar(32)                   latin1_swedish_ci YES       (NULL)                select,insert,update,references     
last_name      varchar(32)                   latin1_swedish_ci YES       (NULL)                select,insert,update,references     
first_name      varchar(32)                   latin1_swedish_ci YES       (NULL)                select,insert,update,references     
middle_name     varchar(32)                   latin1_swedish_ci YES       (NULL)                select,insert,update,references     
phone_1       varchar(32)                   latin1_swedish_ci YES       (NULL)                select,insert,update,references     
phone_2       varchar(32)                   latin1_swedish_ci YES       (NULL)                select,insert,update,references     
fax         varchar(32)                   latin1_swedish_ci YES       (NULL)                select,insert,update,references     
address_1      varchar(64)                   latin1_swedish_ci                            select,insert,update,references     
address_2      varchar(64)                   latin1_swedish_ci YES       (NULL)                select,insert,update,references     
city         varchar(32)                   latin1_swedish_ci                            select,insert,update,references     
state        varchar(32)                   latin1_swedish_ci                            select,insert,update,references     
country       varchar(32)                   latin1_swedish_ci                            select,insert,update,references     
zip         varchar(32)                   latin1_swedish_ci                            select,insert,update,references     
extra_field_1    varchar(255)                  latin1_swedish_ci YES       (NULL)                select,insert,update,references     
extra_field_2    varchar(255)                  latin1_swedish_ci YES       (NULL)                select,insert,update,references     
extra_field_3    varchar(255)                  latin1_swedish_ci YES       (NULL)                select,insert,update,references     
extra_field_4    char(1)                     latin1_swedish_ci YES       (NULL)                select,insert,update,references     
extra_field_5    char(1)                     latin1_swedish_ci YES       (NULL)                select,insert,update,references     
perms        varchar(40)                   latin1_swedish_ci         shopper               select,insert,update,references     
bank_account_nr   varchar(32)                   latin1_swedish_ci                            select,insert,update,references     
bank_name      varchar(32)                   latin1_swedish_ci                            select,insert,update,references     
bank_sort_code    varchar(16)                   latin1_swedish_ci                            select,insert,update,references     
bank_iban      varchar(64)                   latin1_swedish_ci                            select,insert,update,references     
bank_account_holder varchar(48)                   latin1_swedish_ci                            select,insert,update,references     
bank_account_type  enum('Checking','Business Checking','Savings') latin1_swedish_ci         Checking               select,insert,update,references     
Manager       varchar(50)                   latin1_swedish_ci                            select,insert,update,references     
managername     varchar(75)                   latin1_swedish_ci                            select,insert,update,references     
prjname       varchar(25)                   latin1_swedish_ci YES       (NULL)                select,insert,update,references     
accountnr      varbinary(25)                  NULL                                  select,insert,update,references     
departement     varchar(50)                   latin1_swedish_ci                            select,insert,update,references     

[Twisted Mind]

have fun reading it :)

[mcibor]

It should be AND not OR!

SELECT order_id,
order_status_it,
order_status_manager,
order_status_finance
FROM mos_pshop_orders
WHERE user_id IN (select id FROM mos_users WHERE country = 'NLD' AND departement ='test')

Or you can use left join:

SELECT mos_pshop_orders.order_id,
mos_pshop_orders.order_status_it,
mos_pshop_orders.order_status_manager,
mos_pshop_orders.order_status_finance
FROM mos_pshop_orders, mos_users
WHERE mos_users.user_id = mos_pshop_orders.user_id
AND mos_users.country = 'NLD'
AND mos_users.departement ='test';

Both of them should work fine!
Best regards
Michal Cibor

[Twisted Mind]

Ill try anyway i tryed AND and OR i have AND now but ill try it out

[Twisted Mind]

Thanks for trying to help me but still it doesnt work else then my own script

Remeber in the usertable user id field is just called id

[Twisted Mind]

OMG im so sorry i just looked at the data in the usertable but all the countrys where NL :P now it works just fine so it seems i dind need your help bu thanks anyway i have learned some thinga from it :)

[dmmh]

lol

[Twisted Mind]

Well there was one tiny little error on your script good i checked it all carfully becouse now he selected notyourcountry - yourdepartment :P

[Twisted Mind]

Well thanks but you upper script doesnt work... the lower does fine with some changes ur the best like all the rest here :P

[mcibor]

Shacks! Glad to be of help!

Have a good time learning php!

Best regards
Michal Cibor

[sunveria]

Hope this help....

$query = "SELECT
order_id,
order_status_manager,
order_status_finance,
order_status_it
FROM $table
WHERE order_id NOT IN (select order_id FROM mos_pshop_order_user_info WHERE country='$mycountry' AND department='$department')";
$result = mysql_db_query("$Database",$query) or die ("Error: ".mysql_error());

^ ^

[Twisted Mind]

ehm this has been fixed a mont ago dude