0

my first table has about 18K records

so when i

select * from table2 i get about 18k

i'm trying to do a join on it as follows, but i'm getting like 26K back.. what am i doing wrong? i though it's supposed to return all of the "right" aka table2 records plus show me whatever value matches from the first in a separate column...

Select t1.fID , t2.*
FROM table1 t1 right join table2 t2 on t1.fName = t2.f

here is an exmaple of my tables:

table 1: fID, fName

table 2: id, f, address, etc

i need to get all records from table 2, with an fID column, whenever f=fName

Madam Zu Zu
  • 6,437
  • 19
  • 83
  • 129

4 Answers4

3

table1 has many rows with a value of fname that matches the same in table2.

Example, say 5k rows table2 have no matching rows in table1, you have a average of 2 rows in table 1 for each of the remaining 13k table2 rows

Because you have also asked for a column for table1, this will happen. You'll note multiple t1.fId values for a given t2.fname. Or NULLs

gbn
  • 422,506
  • 82
  • 585
  • 676
  • so, how would i go about getting all records from table 2, with a matching column from table 1, if there is one.. – Madam Zu Zu Jan 05 '11 at 20:30
  • @xrum...which matching row would you like to display the column from...as gbn explained, there are multiple matching rows. – Gerrat Jan 05 '11 at 20:34
  • @xrum: you are getting this. You can't reduce the rows unless you 1. aggregate and take perhaps the MIN(t1.fId) or 2. decide not to have t1.fId at all – gbn Jan 05 '11 at 20:36
  • @Gerrat - table 2 has names and all kids of other information, table 1 has names and ids, i need to get everything out of table 2, but also have a matching id next to the name.... if there is one – Madam Zu Zu Jan 05 '11 at 20:36
  • @xrum: you need a way of matching a row in table 2 to a row in table 1 in addition to the name – stusherwin Jan 05 '11 at 20:43
  • there's nothing else in that table :( – Madam Zu Zu Jan 05 '11 at 20:45
  • ok, i found another field i can join the two tables by. but how do i do two joins at the same time??? Select distinct t2.siteID, t1.facilityID , t2.* FROM table1 t1 right outer join table2 t2 on t1.facilityName = t2.facility and table1 t1 right join table2 t2 on t1.siteCode = t2.siteID – Madam Zu Zu Jan 05 '11 at 20:51
2

If t1.fName and t2.f aren't unique identifiers for their tables, you will find that rows from table1 are being joined with multiple rows from table2.

stusherwin
  • 1,836
  • 19
  • 19
0

The RIGHT JOIN keyword Return all rows from the right table (table_name2), even if there are no matches in the left table (table_name1).See Right Join

So it looks like you do not have your matching criteria set correctly or you have no matches.

Eppz
  • 3,178
  • 2
  • 19
  • 26
  • Because Right join gets all records from the right table regardless of whether or not a match exists. – Eppz Jan 25 '11 at 18:20
0

This is possible when some fName values are repeated in Table2 and/or Table 1.

Run these Queries and See:

SELECT fName, COUNT(1) FROM Table2 GROUP BY fName HAVING COUNT(1) > 1

SELECT fName, COUNT(1) FROM Table1 GROUP BY fName HAVING COUNT(1) > 1
Chandu
  • 81,493
  • 19
  • 133
  • 134
  • 1
    FYI, COUNT(1) adds no benefit... http://stackoverflow.com/questions/1221559/count-vs-count1/1221649#1221649 – gbn Jan 05 '11 at 20:29
  • I don't want to start this again :).. I just follow it as a practice and I don't expect it to be performing better than COUNT(*) – Chandu Jan 05 '11 at 20:32