0

I have a situation here. Currently I have

  • two polling tables (poll_one and poll_two) which will poll data from outside data-source. This two tables will keep interchange between each other.
  • one checking table (check_poll) which tells me which tables (poll_one or poll_two) is active
  • one user edit table (edit) which stores exact same columns and names as the two poll tables.

Basically I wan to create a dbview that joins the edit table and the active poll table. However, I am not sure how to do a conditional dbview to first check which polling table is active before performing my query.

I cannot use stored procedures as this dbview will be used for my hibernate mapping.

Below is a sample definition of my dbview:

SELECT 
CASE 
WHEN u.name IS NULL THEN p.name ELSE u.name
END AS name,

CASE 
WHEN u.size IS NULL THEN p.size ELSE u.size
END AS size,

//same for the rest of columns

FROM user_edit_table u 
LEFT JOIN poll_one p //I wan to make this conditional
ON p.id = u.id

Is it possible? Is there any other ways?

Thanks for all the replies, An example is shown in the Image Below: enter image description here

So based on the check table, I will choose which table I wan to left join. There is no foreign relation between the tables. All are stand alone.

  • 2
    can you add more details about table / foreign key relation? – Spike Oct 13 '16 at 7:06
  • 3
    Edit your question and add some sample data and the expected output based on that data. Formatted text please, no screen shots – a_horse_with_no_name Oct 13 '16 at 7:17
2

It's not exactly clear how the one or the two table is to be chosen but something like this should work (I assume that the check_poll table has a single row always):

SELECT
    COALESCE(p.name, u.name) AS name,
    COALESCE(p.size, u.size) AS size
FROM check_poll AS c
  CROSS JOIN user_edit_table AS u 
  LEFT JOIN poll_one AS p //I wan to make this conditional
    ON p.id = u.id
WHERE c.poll = 1        -- whatever shows which poll table to choose

UNION ALL

SELECT
    -- same list
FROM check_poll AS c
  CROSS JOIN user_edit_table u 
  LEFT JOIN poll_two p 
    ON p.id = u.id
WHERE c.poll = 2 ;

or even combine the two subqueries in one:

SELECT
    COALESCE(p1.name, p2.name, u.name) AS name,
    COALESCE(p1.size, p2.size, u.size) AS size
FROM check_poll AS c
  CROSS JOIN user_edit_table AS u 
  LEFT JOIN poll_one AS p1 
    ON  p1.id = u.id
    AND c.poll = 1 
  LEFT JOIN poll_two AS p2 
    ON  p2.id = u.id
    AND c.poll = 2 ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.