Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Simple sql join query

Status
Not open for further replies.

cfcProgrammer

Programmer
Mar 1, 2006
88
CA
Hi,

I am new to sql query. Could someone please take a look at the below query and advise me why I am getting every record from the customer table when all I want is the customer id I have identified in the select statement. I need to have the fields identified in the main select as part of the results.

SELECT m.Id as 'Mobile ID',
m.SerialNum as 'Serial NO',
m.CustomerId as 'Mobile Customer ID',
c.Id as 'Customer ID',
c.ParentCustomerId as 'Parent ID',
c.ARAccountCode as 'Account Code',
c.ContactCompanyName as 'Contact Name'
FROM Mobile m, Customer c
where
m.CustomerId in(SELECT c.Id
FROM Customer c
where (c.Id = 22151 or c.ParentCustomerId = 22151)
and c.IsActive ='1')
and m.IsActive ='1'

Thanks
CfcProgrammer

cfcProgrammer
 
Hi

Probably because the Customer table is used without any restriction :
CfcProgrammer said:
FROM Mobile m, Customer c

Try with a [tt]join[/tt] to define the relation between the records in Mobile and Customer, so only the records which have their pair in the other table pass through :
Code:
[b]from[/b] Mobile m
[b]join[/b] Customer c [b]on[/b] c.[i]somefield[/i] = m.[i]otherfield[/i]


Feherke.
feherke.github.io
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top