SQL Join Two Tables Without Keys/Relations

SQL join two tables without keys/relations

SELECT * FROM Table1 CROSS JOIN Table2

This will get you every combination of all columns from Table1 and Table2.

SQL join 2 tables without relationship

outer apply might be the easiest solution:

select a.*, b.website
from a outer apply
     (select top 1 b.*
      from b
      where b.id = a.id and
            b.date >= a.start
      order by b.date desc
     ) b;

How to join 2 tables without relationship in SQL

If you want all customers to have a year and their respective orders if they have them per year.

SELECT CUS.ID AS CustomerID, CUS.Name AS CustomerName, YEA.year AS Year, ORD.ID AS OrderID 
FROM Customers AS CUS
   CROSS JOIN Years AS YEA
   LEFT JOIN Orders AS ORD 
        ON CUS.ID = ORD.Customer_ID
        AND ORD.YEAR_ID = YEA.ID;

This will give you and their orders as seperate rows.
If you want the number of orders use this instead:

SELECT CUS.ID AS CustomerID, CUS.Name AS CustomerName, YEA.year AS Year, Count(ORD.ID) AS NrOfOrders 
FROM Customers AS CUS
   CROSS JOIN Years AS YEA
   LEFT JOIN Orders AS ORD 
        ON CUS.ID = ORD.Customer_ID
        AND ORD.YEAR_ID = YEA.ID
GROUP BY CUS.ID, CUS.Name, YEA.Year;

Which will show the number of orders and give each customer a row per year.

Try it out/see it in action here, I added some data to show the multiple orders.

sql join two tables without keys

Your code looks like Oracle, so I'll assume you're using an Oracle database.

If you want to join the two tables, you will need some sort of key, be it a composite or a surrogate one. If there's no way you can find a composite key with the fields in that file, you will have to generate a surrogate one.

I'm not an expert in external tables, but the steps I'd try to follow if trying to do what you need would be

1. create a sequence temp_load_seq;

2. load the whole file into an external table temp_tab, using temp_load_seq.nextval() on each row to save the ordering; this would give me a table that looks like the file, with an added "artificial row number";

3. create another sequence surrogate_key_seq;

4. create my final tables fathers_tab and children_tab; the first one would have an additional id column, the second would have an additional father_id column;

5. write a procedure that loops through temp_tab and inserts its rows into either fathers_tab or children_tab depending on type_record value;

   1. when inserting into fathers_tab I would populate its id field with surrogate_key_seq.nextval();
   2. when inserting into children_tab I would populate its father_id field with surrogate_key_seq.currval().

This way the final tables could be easily filtered and joined on id and father_id.

I'm not 100% sure step 2 would be feasible, because of the usage of a sequence in an external table, but from this article it seems it should be fine.

SQL Join two tables without Relations

Have you tried?

SELECT TABLE1.NAME
FROM TABLE1
WHERE TABLE1.NAME = 'SearchQuery'

UNION

SELECT TABLE2.NAME
FROM TABLE2 
WHERE TABLE2.NAME = 'SearchQuery';

You may want to use UNION ALL if you don't want to exclude repeated values.

To limit your result set you can do something like this:

SELECT * FROM ( HERE GOES ABOVE QUERY ) LIMIT 2;

SQL - Join two tables without a key

You could create a fake key using the rownum pseudo-column, and join according to that:

SELECT t1.col1, t2.col2
FROM   (SELECT   col1, ROWNUM AS rn
        FROM     table1
        ORDER BY col1) t1
JOIN   (SELECT   col2, ROWNUM AS rn
        FROM     table2
        ORDER BY col2) t2 ON t1.rn = t2.rn

EDIT:

A slightly "clunkier", yet more ANSI-friendly approach would be to use the ROW_NUMBER() window function:

SELECT t1.col1, t2.col2
FROM   (SELECT   col1, ROW_NUMBER() OVER (ORDER BY col1) AS rn
        FROM     table1) t1
JOIN   (SELECT   col2, ROW_NUMBER() OVER (ORDER BY col2) AS rn
        FROM     table2) t2 ON t1.rn = t2.rn

Join data from two tables without unique key

I'm not able to use relation due to the lack of unique, primery key,
am I right?

No, this is not right, you can normally JOIN the two tables. It’s also legal to JOIN two tables or result sets on any columns that have what so called eligible data types.

SELECT
  t1.ProductId, t1.year, t1.sales, t2.category
FROM table1 AS t1
INNER JOIN table2 AS t2 ON t1.productId = t2.productId;

---

but I would like not to do it every time when user add new data to
table 1

You don't need a ProductId in the second table, you need a CategoryId instead, and add a new column CategoryId to the first table too and declare it as a foreign key. Your tables should look like so:

Table1 (Products):

• ProductId,
• CategoryId,
• Year,
• Sales.

Table2 (Categories):

• CategoryId,
• CategoryName.

Then the category is added once to the categories table table2 and the product is added once to the products table table1. And for each product enter a CategoryId for it. This what so called normalization

You should also define ProductId in table1 and CategoryID in table2 as primary keys, and CategoryID in the first table as foreign key.

---

Related Topics