Cross join and merge

  • shrenikpaul

    SSC Rookie

    Points: 32

    Hi,

    I am not an SQL expert and hence looking for a solution to one of my problem.

    I have 3 tables. On joining I am getting cartesian product which I do not need.

    Instead I want non-repeated values.

    I have attached the sample tables. Can someone help me for it?

    Thanks.

     

     

    Attachments:
    You must be logged in to view attached files.
  • Phil Parkin

    SSC Guru

    Points: 243772

    Instead of

    SELECT cols

    ...

    Try

    SELECT DISTINCT cols

    ...

    Does that help?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Jonathan AC Roberts

    SSCoach

    Points: 16953

    You haven't shown the query you have written or the results you are getting.

    SELECT t1.ID, t1.Name, t2.ValueA, t3.ValueB
    FROM Table1 t1
    INNER JOIN Table2 t2
    ON t2.ID = t1.ID
    INNER JOIN Table3 t3
    ON t3.ID = t1.ID
    AND t3.ID = t2.ID
    ORDER BY 1, 2, 3, 4

    I can't imagine what query you need to get 'N/A' in ValueB, for example, with Mike there are joins on all the tables which would give you 'CA1' where you have 'N/A'.

     

     

     

  • drew.allen

    SSC Guru

    Points: 76635

    I think this is what he is looking for.

    WITH t2 AS
    (
    SELECT t2.ID, t2.ValueA, ROW_NUMBER() OVER(PARTITION BY t2.ID ORDER BY t2.ValueA) AS rn
    FROM Table2 AS t2
    )
    , t3 AS
    (
    SELECT t3.ID, t3.ValueB, ROW_NUMBER() OVER(PARTITION BY t3.ID ORDER BY t3.ValueB) AS rn
    FROM Table3 AS t3
    )

    SELECT t1.ID, t1.Name, t2.ValueA, t3.ValueB
    FROM Table1 t1
    INNER JOIN t2
    ON t2.ID = t1.ID
    LEFT OUTER JOIN t3
    ON t3.ID = t2.ID
    AND t3.rn = t2.rn
    ORDER BY t1.ID, t1.Name, t2.rn

    Note that equality is transitive, so that if t2.ID = t1.ID and t3.ID = t2.ID, then t3.ID = t1.ID MUST be true and you don't need to specify it in your query.

    Also, you should NEVER use ordinal column positions in published code.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply