CODEWITHSUNDEEP
phpsql
Peter Brooks
Peter Brooks

Reputation: 49

Combine the results of 2 queries, where there is no join or union

I would like to append the results of 2 queries into one result set.

SELECT n.member_no, n.surname, n.first_name
            FROM `names` AS n
            WHERE member_no = '1003';

SELECT s.registration
                FROM `system` AS s
                WHERE s.RECNUM = 1;

This must return one record with data from the names table plus data from the system (one record) table

Member_no | surname |  first_name  |  registration
--------------------------------------------------
  1003    |  Brown  |     Peter    |  My registration

Upvotes: 0

Views: 45

Answers (3)

Divyesh patel
Divyesh patel

Reputation: 987

Try this one.

SELECT DISTINCT n.member_no, n.surname, n.first_name,s.registration
FROM `names` AS n, `system` AS s
WHERE s.RECNUM = 1 AND member_no = '1003';

Upvotes: 0

Guilherme Martin
Guilherme Martin

Reputation: 847

You can use CrossJoin:

SELECT n.member_no, n.surname, n.first_name, s.registration
            FROM names AS n
            CROSS JOIN system s
            WHERE n.member_no = '1003' and s.RECNUM = 1;

Upvotes: 2

Ed Bangga
Ed Bangga

Reputation: 13006

we can correlate your registration and name tables based on row_number(). You may want to try below query.

SELECT rn.member_no, rn.surname, n.first_name, s.registration
FROM
    (SELECT member_no, surname, first_name, row_number() over (order by member_no) rn
            FROM `names`) n           
LEFT JOIN 
    (SELECT row_number() over (order by RECNUM) rn, registration
                FROM `system`) s on s.rn = n.rn              
WHERE n.member_no = '1003'

Upvotes: 0

Related Questions