The following query:
select unnest(Table2.L) as X, unnest(Table1.O)
from Table1, Table2
where Table1.code = Table2.code
order by X ;
produces the desired results. I would, however, like to exclude (unnested) rows which match some value. Adding a condition to the query, such as the following:
and unnest(Table2.L) != '-'
obviously does not work. Is this possible? How?
If by unnest(Table2.L) != '-' you mean
throw out all the unnested elements that are
'-'
then use a derived table and filter out the unnested values you don't want:
select *
from (
select unnest(Table2.L) as X, unnest(Table1.O)
from Table1 join Table2 on Table1.code = Table2.code
) dt
where X != '-'
order by X ;
If you mean
ignore all rows from
Table2whereLcontains'-'
then you can use the @> operator to check if L contains a certain element:
select unnest(Table2.L) as X, unnest(Table1.O)
from Table1 join Table2 on Table1.code = Table2.code
where not Table1.L @> ARRAY['-']
or you could use ANY:
select unnest(Table2.L) as X, unnest(Table1.O)
from Table1 join Table2 on Table1.code = Table2.code
where not '-' = any(Table1.L)
And do yourself a favor by forgetting that implicit joins exist, always use explicit join conditions.
Another way of doing it:
SELECT x, y
FROM (SELECT code, unnest(l) AS x FROM table1) t1
JOIN (SELECT code, unnest(o) AS y FROM table2) t2 USING (code)
WHERE x <> '-'
ORDER BY x;
May of may not be faster. Depends on how selective the WHERE clause is. Run a quick EXPLAIN ANYLYZE.
Note that I untangled table1 and table2, which are reversed in the example. If you are confused by all the clarity, try substituting x -> y in the WHERE and ORDER BY clause.
If you actually want to eliminate occurrences of - on both sides, add AND y <> '-'
to the WHERE clause - making it symmetric (no cuonfusion possible).
I would also ORDER BY x, y, if x is not guaranteed to be unique, to make the sort order stable.
'-' rows. Your answer gives ~34000, so something is wrong somewhere :) I'm checking it now. Also, I was able to include additional columns from Table2 in the query which "mu is too short" provided, but am not able to do so with your answer.