Compare two columns is IN another table

Question

Hi all,

I am moving from using Hibernate to SQL queries and this problem is rather difficult with my background in SQL:

I have the following table and the fields I am interesting in receiving:

::PROFILE_TABLE FIELDS::

ACCOUNT_FK   > OWNER_FK 

RECEIVER_FK  > ACCOUNT_FK  > _ OWNER_FK

I need to get the OWNER in the ACCOUNT table for each record in this profile table, I can't think of a way of doing this without a huge sql query which I can't even think of right now.  The profile table has an account field and a receiver field which also contains an account field in the table.

Obviously in Hibernate and java classes I can just use

Profile.Account.Owner is in array Or Profile.Receiver.Account.Owner is in array to check if any of these two owners for each profile is present.

Anyone have ideas?

Select the owner from the account table where account.id = profile.account_fk and then join this for the multiple joins I need for the receiver?

Answer 1

It's probably not too difficult, but the challenge for me as an SQL person is to understand what your table looks like from a Java-style description.

If you post the CREATE TABLE script for the table that helps. Even better if you also post INSERT statements with sample data and the expect result given that sample and an explanation why you want that result. Then it is possible to develop a tested query.

Answer 2

In addition to Erland response, which is basically the same response I wanted to add :-)

1. Once you learn How to use SQL Server Profiler or Extended Event (which is a bit more advance topics than your current level probably), you will have a VERY POWEFUL TOOL to work with.

Using the tools I mentioned above, you can monitor the queries that get to the server. This means that, if you know how to get the result using Object-relational mapping (ORM) like Entity Framework or Hibernate, then you can post your ORM code and check the server what is the query that got there. This is VERY USEFUL tool to lean sql for people that know how to use ORM

2. In the mean time you can get the queries that create the table using the SSMS GUI:

open the explorer windows and find the relevant tables -> right click on the table name ->  in the menu select "Script table as"  -> "create to" -> click on "New Query Editor Window" -> this will open the create query in a new editor window :-)

** As  Erland said, queries to insert some sample data is important as well (and the rest of the information he mentioned).

Here are some link relevant to what I mentioned:

• How To: Use SQL Profiler
• SQL Server Profiler
• Extended Events

---

Ronen Ariely  [Personal Site]    [Blog]    [Facebook]    [Linkedin]

Answer 3

Thanks for the replies everyone, I am just wondering now if this should all be done in SQL or not? I'll refine my problem in English further for clarification

There is a profile table that needs to be "filtered" to contain only the specific number of rows for which their ID is 5 AND their OWNER_ID exists in a Black List table. However, the OWNER_ID is not a row in this table but only obtainable by going through other tables like so:

profile.account.owner_id

profile.receiver.account.owner_id

So yes, the account holds the field for owner id and account is accessible in the profile for the sender, but for the receiver there's an extra intermediate table called receiver. I only need to select the rows in which their owner ID is in another table. Is this the best way to be going about this is 100% SQL?

Apologies for my poor skills in SQL and I appreciate your replies to my poorly written problem.

PS I'm not on the system with the database at the moment

Answer 4

Thanks for the replies everyone, I am just wondering now if this should all be done in SQL or not?

Most likely, yes.

PS I'm not on the system with the database at the moment

Well, since you are not on your system, and you apparently don't know your tables, so that you can create something to explain your problem, you will have to let this rest until you can give us CREATE TABLE statements and sample data. It appears to be a fairly simple problem, but without knowledge of the tables it is difficult to give you an exact query.

Answer 5

Hi Erland,

I have given you the fields that are used and the relationship between each table.

Profile Table:

- Number (I will be selecting records according to this number as one criteria)

- Account_fk (id in account table)

- Receiver_fk (id in receiver table)

Account Table:

- Owner_fk (id in Owner table) 

Receiver Table:

- Account_fk (id in Account table)
                    

   

Okay guys  I will post a mockup DDL tomorrow for every table and field. Thanks again.

Answer 6

So it's something like

SELECT ...
FROM   Profile P
WHERE  NOT EXISTS (SELECT *
                   FROM   Accounts A
                   WHERE  P.Account_fk = A.Account_pk
                     AND  A.Owner_fk IN (SELECT ID FROM BlackList))
  AND NOT EXISTS (SELECT *
                  FROM   Receivers R
                  JOIN   Accounts A ON R.Account_fk = A.Account_pk
                  WHERE  P.Account_fk = R.Account_pk
                    AND  A.Owner_fk IN (SELECT ID FROM BlackList))

Answer 7

Hi Erland,

I think the OP said he wanted people who are black-listed.

So, if that's indeed the intent, remove the NOT from both EXISTS subqueries and use OR instead of AND if you wanted to get black-listed owners.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles

Answer 8

So it's something like

SELECT ...
FROM   Profile P
WHERE  NOT EXISTS (SELECT *
                   FROM   Accounts A
                   WHERE  P.Account_fk = A.Account_pk
                     AND  A.Owner_fk IN (SELECT ID FROM BlackList))
  AND NOT EXISTS (SELECT *
                  FROM   Receivers R
                  JOIN   Accounts A ON R.Account_fk = A.Account_pk
                  WHERE  P.Account_fk = R.Account_pk
                    AND  A.Owner_fk IN (SELECT ID FROM BlackList))

Yes! Been testing this with mockup data and seems to work like a charm, thanks a lot. Will test on it the system asap.

@Naomi N - Yeah I changed the code to exactly that ;)

Edit : yes works fine, I had to slightly modify the following:

SELECT ...
FROM   Profile P
WHERE  EXISTS (SELECT *
                   FROM   Accounts A
                   WHERE  P.Account_fk = A.Account_pk
                     AND  A.Owner_fk IN (SELECT ID FROM BlackList))
  OR EXISTS (SELECT *
                  FROM   Receivers R
                  JOIN   Accounts A ON R.Account_fk = A.Account_pk
                  WHERE  P.Receiver_fk= R.Account_pk
                    AND  A.Owner_fk IN (SELECT ID FROM BlackList))

Just changed account_fk to receiver_fk in the second exist statement for the other field in the profile. Thanks Erland!!

Answer 9

I will post a mockup DDL tomorrow for every table and field.

I had already prepared some options for what you requested, but I was waiting for you to post the DDL. But today I realized that you had already marked one of the answers. Even so, below are 3 options.

There is a profile table that needs to be "filtered" to contain only the specific number of rows for which their ID is 5 AND their OWNER_ID exists in a Black List table

Try

-- code #2
SELECT P.columns
  from tbPROFILE as P
  where P.ID = 5
        and (exists (SELECT *
                       from tbBLACKLIST as BL
                       where BL.OWNER_FK = (SELECT A.OWNER_FK
                                              from tbACCOUNT as A
                                              where A.ACCOUNT_ID = P.ACCOUNT_FK
                                           )
                    )
             or
             exists (SELECT *
                      from tbBLACKLIST as BL
                      where BL.OWNER_FK = (SELECT A.OWNER_FK
                                             from tbACCOUNT as A
                                             where A.ACCOUNT_ID = (SELECT R.ACCOUNT_FK
                                                                     from tbRECEIVER as R
                                                                     where R.RECEIVER_ID = P.RECEIVER_FK
                                                                  )
                                          )
                    )
            );

or

-- code #3
SELECT P.columns
  from tbPROFILE as P
  where P.ID = 5
        and (exists (SELECT *
                       from tbBLACKLIST as BL
                            inner join tbACCOUNT as A on BL.OWNER_FK = A.OWNER_FK
                       where A.ACCOUNT_ID = P.ACCOUNT_FK
                    )
             or
             exists (SELECT *
                       from tbBLACKLIST as BL
                            inner join tbACCOUNT as A on BL.OWNER_FK = A.OWNER_FK
                            inner join tbRECEIVER as R on A.ACCOUNT_ID = R.ACCOUNT_FK
                       where R.RECEIVER_ID = P.RECEIVER_FK
                    )
            );

or

-- code #4
SELECT P.columns
  from tbPROFILE as P
       inner join tbACCOUNT as A on A.ACCOUNT_ID = P.ACCOUNT_FK
       inner join tbBLACKLIST as BL on BL.OWNER_FK = A.OWNER_FK
  where P.ID = 5
union
SELECT P.columns
  from tbPROFILE as P
       inner join tbRECEIVER as R on R.RECEIVER_ID = P.RECEIVER_FK
       inner join tbACCOUNT as A on A.ACCOUNT_ID = R.ACCOUNT_FK
       inner join tbBLACKLIST as BL on BL.OWNER_FK = A.OWNER_FK
  where P.ID = 5;

---

      José Diz     Belo Horizonte, MG - Brasil

---

Hello Jose,

Yes apologies, as soon as it was working fine I said it was resolved. I do like your code #4 example as in my opinion it is easier to read not sure about performance differences though. Will try it when I get home!

Thanks Jose.

Answer 10

Thanks Jose, yours is the easiest to read. There is some duplication in the join with the black list and where clause though 

Answer 11

@Erland @Jose

I have a final question; If I had to use the resulting data to search another table based upon some of the fields I have received, is this possible? The best solution would be to do all of this in the same query right?

Answer 12

Yes, you would need to incorporate it into the final query.

Alternatively, you can output that result into a temporary table and use that for consequent searches.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles

Answer 13

@Naomi N

Thanks for the fast response. Yes, I might try the temp table because the I need to search other tables similar to the first but I only require the ID of the resulting rows from the earlier query so it is different fields this time.

I can select the results in a temporary table using the #<table name> right?

Not all occasions I need to use this data to search another table, so maybe 2 separate queries? The one above for one condition and another query to get the extra data from additional tables?

Answer 14

Yes, use can use temp table #<table name>

It can be 2 separate queries, you may want to experiment and analyze the time and performance to decide if you need a temporary table or just 2 separate queries.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles

Answer 15

There is some duplication in the join with the black list and where clause though 

How is the relationship between the ACCOUNT and BLACKLIST tables: 1:1, 1:N?
Could you provide us with the DDL statement of the tables?

Note that the join of the subqueries in code #4 is of UNION type. It can't be UNION ALL.

---

      José Diz     Belo Horizonte, MG - Brasil

---

Yeah I was just mentioning the SQL script. There is no relation between them, only the Owner and Blacklist.

Answer 16

@Erland

@Jose

If I post the full DDL do you think you could modify the existing scripts (which work fine) to check another table (this time it should not exist in there).

The fields I need to check are also in Account table but are not Owner_fk. And the black list table I have to check against will not contain a single value but value_1 value_2 per row, in which the both values should not exist in both combinations. I am posting this to check if you guys still view this thread as I have marked it as closed (I thought  I could complete this giving your initial scripts)

Answer 17

It always help to have CREATE TABLE + INSERT statements with sample data and the execpted results, as this makes it possible to test the solution before posting.

Answer 18

It always help to have CREATE TABLE + INSERT statements with sample data and the execpted results, as this makes it possible to test the solution before posting.

Ok will do this later and post back if you could help with it, thanks again.

Answer 19

@Jose

@Erland

** The field relationship and tables are same as before but I got a bit muddled on the name (there is an extra field in the profile table which is used for the whitelist (not blacklist solution already given).

The previous posts gave the solution for the blacklist, but I need to also fetch two values to check if they sre BOTH contained in a row in the whitelist, if not, then that result should be returned. 

Profile Table (please scroll to the right for my expected result + comments):

CREATE TABLE [dbo].[PROFILE](
	[ID] [bigint],
	[RECEIVER_FK] [bigint],
	[ACCOUNT_FK] [bigint],
	[DESTINATION_RECEIVER_FK] [bigint])



INSERT [dbo].[PROFILE] ([ID], [RECEIVER_FK], [ACCOUNT_FK], [DESTINATION_RECEIVER_FK]) VALUES (1, 100, 1, 101);   --This will be in blacklist and whitelist (not include in results)
INSERT [dbo].[PROFILE] ([ID], [RECEIVER_FK], [ACCOUNT_FK], [DESTINATION_RECEIVER_FK]) VALUES (2, 100, 2, 102);   --This will not be in blacklist and will be in whitelist (not include in results)
INSERT [dbo].[PROFILE] ([ID], [RECEIVER_FK], [ACCOUNT_FK], [DESTINATION_RECEIVER_FK]) VALUES (3, 101, 1, 102);	 --This will be in blacklist and not in whitelist BUT not include because receiver points to same account
INSERT [dbo].[PROFILE] ([ID], [RECEIVER_FK], [ACCOUNT_FK], [DESTINATION_RECEIVER_FK]) VALUES (4, 102, 1, 103);   --This will be in blacklist and not in whitelist (include in results)

Receiver table:

CREATE TABLE [dbo].[RECEIVER](
	[ID] [bigint],
	[ACCOUNT_FK] [bigint])

	

INSERT [dbo].[RECEIVER] ([ID], [ACCOUNT_FK]) VALUES (100, 1);
INSERT [dbo].[RECEIVER] ([ID], [ACCOUNT_FK]) VALUES (101, 2);
INSERT [dbo].[RECEIVER] ([ID], [ACCOUNT_FK]) VALUES (102, 2);
INSERT [dbo].[RECEIVER] ([ID], [ACCOUNT_FK]) VALUES (103, 3);

Account table:

CREATE TABLE [dbo].[ACCOUNT](
	[ID] [bigint],
	[OWNER_FK] [bigint],
	[CO_OWNER_FK] [bigint])




INSERT [dbo].[ACCOUNT] ([ID], [OWNER_FK], [CO_OWNER_FK]) VALUES (1, 1, 5);
INSERT [dbo].[ACCOUNT] ([ID], [OWNER_FK], [CO_OWNER_FK]) VALUES (2, 2, 6);
INSERT [dbo].[ACCOUNT] ([ID], [OWNER_FK], [CO_OWNER_FK]) VALUES (3, 3, 4);

WhiteListCoOwner table:

CREATE TABLE [dbo].[WhitelistCoOwner](
	[ID] [bigint],
	[CO_OWNER_1_FK] [bigint],
	[CO_OWNER_2_FK] [bigint])




INSERT [dbo].[WhitelistCoOwner] ([ID], [CO_OWNER_1_FK], [CO_OWNER_2_FK]) VALUES (1, 6, 5);

BlackListOwner table:

CREATE TABLE [dbo].[BlackListOwner](
	[OWNER_FK] [bigint],
	[ID] [bigint])



INSERT [dbo].[BlackListOwner] ([ID], [OWNER_FK]) VALUES (1, 1);
INSERT [dbo].[BlackListOwner] ([ID], [OWNER_FK]) VALUES (2, 4);

Owner table:

CREATE TABLE [dbo].[Owner](
	[ID] [bigint],
	[CODE] [varchar](255))




INSERT [dbo].[Owner] ([ID], [CODE]) VALUES (1, 'owner1');
INSERT [dbo].[Owner] ([ID], [CODE]) VALUES (2, 'owner2');
INSERT [dbo].[Owner] ([ID], [CODE]) VALUES (3, 'owner3');
INSERT [dbo].[Owner] ([ID], [CODE]) VALUES (4, 'owner4');

CoOwner table:

CREATE TABLE [dbo].[CoOwner](
	[ID] [bigint],
	[CODE] [varchar](255))



INSERT [dbo].[CoOwner] ([ID], [CODE]) VALUES (4, 'coOwner1');
INSERT [dbo].[CoOwner] ([ID], [CODE]) VALUES (5, 'coOwner2');
INSERT [dbo].[CoOwner] ([ID], [CODE]) VALUES (6, 'coOwner3');

Obviously the Owner and CoOwner tables are not important because I am only checking for the ID from these tables (OWNER_FK, CO_OWNER_FK) but I have shown the DDL anyhow.

I have left comments on the profile table about which record should be returned and which wont; given reasons why. This is the expected result. So the query should turn 1 row from the profile table with the id of 4 and receiver_fk of 2.

The following query by Erland will give the correct results for comparing the Owners in the blacklist, but I do not know how to compare the co-owners to the white list (note the co-owner field is also in the account table but instead of using account_fk from profile we use receiver_fk with the destination_receiver_fk).

Please ask for more information if i am not clear. Blacklist works fine, but to compare 2 different fields to a single whitelist row seems impossible. Well, all in 1 query anyhow.

SQL for blacklist that works from Erland slightly modified (Jose I didn't have time to convert yours):

SELECT profile.* FROM PROFILE profile
WHERE EXISTS (SELECT *
                   FROM   Account A
                   WHERE  profile.Account_fk = A.id
                     AND  A.Owner_fk IN (SELECT OWNER_FK FROM BlackListOwner))
  OR EXISTS (SELECT *
                  FROM   Receiver R
                  JOIN   Account A ON R.Account_fk = A.ID
                  WHERE  profile.destination_receiver_fk = R.ID
                    AND  A.Owner_fk IN (SELECT OWNER_FK FROM BlackListOwner))

edit: I forgot to mention as in my comments on profile DDL, the account for the receiver and destination receive cant be the same, if it is it wont return any results or check the whitelist.

further edit(REQUIREMENT IN DETAIL): the profile table has the fields

Receiver_fk,

account_fk  

destination_receiver_fk.

the first blacklist search uses the account_fk and destination_receiver_fk to get the account and then owner. but the whitelist uses the receiver_fk and destination_receiver_fk (receiver instead of account_fk) to get the account then the CO-OWNER. So 1 same field, 1 different and not Owner from account but Co Owner.

Start to finish:  Search blacklist for account.owner or destination_receiver.account.owner..if true then search whitelist for receiver_fk.account.coOwner AND destination_receiver.account.coOwner. If both exist in _1 and _2, or _2 and _1 and they are not the same, return this row in the profile for this account.

                                  

Answer 20

I will have to confess that I don't understand the WhitelistCoOwner table. There are two owner_fk columns, but there is only one co-owner per account?

Answer 21

I will have to confess that I don't understand the WhitelistCoOwner table. There are two owner_fk columns, but there is only one co-owner per account?

There's an account field in the profile that is used for the blacklist but the two required co-owners are taken from the two accounts linked to receiver and destination_receiver in profile.

profile.receiver_fk.account.coOwner 

profile.destination_receiver_fk.account.coOwner

These two fields are required to be compared with the two in white list. This is mockup, probably not v good 

So the interested fields are:

Blacklist:

profile.account_fk.owner_fk

profile.destination_receiver_fk.account_fk.owner._fk

Whitelist:

profile.receiver_fk.coOwner_fk

profile.destination_receiver_fk.account_fk.Coowner._fk

"Destination_receiver_fk" and "receiver_fk" are both "Receiver" which have an account linked to them. Account also happens to directly be in profile table so there's actually 3 accounts..

Answer 22

I assume that the two owner_fk in the white list table cannot be any order, but one pertains to receiver and one to the destination receiver.

There is one condition commented out below. There is a comment about a row being excluded when both recievers point to the same account, but you don't talk about that elsewhere. The condition I've commented out is for this case.

SELECT p.* FROM PROFILE p
WHERE (EXISTS (SELECT *
               FROM   ACCOUNT A
               WHERE  p.ACCOUNT_FK = A.ID
                 AND  A.OWNER_FK IN (SELECT OWNER_FK FROM BlackListOwner)) OR
       EXISTS (SELECT *
               FROM   RECEIVER R
               JOIN   ACCOUNT A ON R.ACCOUNT_FK = A.ID
               WHERE  p.DESTINATION_RECEIVER_FK = R.ID
                 AND  A.OWNER_FK IN (SELECT OWNER_FK FROM BlackListOwner)))
/*   AND NOT EXISTS (SELECT *
                   FROM   RECEIVER R1
                   JOIN   RECEIVER R2 ON R1.ACCOUNT_FK = R2.ACCOUNT_FK
                   WHERE  R1.ID = p.RECEIVER_FK
                     AND  R2.ID = p.DESTINATION_RECEIVER_FK) */
   AND NOT EXISTS (SELECT *
                   FROM   RECEIVER R
                   JOIN   ACCOUNT A ON A.ID = R.ACCOUNT_FK
                   WHERE  R.ID = p.RECEIVER_FK
                     AND  A.CO_OWNER_FK IN (SELECT WO.CO_OWNER_2_FK FROM WhitelistCoOwner WO))
   AND NOT EXISTS (SELECT *
                   FROM   RECEIVER R
                   JOIN   ACCOUNT A ON A.ID = R.ACCOUNT_FK
                   WHERE  R.ID = p.DESTINATION_RECEIVER_FK
                     AND  A.CO_OWNER_FK IN (SELECT WO.CO_OWNER_1_FK FROM WhitelistCoOwner WO))

---

Erland Sommarskog, SQL Server MVP, [e-mail]