473,324 Members | 2,356 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Query Help is Needed

This seems simple, but it keeps tricking me. Could not get the proper
result. Thanks in advance!

TABLE1
App_ID Business_Unit_ID PROFILE_ID
1 001 AA1
3 001 AA1

TABLE2 - SUPERSET
App_ID Business_unit_ID
1 001
1 002
1 003
1 007
2 002
2 003
3 001
3 002
3 007
4 009
4 008

USING Business_Unit_ID and App_ID in TABLE1,

check to see if App_ID & Business_Unit_ID is in table2

if app_id and Business_Unit_Id from table1 is found in table2 (or is a
sub-set in table2) then
retun true
else false

In this example, the result should be True
Jul 20 '05 #1
3 1195
Assuming NULLs don't exist or are to be ignored:

SELECT CASE WHEN
NOT EXISTS
(SELECT *
FROM Table1 AS T1
LEFT JOIN Table2 AS T2
ON T1.app_id = T2.app_id
AND T1.business_unit_id = T2.business_unit_id
WHERE T2.app_id IS NULL)
THEN 'TRUE' ELSE 'FALSE' END

Notice that this returns 'TRUE' if Table1 is empty.

--
David Portas
------------
Please reply only to the newsgroup
--
Jul 20 '05 #2
[posted and mailed, please reply in news]

OBJECT MODULE, INC. (gr*********@yahoo.com) writes:
This seems simple, but it keeps tricking me. Could not get the proper
result. Thanks in advance!

TABLE1
App_ID Business_Unit_ID PROFILE_ID
1 001 AA1
3 001 AA1

TABLE2 - SUPERSET
App_ID Business_unit_ID
1 001
1 002
1 003
1 007
2 002
2 003
3 001
3 002
3 007
4 009
4 008

USING Business_Unit_ID and App_ID in TABLE1,

check to see if App_ID & Business_Unit_ID is in table2


My interpretation is that you want to test whether all rows in TABLE1
are present in TABLE2.

This would be something like:

IF EXISTS (SELECT *
FROM tbl1
WHERE NOT EXISTS
(SELECT *
FROM tbl2
WHERE tbl2.App_ID = tbl1.App_ID
AND tbl2.Business_unit_ID = tbl1.Busieness_unit_ID)
PRINT 'tbl1 is not a subset'
ELSE
PRINT 'tbl2 is not a subset'

Note: had you included CREATE TABLE statements for the tables and
INSERT statements with the sample data, I would have been able to
test the solution.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3
Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn********************@127.0.0.1>...
[posted and mailed, please reply in news]

OBJECT MODULE, INC. (gr*********@yahoo.com) writes:
This seems simple, but it keeps tricking me. Could not get the proper
result. Thanks in advance!

TABLE1
App_ID Business_Unit_ID PROFILE_ID
1 001 AA1
3 001 AA1

TABLE2 - SUPERSET
App_ID Business_unit_ID
1 001
1 002
1 003
1 007
2 002
2 003
3 001
3 002
3 007
4 009
4 008

USING Business_Unit_ID and App_ID in TABLE1,

check to see if App_ID & Business_Unit_ID is in table2


My interpretation is that you want to test whether all rows in TABLE1
are present in TABLE2.

This would be something like:

IF EXISTS (SELECT *
FROM tbl1
WHERE NOT EXISTS
(SELECT *
FROM tbl2
WHERE tbl2.App_ID = tbl1.App_ID
AND tbl2.Business_unit_ID = tbl1.Busieness_unit_ID)
PRINT 'tbl1 is not a subset'
ELSE
PRINT 'tbl2 is not a subset'

Note: had you included CREATE TABLE statements for the tables and
INSERT statements with the sample data, I would have been able to
test the solution.


MANY THANKS GUYS!!
Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
by: Irlan agous | last post by:
$query = "update reactie,form set 'reactie.persemail' = 'form.email' where 'reactie.persid' = 'form.id' AND reactie.persemail='' "; How can i get this query to work? Irlan
20
by: | last post by:
If I need to check if a certain value does exist in a field, and return either "yes" or "not" which query would be the most effestive?
6
by: paii | last post by:
I have a table that stores job milestone dates. The 2 milestones I am interested in are "Ship Date" TypeID 1 and "Revised Ship Date" TypeID 18. All jobs have TypeID 1 only some jobs have TypeID 18....
3
by: Eagle | last post by:
Hi all, This one's drivin' me nuts. Any help would be appreciated. (Access 2000). I have 3 tables: tblTools: having the basic data of a tool and a field (txt) showing yes or no with regard to...
3
by: cover | last post by:
I have a table with 50 fields that receive input depending on whether that input came in from a 'shaker' form or a 'conveyor' form. Input from the 'conveyor' form might populate 25 fields while...
3
by: faceman28208 | last post by:
Over the past few years I have consulted on six large projects that all independently arrived at the same moronic design desision: The use of SQL query classes. No, I don't mean a class...
0
by: Chuck36963 | last post by:
Hi all, I've been working on a listing problem and I can't figure out how to work it out. I have looked far and wide on the web to find answers, but I'd like other peoples input on my project in...
8
by: Roland Hall | last post by:
In Access you use "*" + + "*", + can be replaced with & Calling a parameterized query in Access requires % be used in place of *, however, all that I have read show dynamic SQL passed to Access: ...
2
by: sara | last post by:
I am helping a non-profit track their elder clients and care given to the clients. The organization would like a report that shows various info on ALL clients, such as: # in each town, # and...
20
by: exipnakias | last post by:
Hello Guys. In a form I created a listbox which looks up the values of a table. I want: 1) ..to create a query where a parameter will be needed in order to be loaded. But I do not want to...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.