CODEWITHSUNDEEP
sqlt-sql
Sam
Sam

Reputation: 1403

Query Join Condition

I Have two tables named account and assignedgroup. In account table there are two columns named ID and sourceaccount and In assignedgroup I have two columns named ID and assignedgroup.

I want to select ID by joining account and assignedgroup tables. I did something like this:

select a.ID
from account a 
  left outer join assignedgroup b 
   on a.sourceaccount = b.assignedgorup

But I get Null as output the reason being that The values in sourceaccount are like this

sourceaccount:

sample
sample

and whereas the values in assignedgroup are like this

assignedgroup:

sample-L1
sample-P-L1

Can anyone help me on how to join these two tables?

I want to get all the ID's whenever there is a sample value in both sourceaccount and assignedgroup.

It is comparing sample with sample-L1 since those are not equal it is returning null but I want the ID even if it has values like that. I mean if somepart of the column values matches then also I want those values to be displayed

Upvotes: 1

Views: 97

Answers (3)

Quassnoi
Quassnoi

Reputation: 425341

SELECT  a.id
FROM    account a
LEFT JOIN
        assignedgroup b
ON      b.assignedgorup LIKE a.sourceaccount + '-%'

Upvotes: 1

Philip Kelley
Philip Kelley

Reputation: 40309

Since the actual data within those two tables does not actually match, you cannot join those rows in those tables unless you start making some rather broad assumptions about the nature of the data in those tables. For example, this might work:

SELECT a.ID
 from Account a
  left outer join AssignedGroup b
   on a.sourceaccount = left(b.assignedgroup, 6)

or, more generically, 

SELECT a.ID
 from Account a
  left outer join AssignedGroup b
   on a.sourceaccount = left(b.assignedgroup, len(a.sourceaccount))

However, this is truly horrible code, logic, and database design, and I would only use this while tyring to troubleshoot and/or fix messed-up data.

Upvotes: 0

HLGEM
HLGEM

Reputation: 96552

Clearly your database structure is flawed. If you need the first part of the assigned group to join to the source account, it should be stored that way in a separate column. YOu can do some manipulation to get the right values, but changing the datbase structure is the best fix as any data manipulation you do in a join is very expensive (and likely to introduce bugs).

Upvotes: 0

Related Questions