Problem with Query from 2 tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problem with Query from 2 tables

Author

Topic

spowell
Starting Member

23 Posts

Posted - 2008-10-07 : 07:43:51

I am having some problems with a query which is pulling records from 2 tables. Some records are in both tables so I want to make sure there are no duplicates in there as well. What I have put below works ok for the records out of the 1st table but can't seem to make the amendments for it to work with the 2nd one as well.

The other table is called dbo.contactdetails and the column I need to select is called listsourcefk. The pk for this table is different as well and is pksystemrecord.

Any help will be greatly appreciated

select a.title,
a.firstname,
a.surname,
a.address1,
a.address2,
a.address3,
a.town,
a.county,
a.postcode
from dbo.ContactInfo as a
inner join (
select max(pkcustomerrecord) as maxrec
from dbo.ContactInfo
where consultant in (
'Mike',
'Kevin'
'Terry')
and postcode is not null
or balance not in ('0', ' ', 'null')
and postcode is not null
group by
address1
) as a1 on a1.maxrec = a.pkcustomerrecord
order by
a.postcode desc,
a.address1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 07:49:04

can you show some sample data from both table and then give your expected output from them?

spowell
Starting Member

23 Posts

Posted - 2008-10-07 : 10:31:02

Contactinfo has the following columns

pkcustomerrecord,title,firstname,surname,address1,address2,address3,town,county,postcode,consultant,balance

1,mr,sam,smith,1 the street,the place,somewhere,the town,the county,AA1 2RR,mike,2000
2,mrs,jean,jones,2 the street,the place,somewhere,the town,the county,BB1 3RR,terry,500
3,miss,clare,harris,2 the street,the place,somewhere,the town,the county,CC1 2BB,null,3000

and contactdetails has

pksystemrecord,title,firstname,surname,address1,address2,address3,town,county,postcode,listsourcefk

234,mr,tom,jones,2 the close,the village,somewhere,the town,the county,VV2 1RT,555
1234,mrs,tina,james,2 the close,the village,somewhere,the town,the county,WW1 3RT,555

There are also other columns in the tables but none that are relevant.

People could be in either table more than once or in both so don't want duplicate records

The output I need is

Title,Firstname,Surname,Address1,Address2,Address3,Town,County,Postcode

Hope this helps

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 12:03:14

[code]select distinct Title,Firstname,Surname,Address1,Address2,Address3,Town,County,Postcode
from
(
select Title,Firstname,Surname,Address1,Address2,Address3,Town,County,Postcode from Contactinfo
union all
select Title,Firstname,Surname,Address1,Address2,Address3,Town,County,Postcode from contactdetails
)t[/code]

spowell
Starting Member

23 Posts

Posted - 2008-10-07 : 13:41:29

Sorry I didn't explain myself very well. from the contactinfo table I only need to select people if

Mike, Kevin or Terry are in the consultant box or
Balance is '0', ' ', 'null'

and from the contactdetails table if

listsourcefk is either 535 or 545

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 13:50:02

put them in where condition

select distinct Title,Firstname,Surname,Address1,Address2,Address3,Town,County,Postcode
from
(
select Title,Firstname,Surname,Address1,Address2,Address3,Town,County,Postcode from Contactinfo 
where consultant in ('Mike','Kevin','terry')
or coalesce(nullif(replace(balance,'','null'),'null'),0)=0 
union all
select Title,Firstname,Surname,Address1,Address2,Address3,Town,County,Postcode from contactdetails 
where listsourcefk in (535,545)
)t

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources