join

Question

join

mtz676

SSCrazy Eights

Points: 9212
More actions
March 12, 2024 at 8:22 am

#4393859

I have 1 record(1row*2columns) from 1 select
and
a different select which can result in multiple rows but will always have 3 columns.
How can I repeat the 1 row next to the result set from the second select.
No common columns from these 2 selects...

Viewing 6 posts - 1 through 5 (of 5 total)

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Your final resultset cannot contain a mixture of numbers of columns and datatypes. But you can create a wider dataset, eg

SELECT Col1, Col2, Col3, Col4 = NULL, Col5 = NULL
from qry1
UNION ALL
SELECT Col1 = NULL, Col2 = NULL, Col3 = NULL, Col4, Col5
from qry2

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

mtz676 SSCrazy Eights Points: 9212 More actions · Answer 2

My first select :

A E:\1.mdf D

A_1 E:\2_1.ndf D

A_log F:\3.ldf L

My second select:

SrvA Db1

This is what the result looked like from the query posted. Thank you for this...but

A E:\1.mdf D NULL NULL

A_1 E:\2_1.ndf D NULL NULL

A_log F:\3.ldf L NULL NULL

NULL NULL NULL SrvA Db1

...This is what I need

A E:\1.mdf D SrvA Db1

A_1 E:\2_1.ndf D SrvA Db1

A_log F:\3.ldf L SrvA Db1

frederico_fonseca SSCoach Points: 16154 More actions · Answer 3

this is why you should supply consumable data and desired output.

so on above example, how do we link between tbl1 and tbl2? this is assuming that there are more than 1 rows on tbl2

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

Like this?

WITH qry1
AS (SELECT *
    FROM
    (
        VALUES
            ('SrvA', 'Db1')
    ) x(Col4, Col5) )
    ,qry2
AS (SELECT *
    FROM
    (
        VALUES
            ('A', 'E:\1.mdf', 'D')
           ,('A_1', 'E:\2_1.ndf', 'D')
           ,('A_log', 'F:\3.ldf  ', 'L')
    ) y(Col1, Col2, Col3) )
SELECT qry2.Col1
      ,qry2.Col2
      ,qry2.Col3
      ,qry1.Col4
      ,qry1.Col5
FROM qry2
    CROSS JOIN qry1;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 5

mtz676 wrote:

I have 1 record(1row*2columns) from 1 select
and
a different select which can result in multiple rows but will always have 3 columns.
How can I repeat the 1 row next to the result set from the second select.
No common columns from these 2 selects...

Dude... readily consumable data and desired result output, please! I see you finally provided some but you shouldn't need to be reminded with every post. And, no... I'm not trying to be mean... I'm trying to tell you how to help yourself.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)