-1

What type of JOIN would I use to get table1 and table2 to be matched only once. For example, I have table1 (40 rows) and table2 (10000 rows). But I get table1 repeated over and over when I use a join on table1.LocationArea = table2.Location

What I get:                         What I wish I could get:
t1.LocationArea,t2.Location         t1.LocationArea,t2.Location
---------------------------         ---------------------------
az,az                               az,az
az,az                               null,az
ca,ca                               ca,ca
il,il                               il,il
tx,tx                               tx,tx
tx,tx                               null,tx
az,az                               null,az
                                    null,il
                                    null,ca

I wish to end up with 10000 records in the query.

I have tried inner join, left, and I am using ZOHO reports which does not support outer join's.

SELECT "table1"."LocationArea", "Location" 
FROM "table2"
left join "table1" on  "Location" = "table1"."LocationArea"
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
coco minion
  • 123
  • 2
  • 15
  • What exactly are you using? Your question mentions ZOHO, but you've also tagged the question with mysql, sql-server and postgresql. Which one is it? – Becuzz Jan 19 '15 at 22:51
  • It would also be really helpful if you would show your table structure, some sample data and what you have already tried. Otherwise the best we can do is just guess how to help you. – Becuzz Jan 19 '15 at 22:52
  • Ok sorry was trying to find a solution using any sql program and maybe us it in ZOHO reports because they state that they use most sql programs commands in zoho...(but they don't have all the commands) – coco minion Jan 19 '15 at 22:55
  • have you tried using distinct? – HLGEM Jan 19 '15 at 23:16
  • Yes. but it will chopped table2 down. Maybe im using it wrong. – coco minion Jan 19 '15 at 23:26
  • SELECT distinct"table1"."LocationArea", "Location" FROM "table2" left join "table1" on "Location" = "table1"."LocationArea" and it give me back 19 records. im trying to keep the 10,000 – coco minion Jan 19 '15 at 23:28

2 Answers2

2

Obviously, you have duplicate values for both of the joining columns. Instead of the Cartesian product an [INNER] JOIN would produce for this, you want each row to be used only once. You can achieve this by adding a row number (rn) per duplicate and join on rn additionally.

Each table can have more or fewer dupes for the same value than the other unless you have additional restrictions in place (like a FK constraint) - but there is nothing in your question. To keep all rows one would use a FULL [OUTER] JOIN. But you want to keep 10000 records in the result, which is the cardinality of table2. So it must be a LEFT [OUTER] JOIN on table1 (with 40 rows) - and exclude possible excessive rows from table1.

SELECT t1."LocationArea", t2."Location"
FROM  (
   SELECT "Location"
        , row_number() OVER (PARTITION BY "Location") AS rn
   FROM   table2
   ) t2
LEFT JOIN (
   SELECT "LocationArea"
        , row_number() OVER (PARTITION BY "LocationArea") AS rn
   FROM   table1
   ) t1 ON t1."LocationArea" = t2."Location"
       AND t1.rn = t2.rn;

Works for Postgres or SQL Server. MySQL doesn't support window functions, you would need a substitute:

To be clear: LEFT JOIN is just shorthand for LEFT OUTER JOIN, so you are already using an outer join. Your statement is a misunderstanding:

I am using ZOHO reports which does not support outer join's.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0
SELECT * FROM table1 LEFT JOIN table2 ON `table_1_primary_key` = `table_2_primary_key`

Or SELECT colname FROM table1 LEFT JOIN table2 ON table_1.colname = table_2.colname

Depending on the structure of your database

Ahmad Tijani
  • 392
  • 3
  • 10