I have this "view" on three tables:

select count(*) from (telecom left outer join contact left outer join relatie) left outer join relatie as relatie_B

(the table telecom effectively always points to the same parent table "relatie", but this can be via an intermediate table (contact)

How can sort on columns of tbis table "relatie" (and "relatie_B") so that rows can be properly kept together. I currently create a temporary table for this which I "manually" fill with de values I need to sort on, but there must be a more elegant (and faster) way

Thanks in advance!

asked 04 Dec '13, 03:17

Simon%20Door's gravatar image

Simon Door
1111
accept rate: 0%

A small sample with your tables would be helpful - for a "select count(*)" query, it's difficult to guess why a sort order would be necessary at all...

(04 Dec '13, 05:02) Volker Barth

(sorry for the "count(*)", pasted it without carefully looking at it ...)

hope the following example makes it more clear:

select relatie.naam, relatie_B.naam, telecom.number from (telecom left outer join contact left outer join relatie) left outer join relatie as relatie_B

How can I sort on relatie.naam and relatie_B.naam (actually the same column ...) at the same time, so they can be viewed as belonging to the same entity?

(04 Dec '13, 07:52) Simon Door
Replies hidden

Sorry, I still don't get the point. You certainly can specify several columns as sort order, such as

select relatie.naam, relatie_B.naam, telecom.number
from (telecom left outer join contact left outer join relatie)
  left outer join relatie as relatie_B
order by 1, 2

However, I guess that's not what you are asking for...

relatie.naam and relatie_B.naam (actually the same column ...)

The same column name, but of different instances of the same table... - how do you specify the join condition here? (When using a self-reference, i.e. a join between two instances of the same table, you would usually want to join between different rows by using different columns as join condition - such as "FROM person child left join person parent on child.parent_id = parent.id", otherwise you would simply join each row with itself...)

Could you give a small sample of the desired output?

(04 Dec '13, 09:19) Volker Barth

Please show us the whole view, something that has ON conditions that define how the joins work, something that doesn't throw this error...

Could not execute statement.
There is no way to join 't' to 't2'
SQLCODE=-146, ODBC 3 State="42000"
Line 1, column 1

SELECT * FROM t INNER JOIN t AS t2
(04 Dec '13, 10:26) Breck Carter

None of the code you have posted can possibly work, including your "this works!" answer:

select relatie.naam, 
       relatie_B.naam,  
       telecom.number,  
       if relatie.naam is not null 
          then relatie.naam 
          else relatiealias.naam 
       endif as sortable_col 
  from (telecom 
           left outer join contact  
           left outer join relatie)  
        left outer join relatie as relatie_B 
 order by sortable_col

Could not execute statement.
Correlation name 'relatiealias' not found
SQLCODE=-142, ODBC 3 State="42S02"
Line 5, column 1

-- The REAL problem (no join conditions) is revealed after fixing
-- the above error...

select relatie.naam, 
       relatie_B.naam,  
       telecom.number,  
       if relatie.naam is not null 
          then relatie.naam 
          else 'dummy value' 
       endif as sortable_col 
  from (telecom 
           left outer join contact  
           left outer join relatie)  
        left outer join relatie as relatie_B 
 order by sortable_col

Could not execute statement.
There is no way to join 'telecom' to 'contact'
SQLCODE=-146, ODBC 3 State="42000"
Line 1, column 1

We are happy to help, but you must post actual code.

permanent link

answered 05 Dec '13, 07:50

Breck%20Carter's gravatar image

Breck Carter
25.7k427587847
accept rate: 20%

I second your , hm, suggestion...

That being said, in case there are FKs specified between the involved tables, then the JOIN without an ON condition can certainly work (and will be a KEY JOIN by default). However, I don't think it will work here because of the self-join - even if there is a FK relationship within the table's columns I guess a KEY JOIN would fail to know which table alias would be assigned which end of the FK relationship...

Another reason why we need actual code (including the tables's definition)...

(05 Dec '13, 08:18) Volker Barth

Sorry, I, indeed, should have posted more info and had better checked the labels I used before posting.

The actual query was generated by an application and had a lot of unneccessary stuff in it. I tried to simplify things by omitting this en translating some labels ...

And there indeed are fk relations specified between the tables ("telecom" has fk's to "contact" and "relatie" and the "contact" has a fk to "relatie". I know to little of sql to know for sure wether this is a KEY JOIN or not ...

Again sorry for the inconvenience!

(05 Dec '13, 10:31) Simon Door

The problem lies in the fact that either relatie.naam contains null values or relatie_B.naam contains null values. But I've found an easy solution

I've created a "virtual" column and can apparently also sort on this column too!

select relatie.naam, relatie_B.naam, telecom.number, if relatie.naam is not null then relatie.naam else relatiealias.naam endif as sortable_col from (telecom left outer join contact left outer join relatie) left outer join relatie as relatie_B order by sortable_col

and this works!

permanent link

answered 05 Dec '13, 05:51

Simon%20Door's gravatar image

Simon Door
1111
accept rate: 0%

Ah, I see. Well, then you can shorten your query by replacing your IF expression with an ISNULL (or COALESCE) function call, such as

...telecom.number, isnull(relatie.naam, relatiealias.naam) as sortable_col...
(05 Dec '13, 06:34) Volker Barth

Just to add: It is good practise to explain the particular problem (sorting when the according columns can both have nulls) already in the original posting - that would have given many folks the chance to help you fast with a working solution instead of counter-questions...

(05 Dec '13, 06:37) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×10

question asked: 04 Dec '13, 03:17

question was seen: 733 times

last updated: 05 Dec '13, 10:31