Question

Possible combinations for each ID

  • 15 November 2018
  • 2 replies
  • 1 view

Badge

Hi,

I have a problem and I was wondering if you could help me.

I have a road intersection table like this:

 

OdonymeIDIDRueIDINTNameCountIndex730350012611Robert, Rue20730350013121Roxanne, Rue21730350013132Roxanne, Rue20730350015142Thérèse, Rue21730350012092Rue Raymond32730350012653Robert, Rue20730350012963Rosaire, Rue21730350012974Rosaire, Rue20730350015184Thérèse, Rue21

 

 

I need to create a table of intersection combinations (IDINT). In other words, for each intersection id I want to know the possible intersection 2 streets at a time.

Ex:

IDINTIDRue (1)IDRue(2)112121234243249294239293

 

Someone would have any idea?

thanks

 

 


2 replies

Userlevel 2
Badge +17

I don't know where the "13"s in your example result come from, but if it was just a typo ("9" was correct) and your requirement was to enumerate 2-combinations of IDRue for each group of IDINT, this workflow might help you.

0684Q00000ArLdqQAF.png

Alternatively, the InlineQuerier with this SQL statement does the trick.

select
    a."IDINT",
    a."IDRue" as "IDRue (1)",
    b."IDRue" as "IDRue (2)"
from "data" as a
inner join "data" as b
on b."IDINT" = a."IDINT" and b."IDRue" <> a."IDRue"
order by a."IDINT", a."IDRue"

0684Q00000ArLZpQAN.png

Badge

I may have expressed myself badly, but it's all the combinations I was looking for. I still find a similar solution to yours, using the inlinequerier like you suggest, but with this request:

select a."IDINT", a."IDRue" as IDRue1, b."IDRue" as IDRue2
from "InputTable"as a
cross join "InputTable" as b
where a."IDRue" != b."IDRue"
and a."IDINT" = b."IDINT"

;

Thanks for your time!

Reply