Problem in sql query

I have two tables called customer and items.

开发者_JAVA技巧With single query I want to get all the custid and customername whose custid='Custid1' and itemid whose custid='Custid1'.

How can I build this query?? custid is the primary key of customer table. custid is the foreign key of items table.

I have written this query its not listing all the custid, only listing custid thrice... Here is my query:

create procedure spGetCustomer
as
begin
  select a.custid, a.custname,b.itemid  
  from customer a inner join items b on a.custid = b.custid 
  WHERE b.custid='custid1'
end

Your question isn't quite clear - what is it you really want??

The JOIN you have will list all customers with items where the customer.custid is custid1. But that doesn't seem to be what you're looking for.....

Could it be you're looking for all customers - with or without items - that have a specific custid?? Try something like this:

SELECT 
    c.custid, c.custname, i.itemid  
FROM 
    dbo.customer c
LEFT OUTER JOIN 
    dbo.items i ON c.custid = i.custid 
WHERE 
    c.custid = 'custid1'

If the items.custid is indeed a foreign key into the customer table, there shouldn't be any items around that have custid = custid1 that aren't joined to an existing customer.

If your question actually relates to how you pass the parameter in, your proc should be:

create procedure spGetCustomer(@custid1 varchar(100)) as begin select a.custid, a.custname,b.itemid
from customer a inner join items b on a.custid = b.custid WHERE b.custid=@custid1 end