May 24, 2017 at 9:32 am
Hi,
I have 3 main tables... Agents (id, name, ...), Credentialings (id, name), Products (id, name, ...).
Each credentialing can be mandatory for some products, so there's a table Credentialins_Products (id_credentialing, id_product) to relate them.
An agent can have credentialings related to a specific product or for all products, so there's a table Agents_Credentialings (id_agent, id_credentialing, id_lob NULL, number, ...).
Let's say I have
create table agents (id INT, name VARCHAR(50))
create table credentialings (id INT, name VARCHAR(50))
create table products (id INT, name VARCHAR(50))
create table credentialings_products (id_credentialing INT, id_product INT)
create table agents_credentialings (id_agent INT, id_credentialing INT, id_product INT, number VARCHAR(20))
insert into agents values (1, 'Agent 1'),(2, 'Agent 2')
insert into credentialings values (1, 'Credentialing 1')
insert into products values (1, 'Product 1'), (2, 'Product 2')
insert into credentialings_products values (1,1), (1,2)
insert into agents_credentialings values (1, 1, 1, 'Number for p1')
insert into agents_credentialings values (1, 1, null, 'Number for other p')
I'd like to get all Agents with their Credentialings (either they have or not) and information, so I build the following query:
SELECT
t0.id id_agent,
t0.name agent,
t1.*,
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)
ORDER BY t0.id, t1.id_credentialing, t1.id_product
The problem is id_agent = 1 AND id_credentialing = 1 has 2 records with id_product = 1 AND id_product = NULL... If there's a record with id_product filled I'd like to throw away, for that product, the NULL record.
id_agent agent id_credentialing credentialing id_product product number
1 Agent 1 1 Credentialing 1 1 Product 1 Number for p1
1 Agent 1 1 Credentialing 1 1 Product 1 Number for other p
1 Agent 1 1 Credentialing 1 2 Product 2 Number for other p
2 Agent 2 1 Credentialing 1 1 Product 1 NULL
2 Agent 2 1 Credentialing 1 2 Product 2 NULL
This is the result...
But I'd like it to be:
id_agent agent id_credentialing credentialing id_product product number
1 Agent 1 1 Credentialing 1 1 Product 1 Number for p1
1 Agent 1 1 Credentialing 1 2 Product 2 Number for other p
2 Agent 2 1 Credentialing 1 1 Product 1 NULL
2 Agent 2 1 Credentialing 1 2 Product 2 NULL
How can I do this with a fast query?
Thanks,
Pedro
May 24, 2017 at 9:52 am
I believe I have it....
;WITH data AS (
SELECT
t0.id id_agent,
t0.name agent,
t1.*,
t2.id_product id_p_c,
t2.number,
ROW_NUMBER() OVER (PARTITION BY t0.id, t1.id_credentialing, t1.id_product ORDER BY t0.id, t1.id_credentialing, t1.id_product, t2.id_product DESC) d
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)
)
SELECT * FROM data d0 WHERE d = 1
If partition by agent and credentialing and order by agent, credentialing and product DESC (so NOT NULLs come first) and choose the ones with 1.
My problem is that this has to work on older SQL versions that don't have PARTITION BY....
How can I write this without partition by?
Thanks in advance,
Pedro
May 24, 2017 at 1:13 pm
PiMané - Wednesday, May 24, 2017 9:52 AMMy problem is that this has to work on older SQL versions that don't have PARTITION BY....
How can I write this without partition by?Thanks in advance,
Pedro
Do you seriously have systems on SQL Server 2000? Otherwise, the PARTITION BY clause is still available from 2005 to current versions.
May 24, 2017 at 1:32 pm
Luis Cazares - Wednesday, May 24, 2017 1:13 PMPiMané - Wednesday, May 24, 2017 9:52 AMMy problem is that this has to work on older SQL versions that don't have PARTITION BY....
How can I write this without partition by?Thanks in advance,
PedroDo you seriously have systems on SQL Server 2000? Otherwise, the PARTITION BY clause is still available from 2005 to current versions.
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
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply