Join 2 postgres sql queries

Question

I am trying to join two Postgres queries for a The foreman report. I am trying to see which servers are assigned to puppet class 180

The first Query is:

select puppetclass_id , host_id from host_classes where puppetclass_id = 180

the results look as follows

 puppetclass_id | host_id 
----------------+---------
            180 |     378
            180 |     377

The Second Query is:

select id, name, operatingsystem_id, enabled from public.hosts

the results look as follows:

 id  |            name             | operatingsystem_id | enabled 
-----+-----------------------------+--------------------+---------
 404 | s4-somedevserver- 4r5       |                 17 | t
 411 | mob-omedevserver- 4r2       |                 19 | t

I am trying to match the host_id of the first query and the id of the second query together, but remove duplicates. I have the below query, but it just displayes all our servers and not only servers matching puppetclass 180

SELECT DISTINCT id, name, operatingsystem_id
FROM
  (SELECT puppetclass_id , host_id
   FROM host_classes
   WHERE puppetclass_id = 180) t
CROSS JOIN
  (SELECT id, name, operatingsystem_id, enabled
   FROM public.hosts) m
ORDER BY id

Any pointers in the right direction would be appreciated

Tim Biegeleisen · Accepted Answer · 2018-04-13 06:38:37Z

2

I think you just want an INNER JOIN here:

SELECT DISTINCT t2.*
FROM host_classes t1
INNER JOIN public.hosts t2
    ON t1.host_id = t2.id
WHERE
    t1.puppetclass_id = 180
ORDER BY t2.id;

You are currently doing a CROSS JOIN between the two tables, which will tend to give the cross product of all records from each table.

answered Apr 13, 2018 at 6:38

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rezizter Over a year ago

Perfect, working. thank you soo much for your assistance

Collectives™ on Stack Overflow

Join 2 postgres sql queries

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related