problems with Join query

Question

Hi I've got data in two tables my first table contains

first name
last name

my second table contain

userid
first name
last name

I'm trying to write a sql query to get the userid of a particular user but I'm getting empty set while executing the query. Could anyone please verify that the query I'm using is right? It seems ok to me

 select users.id 
 FROM TABLE1 AS r 
   LEFT JOIN TABLE2 AS users 
     ON (users.firstname = r.firstname 
     AND users.lastname=r.lastname)

Are you sure that firstname and lastname are exactly the same in both tables? Capitalization, trailing (and leading) spaces, all of these things are hidden gotchas. — Brian Hoover
– Brian Hoover, Commented Feb 10, 2012 at 12:43
Is the second table containing two columns or three? The definition implies two while the query implies three. — apokryfos
– apokryfos, Commented Feb 10, 2012 at 15:03
You should never join tables on a free-text field - it will lead to endless problems - especially when someone decides you need to support chinese and now your collations all need to change. Use Ids. — Basic
– Basic, Commented Feb 10, 2012 at 16:13

Matthias Vogel · Accepted Answer · 2012-02-10 12:45:25Z

2

You use twice the same table (TABLE2), but in the description you state that you have two tables.

answered Feb 10, 2012 at 12:45

Matthias Vogel

314 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1202047 Over a year ago

sorry I'm mistaken, actually I meant select users.id FROM TABLE1 AS r LEFT JOIN TABLE2 AS users ON (users.firstname = r.firstname AND users.lastname=r.lastname)

Matthias Vogel Over a year ago

as proposed by Brian Hoover, you may try to trim the fields you are using in your join condition.

Pranav · Accepted Answer · 2012-02-10 12:46:43Z

1

I am not sure but i think you want this:

 select users.id 
      FROM TABLE1 AS r 
    INNER JOIN TABLE2 AS users 
        ON (users.firstname = r.firstname AND users.lastname=r.lastname)

answered Feb 10, 2012 at 12:46

Pranav

8,9214 gold badges28 silver badges42 bronze badges

Comments

Vikram · Accepted Answer · 2012-02-10 12:55:45Z

0

select users.id 
      FROM TABLE1 AS r 
    INNER JOIN TABLE2 AS users 
        ON (lower(ltrim(rtrim(users.firstname))) = lower(ltrim(rtrim(r.firstname))) AND lower(ltrim(rtrim(users.lastname)))=lower(ltrim(rtrim(r.lastname))))

answered Feb 10, 2012 at 12:55

Vikram

8,32335 silver badges47 bronze badges

1 Comment

Vikram Over a year ago

do you have single column for firstname and lastname in second table?

Collectives™ on Stack Overflow

problems with Join query

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related