CODEWITHSUNDEEP
sqlt-sql
Marc
Marc

Reputation: 35

Combining results from two queries without common column

I need to be able write only one SQL statement that allows me to include the column called qty_value from the first query into the second query

These are the queries and the results of each one of them:

Query #1:

SELECT 
    [Records].[Name], [Tables_Data].[Street_Number], [Tables_Data].[Qty_Value]
FROM  
    [Tables_Data], [Records]
WHERE 
    [Records].[Name] = 'John' AND
    [Tables_Data].[Street_Number] = '1510' AND
    [Records].[ID] = [Tables_Data].[Record_ID]

Query #2:

SELECT 
    [Records].[Name], [Tables_Data].[Street_Number], [Tables_Data].[Description], [Tables_Data].[Account]
FROM 
    [Records], [Tables], [Tables_Data]
WHERE 
    [Records].[Name] = 'Tim' AND
    [Tables].[Name] = 'Owners' AND
    [Tables].[Record_ID] = [Records].[ID] AND
    [Tables_Data].[Title] LIKE '%Zone%' AND
    [Tables_Data].[Record_ID] = [Records].[ID] AND
    [Tables_Data].[Table_ID] = [Tables].[ID]

Results

Records.Name, Street_Number, Qty_Value
--------------------------------------
John, 1510, 6         
John, 1510, 3
John, 1510, 6

Records.Name,  Street_Number, Description, Account
--------------------------------------------------
Tim, 2121, St1, 4040
Tim, 5251, St2, 4141
Tim, 6578, St5, 4246

As you notice, there is no common column, therefore I was not able to use JOIN or UNION. If I use the WITH CLAUSE I got the cartesian product (9 records), which is not what I need.

I expect the following:

Records.Name,  Street_Number, Description, Account, Qty_Value
-------------------------------------------------------------
Tim, 2121, St1, 4040, 6
Tim, 5251, St2, 4141, 3
Tim, 6578, St5, 4246, 6

Upvotes: 0

Views: 53

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

You can use full join and row_number():

SELECT rt.*, rtt.*
FROM (SELECT r.[Name], t.[Street_Number], t.[Qty_Value],
             ROW_NUMBER() OVER (ORDER BY r.NAME) as seqnum
      FROM [Tables_Data] t JOIN
           [Records] r
           ON  r.[ID] = t.[Record_ID]
      WHERE r.[Name] = 'John' AND
            t.[Street_Number] = '1510' 
     ) rt FULL JOIN
     (SELECT r.[Name], td.[Street_Number], td.[Description], td.[Account],
             ROW_NUMBER() OVER (ORDER BY r.NAME) as seqnum
       FROM [Records] r JOIN
            [Tables] t
            ON r.[Record_ID] = r.[ID] JOIN
            [Tables_Data] td
            ON td.[Record_ID] = r.[ID] AND
               td.[Table_ID] = t.[ID]
       WHERE r.[Name] = 'Tim' AND
             t.[Name] = 'Owners' AND
             td.[Title] LIKE '%Zone%'
      ) rtt
      ON rt.seqnum = rtt.seqnum;

I also fixed the JOIN syntax and added table aliases.

Upvotes: 1

Related Questions