0

I'm currently trying to pull in some rows, based on the contents of an array found in a column from another table. So far I have:

SELECT * FROM coaches WHERE id = ANY(SELECT unnest({23,23,13}) FROM stations WHERE id = 1

All is fine, but I only get back one entry for number 23. I'm assuming this is to cut down on duplicates, but I actually want the duplicates. I want all the data for number 23 twice, and then number 13 once, to match up with the array of {23,23,13}. I also want it to arrive in the same order.

Hopefully this makes sense... any help would be great. I'm new to this, so sorry if its obvious.

James Douglas
  • 3,328
  • 2
  • 22
  • 43
Action_Turtle
  • 150
  • 11

1 Answers1

2

I think you need a query like this.

 SELECT * FROM 
 ( SELECT unnest(coach_ids) as coach_id FROM  
     stations  WHERE id = 1 ) s WHERE EXISTS ( select id FROM coaches c
                                 where c.id = s.coach_id ) ;

DEMO

EDIT:

you said,

I seem to only get the array IDs come back though, no data.

You should use a JOIN in that case. For maintaining proper ORDER you could use generate_subscripts() along with UNNEST 

SELECT c.* FROM 
 ( SELECT unnest(coach_ids) as coach_id
  ,generate_subscripts(coach_ids, 1) AS idx FROM  
     stations  WHERE id = 1 ) s  JOIN  coaches c
                                 ON  c.id = s.coach_id
                                 ORDER BY s.idx;

DEMO2

Kaushik Nayak
  • 30,772
  • 5
  • 32
  • 45
  • thanks for the code. I seem to only get the array IDs come back though, no data... ```select id FROM coaches c where c.id = s.coach_id``` I have swapped id to * in the select, but no joy... ```select * FROM coaches c where c.id = s.coach_id``` – Action_Turtle Dec 31 '17 at 10:55
  • @Action_Turtle : Edited with a new query. check if it works for you and let me know. – Kaushik Nayak Dec 31 '17 at 16:18
  • I was just this second looking at JOIN @kaushik-nayak , as i thought this was needed! I have now implemented your code, and it works apart from the order is not respected. It returns in the order of the ID's, so 1,2,3,4 not 3,2,4,1 for example... really close, so thank you for your efforts! – Action_Turtle Dec 31 '17 at 16:26
  • @Action_Turtle : You are welcome and a Happy New Year. Please do up vote the answer if you appreciate my efforts. Thanks! – Kaushik Nayak Dec 31 '17 at 16:52
  • Hi @KaushikNayak will do. But do you know how to keep it in order though? – Action_Turtle Dec 31 '17 at 16:53
  • @Action_Turtle : Please check, I've just shown you one of the techniques to do that. You could refer https://stackoverflow.com/questions/23830991/parallel-unnest-and-sort-order-in-postgresql for more options if you prefer. – Kaushik Nayak Dec 31 '17 at 17:08
  • Sorry, didn't see that. Perfect! Thank you for your patience. All working as intended now. thanks again, have a happy new year – Action_Turtle Dec 31 '17 at 17:18