CODEWITHSUNDEEP
sqlpostgresql
WDHud
WDHud

Reputation: 121

PostgreSQL using data from 2 tables without a common value, but a boolean

I'm new to Postgres and am trying to find a way that if one value in one table reads true, then to give me a another value in another table.

Here is one table:

itemid | pictureid1 | pictureid2 | smallpic | medpic | largepic 
--------+------------+------------+----------+--------+----------
    100 | 1          | 2          | t        | t      | t
    101 | 3          | 4          | t        | t      | t
    102 | 5          | 6          | t        | t      | t
    103 | 7          | 8          | t        | t      | t
    104 | 9          | 10         | t        | t      | t
    105 | 11         | 12         | t        | t      | t
    106 | 13         | 14         | t        | t      | t
    107 | 15         | 16         | t        | t      | t
    108 | 17         | 18         | t        | t      | t
    109 | 19         | 20         | t        | t      | t

And here is the other:

id |  size  |                size_url                 
----+--------+-----------------------------------------
  1 | small  | https://loremflickr.com/54/54?lock=
  2 | medium | https://loremflickr.com/400/400?lock=
  3 | large  | https://loremflickr.com/1000/1000?lock=

I'm trying to get it where if for a particular itemid it says "true" for smallpic, then it gives me the size_url for small in the other table, with the pictureid1 and pictureid2 in the first table.

Upvotes: 2

Views: 56

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270683

I think you want to join based on the columns. Presumably, you want this for all sizes:

select t1.*,
       t2s.size_url as small_url,
       t2m.size_url as medium_url,
       t2l.size_url as large_url
from table1 t1 left join
     table2 t2s
     on t2s.size = 'small' and t1.smallpic left join
     table2 t2m
     on t2m.size = 'medium' and t1.mediumpic left join
     table2 t2l
     on t2l.size = 'large' and t1.largepic ;

Upvotes: 1

Mafor
Mafor

Reputation: 10691

SELECT t1.pictureid1, t1.pictureid2, t2.size_url 
FROM table1 t1
INNER JOIN table2 t2
ON t1.smallpic = 't' AND t2.size = 'small'

Upvotes: 1

Sujitmohanty30
Sujitmohanty30

Reputation: 3316

If I understood you (as the expected results are missing,

I use cte to gather url information one time and then by checking the value in item table use it in select clause.

with urls
as
(select max(case when id = 1 then url end) surl
       ,max(case when id = 2 then url end) murl
       ,max(case when id = 3 then url end) lurl
   from pic_urls
)
select i.itemid
      ,i.pictureid1
      ,i.pictureid2
      ,case when smallpic 
         then (select surl from urls)
       end small_url
     , case when medpic 
         then (select murl from urls)
       end med_url
     , case when largepic 
         then (select lurl from urls)
       end large_url
  from items i;

Upvotes: 1

Related Questions