Filter which compare two fields in the same table

Wish to see a specific feature/change in future releases? Feel free to post it here, and if it gets enough "likes", we'd definitely include it in future releases!
Post Reply
User avatar
fbrano
Veteran Member
Posts: 70
Joined: 2018-03-19 10:39
Location: Slovakia
Contact:

Filter which compare two fields in the same table

Post by fbrano » 2019-11-27 13:45

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.
ver 23.15 1484

User avatar
fbrano
Veteran Member
Posts: 70
Joined: 2018-03-19 10:39
Location: Slovakia
Contact:

Re: Filter which compare two fields in the same table

Post by fbrano » 2019-11-27 22:39

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)
ver 23.15 1484

User avatar
fbrano
Veteran Member
Posts: 70
Joined: 2018-03-19 10:39
Location: Slovakia
Contact:

Re: Filter which compare two fields in the same table

Post by fbrano » 2019-11-28 07:43

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
ver 23.15 1484

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: Filter which compare two fields in the same table

Post by onoehring » 2019-12-11 15:42

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

Post Reply