CODEWITHSUNDEEP
sqljoinwhere-clausesqlbase
Roby Sottini
Roby Sottini

Reputation: 2265

SQL: How to use WHERE instead of OUTER JOIN

I am using an old database call Centura Gupta that doesn't have the join clauses (left join, right join, inner join, outer join). So I need to use where to replace the outer join that I need:

SELECT *
FROM table1
OUTER JOIN table2
ON table1.id_person table2.id_person;

I think that where can only replaces inner join:

SELECT *
FROM table1
WHERE table1.id_person = table2.id_person;

Upvotes: -1

Views: 512

Answers (4)

Steve Leighton
Steve Leighton

Reputation: 840

'Old database called Centura Gupta' ??
Maybe you actually mean OpenText Gupta SQLBase - which is by no means 'old'.
You may be running an 'old' version of SQLBase - but it is now up to v12.2 native 64bit, and outer joins are certainly supported. You can either use the native Gupta outer joins - similar to Oracle (+) or standard ANSI outer joins.
If you want to use ANSI OUTER joins, specify 'ANSIJoinSyntax=1' in your Server side Sql.ini

Go here for more SQLBase Join syntax: Gupta SQLBase Manuals

Native Gupta Outer Join:

SELECT t1.id_person, t2.id_person
From table1 t1 , table t2
Where t1.id_person = t2.id_person(+)

ANSI Outer Join:

SELECT t1.person_id, t2.person_id   
From table1 t1
Left Outer Join table2 t2 ON t1.id_person = t2.id_person
Where <what ever filter you want>

Go here for more SQLBase Join syntax: Gupta SQLBase Manuals

Upvotes: 0

Russell Fox
Russell Fox

Reputation: 5435

I don't know about that particular database, but you might be able to use a correlated subquery to get "joined" data. This will pull all records from table1 and the related info from table2, or NULL for whatever4 and whatever5 if there's no matching id_person in table2:

SELECT t1.whatever1
    , t1.whatever2
    , t1.whatever3
    , (SELECT whatever4 FROM table2 AS t2 WHERE t2.id_person = t1.id_person) AS whatever4
    , (SELECT whatever5 FROM table2 AS t2 WHERE t2.id_person = t1.id_person) AS whatever5
FROM table1 AS t1

Upvotes: 0

Suffer Surfer
Suffer Surfer

Reputation: 15

I used this kind of implementation when I didn't know JOINs. May not be exactly right but something which can get you close:

SELECT *
FROM table1 t1, table2 t2
WHERE t1.id_Person=t2.id_Person;

Upvotes: 0

JoshuaG
JoshuaG

Reputation: 400

You could try to use subqueries instead of join

SELECT *
FROM table1 t1
WHERE id_Person IN 
(
SELECT id_Person
FROM table2 
)

Upvotes: 0

Related Questions