Combining Record Sets

[mtorbin]

Hey folks,

How do I combine the two results sets into one:

SELECT userid,firstname,initials,lastname FROM cr_users WHERE userid IN ('1435335','1034861','1425675','1425855')
SELECT userid,addr1,addr2,city,stateprov,country,postalcode,phone FROM cr_useraddr WHERE userid IN ('1435335','1034861','1425675','1425855')

I want to have one result set with all the data above (i.e. each record set would have a name and address associated with it).

Thanks,

- MT

 

[gmmastros]

SELECT 	U.userid,
        U.firstname,
        U.initials,
        U.lastname,
        A.addr1,
        A.addr2,
        A.city,
        A.stateprov,
        A.country,
        A.postalcode,
        A.phone 
FROM 	cr_users As U
        Left Join cr_useraddr As A
          On U.userId = A.userId
WHERE 	A.userid IN ('1435335','1034861','1425675','1425855')

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mtorbin]

I got this far, but it only returns the first result repeated the number times that I have records:

SELECT cr_users.userid,cr_users.firstname,cr_users.initials,cr_users.lastname,cr_useraddr.addr1,cr_useraddr.addr2,cr_useraddr.city,cr_useraddr.stateprov,cr_useraddr.country,cr_useraddr.postalcode,cr_useraddr.phone FROM cr_users,cr_useraddr WHERE cr_users.userid IN ('1435335','1034861','1425675','1425855')

Ideas?

 

[mtorbin]

George,

I get an "invalid character" error when I try your code.

- MT

 

[gmmastros]

Change your where clause to...

WHERE [!]cr_users.userid = cr_useraddr.userid and[/!] cr_users.userid IN ('1435335','1034861','1425675','1425855')

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mtorbin]

SELECT cr_users.userid,
       cr_users.firstname,
       cr_users.initials,
       cr_users.lastname,
       cr_useraddr.addr1,
       cr_useraddr.addr2,
       cr_useraddr.city,
       cr_useraddr.stateprov,
       cr_useraddr.country,
       cr_useraddr.postalcode,
       cr_useraddr.phone
FROM cr_users,cr_useraddr
WHERE cr_users.userid = cr_useraddr.userid AND  cr_users.userid IN ('1435335','1034861','1425675','1425855')

Alas, still error message ORA-00911: Invalid Character

- MT

 

[gmmastros]

Both queries I posted would work with SQL Server. I suggest you post this question in one of the oracle forums here at tek-tips.

Good luck.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom