July 10, 2008 at 11:20 am
Hi Again...
If I have tableA and tableB with the key being keyid in both.
I need a query that will produce a report that says if the keyid is in both tables then DO NOT print it.otherwise if it is in tableA and not in tableB then print....
Can some one else please?
Thanks
July 10, 2008 at 11:23 am
July 10, 2008 at 11:53 am
hello.. ok, i jsut found out a catch I was not aware of.
if in tableA the id exist and looks like 22222.222.22.01.01 and in tableB the base (which is the first 5 plus the next 3) is there (ie. 22222.222) then it is considered it still exists and wil not show?? Am I making any since what so ever??
Thanks!
July 10, 2008 at 12:03 pm
Well, if you know for sure that table B only holds the first 8 (plus the period, so 9) of the ID, just change the JOIN predicate to reflect that....
SELECT a.*
FROM TableA a
LEFT JOIN TableB b
ON a.keyid = LEFT(b.keyid,9)
WHERE b.keyid IS NULL
July 10, 2008 at 12:40 pm
the tricky part (for me) is if the id (in table A) exist at any level in table B then it should NOT print.
TABLE A TABLE B
11111.001 11111.001.05.05 -- WOULD NOT PRINT
11111.002 11111.002.09.01.01 -- WOULD NOT PRINT
22222.001.01 22222.001.01 --- Would Print
July 10, 2008 at 1:15 pm
OK, so based off of what you've just posted, can you tell my what is wrong with this:
DECLARE @TableA TABLE (KeyID varchar(20))
DECLARE @TableB TABLE (KeyID varchar(20))
INSERT INTO @TableA
SELECT '11111.001' UNION ALL
SELECT '11111.002' UNION ALL
SELECT '22222.001.01'
INSERT INTO @TableB
SELECT '11111.001.05.05' UNION ALL -- WOULD NOT PRINT
SELECT '11111.002.09.01.01' UNION ALL -- WOULD NOT PRINT
SELECT '22222.001.01' --- Would Print
SELECT a.*
FROM @TableA a
LEFT JOIN @TableB b
ON a.keyid = LEFT(b.keyid,9)
WHERE b.keyid IS NULL
July 10, 2008 at 1:22 pm
yep... below is what i get:
(3 row(s) affected)
(3 row(s) affected)
KeyID
--------------------
22222.001.01
(1 row(s) affected)
July 10, 2008 at 1:36 pm
July 16, 2008 at 11:53 pm
What I understand is this :
1. a.keyid = b.keyid -- WILL NOT PRINT
2. a.keyid = LEFT(b.keyid,9) -- WILL NOT PRINT
3. (a.keyid <> b.keyid) AND (a.keyid <> LEFT(b.keyid,9)) -- WILL PRINT
DECLARE @TableA TABLE (KeyID varchar(20))
DECLARE @TableB TABLE (KeyID varchar(20))
INSERT INTO @TableA
SELECT '11111.001' UNION ALL
SELECT '11111.002' UNION ALL
SELECT '22222.001.01'
UNION ALL SELECT '120394.001' -- ADDED DATA
INSERT INTO @TableB
SELECT '11111.001.05.05' UNION ALL -- WOULD NOT PRINT
SELECT '11111.002.09.01.01' UNION ALL -- WOULD NOT PRINT
SELECT '22222.001.01' -- would NOT print
UNION ALL SELECT '102948.001' -- ADDED DATA
select keyid from @tablea
where keyid not in (select keyid from @tableb) and
keyid not in (select left(keyid,9) from @tableb) -- There are lots of better way to do this
"-=Still Learning=-"
Lester Policarpio
July 17, 2008 at 3:30 am
Hi Lester and thanks for the reply.
I go back to work on Monday and so I wont be able to test this until then, but I really appreciate it, as I am stuck on this. basically if the id in tableA is at any level in tableB then it will not show.
12345.001.01.01 is in tableA
if 12345.001 or even 12345 is in tableB, then it will not show.
Thanks!!
July 17, 2008 at 6:43 am
Kipp, if table a.id *always* has periods, you could try this:
SELECT
a.*
FROM TableA a LEFT OUTER JOIN
TableB b
ON SUBSTRING(a.id,1,CHARINDEX('.',a.id)-1) =
SUBSTRING(b.id,1,LEN(SUBSTRING(a.id,1,CHARINDEX('.',a.id)-1)))
WHERE b.id IS NULL
Not the most elegant, but it seemed to work on the few test records I created.
Greg
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
July 17, 2008 at 4:03 pm
kipp (7/10/2008)
yep... below is what i get:(3 row(s) affected)
(3 row(s) affected)
KeyID
--------------------
22222.001.01
(1 row(s) affected)
Kipp, you're still stuck on this? Based on the mock up that I did, what results do you expect to get?
July 17, 2008 at 6:44 pm
[/quote]Kipp, you're still stuck on this? Based on the mock up that I did, what results do you expect to get?[/quote]
Yesterday he said he won't be back until Monday, so I guess that's it for this one for a while
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
July 17, 2008 at 9:12 pm
Hmmmm is this what you want?
1. what ever value in TableB which can be seen in TableA must NOT be printed?
Example : TableB = '120394.00%' IF THIS EXISTS IN TableA, every value in TableA LIKE '120394.00%' is not printed
"-=Still Learning=-"
Lester Policarpio
July 18, 2008 at 7:58 am
CREATE TABLE #TableA (KeyID varchar(20))
CREATE TABLE #TableB (KeyID varchar(20))
INSERT INTO #TableA
SELECT '11111.001' UNION ALL
SELECT '11111.002' UNION ALL
SELECT '22222.001.01' UNION ALL
SELECT '120394.001' -- ADDED DATA
INSERT INTO #TableB
SELECT '11111.001.05.05' UNION ALL -- WOULD NOT PRINT
SELECT '11111.002.09.01.01' UNION ALL -- WOULD NOT PRINT
SELECT '22222.001.01' UNION ALL -- would NOT print
SELECT '102948.001' -- ADDED DATA
SELECT a.*
FROM #TableA a
LEFT JOIN #TableB b
ON b.keyid LIKE a.keyid + '%'
WHERE b.keyid IS NULL
Output:
keyid
120394.001
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply