Left Join Complex Query
От | Gregory Wood |
---|---|
Тема | Left Join Complex Query |
Дата | |
Msg-id | 004d01c08d38$2125e330$7889ffcc@comstock.com обсуждение исходный текст |
Ответы |
Re: Left Join Complex Query
|
Список | pgsql-general |
I was wondering if someone might be able to help me with a complex query. I have two tables, ABC (columns A, B, and C) and BC (columns B and C) where two columns from ABC (B and C) are foreign keys into BC. There can be multiple A values for a given BC, or no values at all. I want to do a query so that I get *all* the values from BC, and a true or false value for whether A exists for a given value of BC. I've gotten close, but I still have a problem. What I came up with was: SELECT DISTINCT BC.B,BC.C, CASE WHEN ABC.A=1 THEN 'true' ELSE 'false' END FROM (BC LEFT JOIN ABC ON BC.B=ABC.B AND BC.C=ABC.C) The problem is that although I do get true values if A exists for a given value of BC, I also get false values. In other words, for every 1,2,true, I also get a 1,2,false. I can solve the problem programmatically, but I was hoping to create a view to do all the work for me. I'd love any suggestions! Greg
В списке pgsql-general по дате отправления: