Clever Geek Handbook

SQL - multiple records in one instance in c1 with its correlation mapping in c2

From the three tables, using an inner join, my results look like this:

╔═══════╦═══════╦═══════╗
β•‘ t1.c1 β•‘ t2.c1 β•‘ t3.c1 β•‘
╠═══════╬═══════╬═══════╣
β•‘  1a   β•‘  2a   β•‘  3a   β•‘
β•‘  1a   β•‘  2b   β•‘  3b   β•‘
β•‘  1a   β•‘  2c   β•‘  3c   β•‘
β•‘  1b   β•‘  2a   β•‘  3a   β•‘
β•‘  1b   β•‘  2b   β•‘  3b   β•‘
β•‘  1b   β•‘  2c   β•‘  3c   β•‘
β•šβ•β•β•β•β•β•β•β•©β•β•β•β•β•β•β•β•©β•β•β•β•β•β•β•β•

Can I do it like below?

╔═══════╦═══════╦═══════╗
β•‘ t1.c1 β•‘ t2.c1 β•‘ t3.c1 β•‘
╠═══════╬═══════╬═══════╣
β•‘  1a   β•‘  2a   β•‘  3a   β•‘
β•‘       β•‘  2b   β•‘  3b   β•‘
β•‘       β•‘  2c   β•‘  3c   β•‘
β•‘  1b   β•‘  2a   β•‘  3a   β•‘
β•‘       β•‘  2b   β•‘  3b   β•‘
β•‘       β•‘  2c   β•‘  3c   β•‘
β•šβ•β•β•β•β•β•β•β•©β•β•β•β•β•β•β•β•©β•β•β•β•β•β•β•β•

Here is the code I'm using:

SELECT t1.c1, t2.c1, t3.c1
FROM t1
  INNER JOIN t2
    ON t1.c1=t3.c1
  INNER JOIN t3
    ON t3.c1=t2.c1
WHERE t1.c2 = 'n'
ORDER BY t1.c1 ASC
+3


source to share


2 answers


I think you can use this:



SELECT     CASE WHEN ROW_NUMBER() OVER (PARTITION BY t1.c1 ORDER BY t2.c1,t3.c1) = 1 THEN t1.c1 END, 
           t2.c1, t3.c1
FROM       t1
CROSS JOIN t2
INNER JOIN t3
ON         t3.c1 = t2.c1
ORDER BY   t1.c1
+4


source


You can use CASE

and ROW_NUMBER

.

WITH Cte AS(
    SELECT *,
        RN = ROW_NUMBER() OVER(PARTITION BY t1c1 ORDER BY (SELECT NULL))
    FROM (
        SELECT 
            t1c1 = t1.c1, 
            t2c1 = t2.c1, 
            t3c1 = t3.c1
        FROM t1
        INNER JOIN t2
            ON t1.c1 = t2.c1
        INNER JOIN t3
            ON t3.c1 = t2.c1
        WHERE t1.c2 = 'n'
    )t
)
SELECT
    t1c1 = CASE WHEN RN = 1 THEN c.t1c1 ELSE '' END,
    c.t2c1,
    c.t3c1
FROM Cte c
ORDER BY c.t1c1


I replaced your condition JOIN

with t2

.

+4


source






More articles:


All Articles