Page 1 of 1

Filter which compare two fields in the same table

Posted: 2019-11-27 13:45
by fbrano
1st table has fields 1.Index 2.UserID, 3.ManagerID, 4.RequesterID,... where fields 2,3 are lookup fields from Table 2, while field 4 is filled up with ID of current requester after insert. The 2nd table stores all users and has fields 1.ID, 2.UserName, 3.ManagerID,... where field 3 is lookup value from 2nd table.
values of 1st table:
Index UserID ManagerID RequesterID
1 111 111 113
2 112 112 113
3 113 113 114
4 114 114 112

values of 2nd table:
ID UserName ManagerID
111 John 113
112 Jane 113
113 Chris 114
114 Tom 115

I'd like to make a filter of the 1st table where values ManagerID != RequesterID, so from the sample of values it should be only row where Index=4 because requester Jane 112 != 115 actual manager of Tom.

Re: Filter which compare two fields in the same table

Posted: 2019-11-27 22:39
by fbrano
Create table Streets(
ID int IDENTITY primary key,
Name varchar(100)
);

create table users(
ID int IDENTITY primary key,
Username varchar(100),
StreetID int references Streets(id),
OldStreet varchar(100)
);

insert into streets values ('1st street'), ('2nd street'), ('3rd street'), ('4th street'), ('5th street')
insert into users values ('Pol', 1,'2nd street'), ('Doortje', 1, '2nd street'), ('Marc', 2, '2nd street'), ('Bieke', 2, '2nd street'), ('Paulien', 2, '2nd street'), ('Fernand', 2, '1nd street'), ('Pascal', 2, '2nd street'), ('Boma', 3, '2nd street'), ('Goedele', 3, '2nd street'), ('Xavier', 4, '2nd street')



select username, streetid, oldstreet from users where oldstreet != (select streets.name from streets where streets.id=users.streetid)

Re: Filter which compare two fields in the same table

Posted: 2019-11-28 07:43
by fbrano
How to implement command line which works in phpMyAdmin:

SELECT DISTINCT Pouzivatel, (SELECT Pouzivatel FROM `pouzivatelia` WHERE Osc = pristupy.Pouzivatel), (SELECT Pouzivatel FROM `pouzivatelia` WHERE Osc = (select Manazer FROM `pouzivatelia` WHERE Osc = pristupy.Pouzivatel)), (SELECT Pouzivatel FROM `pouzivatelia` WHERE Osc = Ziadatel) FROM `pristupy` WHERE Ziadatel != (SELECT Manazer FROM `pouzivatelia` WHERE Osc = pristupy.Pouzivatel)

into tablename.view.php to make a table view of filtered unique fields which can be run from link-navmenu.php

Re: Filter which compare two fields in the same table

Posted: 2019-12-11 15:42
by onoehring
Hi fbrano,

sounds like a similar problem I had: Set a filter based on some other table. Maybe this works for you:
viewtopic.php?f=2&t=3346#p11653

Olaf