need to make this work and be db independent

[jangleejat]

In the following query I only get rows returned when a person has friends. I also want to get names and addresses for people who have no friends with a 0 in the result for friends. Problem is that it has to be db independent. If anyone can solve it, please also put in explanation for the logic if you have time.

Select PEOPLE.name,
CONTACTS.address,
Count( distinct FRIENDS.friends_id)
From
PEOPLE,
CONTACTS,
MYGROUP,
FRIENDS
Where CONTACTS.user_ID in (select distinct CONTACTS.user_id from CONTACTS where .....)
And PEOPLE.user_id = CONTACTS.user_id
And MYGROUP.group_id = CONTACTS.group_id
And MYGROUP.some-id = 1
And FRIENDS.user_id = CONTACTS.user_id
group by PEOPLE.name, CONTACTS.address

The expected result is
Name Address Number of Friends
Mary 123 My St. 3
Jaya 453 Stone. 2
Mina 2 Terrace. 0

Currently Mina does not show up because she has no friends ;-)

 

[angiole]

You need to specifiy an outer join to the FRIENDS table.
You can do this in ORacle by appending a (+) to the end of FRIENDS in the From clause, as FRIENDS(+), or use the ANSI standard LEFT OUTER JOIN ... check the DBMS manual. AA 8~)

 

[karluk]

Of course the challenge is to make it database independent. ANSI standard "left outer join" won't work in Oracle, at least prior to Oracle 9i. Other databases likely don't support the Oracle way of specifying outer joins with "(+)".

 

[jangleejat]

Thanks for your posts - I have to do it in one query which will work on any database.

 

[r937]

the problem with trying to be database-independent is that there is darned little left after you remove things like UNION which mysql cannot do

here is an equivalent for LEFT OUTER JOIN --

select A.stuff, B.stuff
from A, B
where A.id = B.ID
union all
select stuff, null
from A
where not exists
( select 1 from B
where id = A.id )

the result set will include all rows of A, with columns from B where there are matching rows and nulls where there aren't

rudy

http://rudy.ca/

 

[angiole]

I agree with Rudy.

Perhaps the next question is 'what is the list of DBMSs you expect this script to run on'.

AA 8~)

 

[jangleejat]

DB2 and Oracle would be good enough

 

[angiole]

Of course, with syntactically different DBMSs this is a challenge.

An option is to investigate dynamic SQL and have it build it the SQL statement for you as proceed through your path of execution. This would require it to be done on both DBMSs, which is as you asked, what you're trying to avoid.

I don't have any solutions, anyone else?

AA 8~)

 

[karluk]

When I was working in DB2, it accepted the (+) syntax for outer joins. I would give it a try and see if it works on both.

Keep in mind, though, that the trend is toward the "left outer join" syntax. Sooner or later you will upgrade to a version that supports it. You may have done so already.

I guess that's the problem with your question. You are not being very specific about which version you're on. We have now determined that "db independent" means "works in both Oracle and DB2". If you would also let us know your specific release level, someone might be able to check the documentation for what syntax is supported.

The alternative is to just write the query and see if it runs on all the databases you need.

 

[tdatgod]

Hi,
what about Teradata? Does it need to run their as well?

----

 

[jangleejat]

I have this clumsy solution, so at least it is working. Need to see if I can refine it Thanks for all your posts. I was not being specific about version because I need it to run on a few versions other than 9i.


Select
PEOPLE.name,
CONTACTS.address,
Count( distinct FRIENDS.friends_id)
From
PEOPLE,
CONTACTS,
MYGROUP,
FRIENDS
Where CONTACTS.user_ID in (select distinct CONTACTS.user_id from CONTACTS where .....)
And PEOPLE.user_id = CONTACTS.user_id
And MYGROUP.group_id = CONTACTS.group_id
And MYGROUP.some-id = 1
And FRIENDS.user_id = CONTACTS.user_id
group by PEOPLE.name, CONTACTS.address
UNION
Select
PEOPLE.name,
CONTACTS.address,
0
From
PEOPLE,
CONTACTS,
MYGROUP
Where CONTACTS.user_ID in (select distinct CONTACTS.user_id from CONTACTS where .....)
And PEOPLE.user_id = CONTACTS.user_id
And MYGROUP.group_id = CONTACTS.group_id
And MYGROUP.some-id = 1
group by PEOPLE.name, CONTACTS.address

 

[angiole]

Just a thought here.

The second query selects a zero for the name and address combos.
It might run quicker if you just selected the name, address, and zero using a distinct, since this will return unique rows, and you can eliminate the group by.
Second, UNION could be replaced by UNION ALL, so that it does not scroll throw all the results to return the unique values, as follows

UNION ALL
Select distinct  
    PEOPLE.name,
    CONTACTS.address,
    0
From     
    PEOPLE,
    CONTACTS,
    MYGROUP
Where  CONTACTS.user_ID in (select distinct CONTACTS.user_id from CONTACTS where .....)
    And PEOPLE.user_id = CONTACTS.user_id
    And MYGROUP.group_id = CONTACTS.group_id
    And MYGROUP.some-id = 1

AA 8~)