CODEWITHSUNDEEP
sqldatabasejoinself-join
Murchy
Murchy

Reputation: 21

SQL joining a table on itself (if possible)?

I have a table like so:

room code     facility
A001              1
A001              2
A002              3
A003              1

Etc

is there a way that i can join this table as follows

room code    facility 1      facility 2
A001             1               2
A002                             3
A003             1

I need it like this so that I can test what rooms have what facility's and then on my html page i will show a list of them based upon which facility's have been filtered by the user

hope this makes sense!

thanks in advance

Upvotes: 0

Views: 90

Answers (3)

user330315
user330315

Reputation:

The following should do this (although it will not put 3 under facility_2 but under facility_1.

select t1.room_code, 
       t1.facility as facility_1,
       t2.facility as facility_2
from the_table t1
  left join the_table t2 
         on t1.room_code = t2.room_code
        and t1.facility <> t2.facility;

This also assumes that there are never more than two rows with the same room_code.

If you have an unknown number of facilities per room you could aggregate them into an array or a comma separated list which you can then deal with in your front end.

Something like this (for Postgres as you didn't mention your DBMS)

select room_code,
       string_agg(facility,',') as facilities, -- gives a comma separated list
       array_agg(facility) as facilities_array -- gives an array of integers
from the_table
group by room_code;

Upvotes: 0

steinmas
steinmas

Reputation: 398

Try this:

SELECT first.roomcode, first.facility, second.facility
FROM mytable first
LEFT JOIN mytable second on first.roomcode = second.roomcode AND first.facility != second.facility

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You don't need to join it. You can just aggregate:

select roomcode,
       min(facility) as facility1,
       (case when min(facility) <> max(facility) then max(facility) end) as facility2
from t
group by roomcode;

Upvotes: 1

Related Questions