Luis Cazares - Wednesday, May 24, 2017 1:13 PM
I wish... Do you recall MSDE.... Yep...
I've figured out another way of doing but it can be a bit "heavy"
CREATE VIEW vw_data AS
SELECT
t0.id id_agent, t0.name agent, t1.*, t2.id_product id_p_c, t2.number
FROM
agents t0
CROSS JOIN ( SELECT t0.id id_credentialing, t0.name credentialing, t1.id_product, t2.name product FROM credentialings t0 INNER JOIN credentialings_products t1 ON t0.id = t1.id_credentialing INNER JOIN products t2 ON t2.id = t1.id_product) t1
LEFT JOIN agents_credentialings t2 ON t2.id_agent = t0.id AND t2.id_credentialing = t1.id_credentialing AND (t2.id_product IS NULL OR t2.id_product = t1.id_product)
GO
SELECT * FROM (
SELECT * FROM vw_data t0 WHERE id_p_c IS NOT NULL
UNION ALL
SELECT * FROM vw_data t1 WHERE id_p_c IS NULL AND NOT EXISTS (SELECT 1 FROM vw_data t2 WHERE t1.id_agent = t2.id_agent AND t2.id_credentialing = t1.id_credentialing AND t1.id_product = t2.id_p_c AND t2.id_p_c IS NOT NULL)
) t