• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 230
  • Last Modified:

SQL JOIN

Hi,

This should be easy but cannot get it correct.

I have 2 tables  one contains 3 Character Codes and the other contains combos of those 3 characters concatentated together as 6 character codes.  So table A hold "ABC" and "DEF", TableB holds "ABCDEF" and "DEFABC".  TableB also holds combos that are NOT possible from codes in TableA.

I need SQL that say give me all from TableB where the first 3 chars or last 3 chars of TableA match an entry in TableB.  The SQL also needs to only return the row ONCE.  So I if I had ABCDEF, althought the join on "ABC" and "DEF" is true it should only be returned ONCE.

Thanks

nutnut
0
nutnut
Asked:
nutnut
  • 3
  • 2
  • 2
  • +1
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do:
select b.*
  from tableB b
where exists(select null from tableA a on b.code like a.code + '%' or b.code like '%' + a.code )

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if you are interesting in knowing which entry/entires from tableA did the match, this will be a different story ...
select b.code
  , max(a.code) , min(a.code)
  from tableB b
  join tableA a on b.code like a.code + '%' 
   or b.code like '%' + a.code 
 group by b.code

Open in new window

0
 
Pratima PharandeCommented:
TableB
Combo   -- values like ABCDEF

TableA
code1 code2
ABC    DEF


Select * from TableB
where Combo in ( Select code1+code2 from table1)
or Combo in ( Select code2+code1 from table1)
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
McOzCommented:
Something like this? (untested)

assuming you have a field "txtCode" in TableA and "txtCombo" in TableB:
SELECT * FROM TableB WHERE (Left(txtCombo,3) IN (SELECT txtCode FROM TableA)) OR (Right(txtCombo,3) IN (SELECT txtCode FROM TableA))

Open in new window


McOz
0
 
nutnutAuthor Commented:
Thanks but it doesn't like

from tableA a on b.code
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry. .
select b.*
  from tableB b
where exists(select null from tableA a where b.code like a.code + '%' or b.code like '%' + a.code )

Open in new window

0
 
Pratima PharandeCommented:
Select TableB.* from TableB

where substring(TableB.combo,1,3) in (Select code from table1)
AND substring(TableB.combo,4,3) in (Select code from table1)
0
 
nutnutAuthor Commented:
Thanks spot on as always!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now