1


Hoping you can help. I have three tables and would like to create a conditional query to make a subset based on a row's presence in one table then excluding the row from the results, then query a final, 3rd table. I thought this would be simple enough, but I'm not well practiced in SQL and after researching/testing for 6 hours on left joins, correlated sub-queries etc, it has helped, but I still can't hit the correct result set. So here's the setup:

T1
arn_mkt_stn
A00001_177_JOHN_FM
A00001_177_BILL_FM
A00001_174_DAVE_FM
A00002_177_JOHN_FM
A00006_177_BILL_FM
A00010_177_JOHN_FM
- note: the name's relationship to the 3 digit prefix (e.g. _177) and the FM part always is consistent: '_177_JOHN_FM' only the A000XX changes

T2
arn_mkt
A00001_105
A00001_177
A00001_188
A00001_246
A00002_177
A00003_177
A00004_026
A00004_135
A00004_177
A00006_177
A00010_177


Example: So if _177_JOHN_FM is a substring of arn_mkt_stn rows in T1, exclude it when getting arn_mkts with a substring of 177 from T2 - in this case, the desired result set would be:
A00003_177
A00004_177
A00006_177

Similarly, _177_BILL_FM would return:
A00002_177
A00003_177
A00004_177
A00010_177


Then I would like to use this result set to pull records from a third table based on the 'A00003' etc
T3
arn
A00001
A00002
A00003
A00004
A00005
A00006
...

I've tried a number of methods [where here $stn_code = JOHN_FM and $stn_mkt = 177]
"SELECT * FROM T2, T1 WHERE arn != SUBSTRING(T1.arn_mkt_stn, 1,6)
AND SUBSTRING(T1.arn_mkt_stn, 12,7) = '$stn_code'
AND SUBSTRING(arn_mkt, 8,3) = '$stn_mkt'
(then use this result to query T3..)

Also a left join and a subquery, but I'm clearly missing something! Any pointers gratefully received, thanks,

Rich.

[EDIT: Thanks for helping out sgeddes. I'll expand on my logic above... first, the result set desired is always in connection with one name only per query, e.g. from T1, lets use JOHN_FM. In T1, JOHN_FM is currently associated with 'arn's (within the arn_mkt_stn): A00001, A00002 & A00010'. The next step in T2 is to find all the 'arn's (within arn_mkt)' that have JOHN_FM's 3 digit prefix (177), then exclude those that are in T1. Note: A00006 remains because it is not connected to JOHN_FM in T1. The same query for BILL_FM gives slightly different results, excluding A00001 & A00006 as it has this assoc in T1.. Thanks, R]

richkoru
  • 15
  • 4

1 Answers1

1

You can use a LEFT JOIN to remove the records from T2 that match those in T1. However, I'm not sure I'm understanding your logic.

You say A00001_177_JOHN_FM should return:

A00003_177
A00004_177
A00006_177

However, wouldn't A00006_177_BILL_FM exclude A00006_177 from the above results?

This query should be close (wasn't completely sure which fields you needed returned) to what you're looking for if I'm understanding you correctly:

SELECT T2.arn_mkt, T3.arn
FROM T2 
  LEFT JOIN T1 ON
    T1.arn_mkt_stn LIKE CONCAT(T2.arn_mkt,'%')
  INNER JOIN T3 ON
    T2.arn_mkt LIKE CONCAT(T3.arn,'%')
WHERE T1.arn_mkt_stn IS NULL

Sample Fiddle Demo

--EDIT--

Reviewing the comments, this should be what you're looking for:

SELECT *
FROM T2 
  LEFT JOIN T1 ON
    T1.arn_mkt_stn LIKE CONCAT(LEFT(T2.arn_mkt,LOCATE('_',T2.arn_mkt)),'%') AND T1.arn_mkt_stn LIKE '%JOHN_FM'
  INNER JOIN T3 ON
    T2.arn_mkt LIKE CONCAT(T3.arn,'%')
WHERE T1.arn_mkt_stn IS NULL

And here is the updated Fiddle: http://sqlfiddle.com/#!2/3c293/13

sgeddes
  • 62,311
  • 6
  • 61
  • 83
  • Thanks very much sgeddes! Fast response too. I've edited my original post to hopefully make things a little clearer wrt my logic. Your example is much appreciated. R – richkoru Feb 22 '13 at 14:54
  • @richkoru -- is this what you're looking for (http://sqlfiddle.com/#!2/3c293/7)? It leaves A0001 because those are associated with different prefixes (not 177). Hopefully this helps. – sgeddes Feb 22 '13 at 15:06
  • thanks! it's pretty close. A00001 should be excluded though as it is assoc with JOHN_FM in T1. I'll have a tinker with this to exclude A00001 and the dups in the RHS result set... – richkoru Feb 22 '13 at 15:17
  • @richkoru -- so the 177 is irrelevant? It excludes A00001_177 but not A00001_105 for example. Hence the reason A00001 is showing up in the results. – sgeddes Feb 22 '13 at 15:22
  • @richkoru -- I've updated my answer with what I think you want :) Best of luck. – sgeddes Feb 22 '13 at 15:26
  • Thanks. The 177 is the 'market code' that JOHN_FM has. In T2, there are 6 A000xxx 'products' that are in JOHN_FM's market, but I want to exclude A00001,A00002 & A00010 as they are already assoc with JOHN_FM in T1. Sorry, it's a bit messy. The main idea is to return T2's products (identified by 177 mkt) for JOHN_FM that are not yet assoc in T1. If it helps, ignore T3, once I have this query working, I can run another later on T3 using A00003, A00004 & A00006. Thanks for persevering. – richkoru Feb 22 '13 at 15:38
  • Hey- thanks! Bang on. Our emails crossed. I'll remove the duplicate arn's in the RHS result set and I'm good to go. Cheers :) – richkoru Feb 22 '13 at 15:41
  • @richkoru -- np, thought perhaps you didn't see the updates before your last msg :) Glad I could help! – sgeddes Feb 22 '13 at 15:42