help with sql query

I am trying to fill a dataset with customers in Table1, but i am searching by ItemID(probably from Table3). I can't figure out the sql query for this request.
Maybe something like:"select * from Table1 ...and now I think I should some how inner join Table1 and Table3 so i can select according to ItemID.

I have the three following tables:

Table1--It's primary key is the CustID identity field:

CustID CustName CustEmail ...

Table2--It's primary key is the ItemID identity field:

ItemID ItemName

Table3--ItemID and CustID function together for the primary key of the table:

ItemID CustID
1 1
2 1
2 2
3 2
1 3
3 3

ThanksTry this

select customers.* from table1, table2,table3 where
table1.custid=table3.custid and table3.itemid=table2.itemid

HTH|||Thanks!

Help with Query

I have the following table:
TableName: Customers
ID - Integer
CustomerID - VarChar
Name - VarChar
LoanNo= Int
Amount = Money
ID CustomerID Name LoanNo Amount
1 Chuck1 Chuck 1 2.00
2 Mike1 Mike 1 4.00
3 Dinah1 Dinah 1 6.00
4 James1 James 1 1.00
5 James1 James 2 3.00
6 Chuck1 Chuck 2 5.00
What I want to do is return all the Customers but only their highest LoanNo
(like MAX(LoanNo)).
Example:
ID CustomerID Name LoanNo Amount
2 Mike1 Mike 1 4.00
3 Dinah1 Dinah 1 6.00
5 James1 James 2 3.00
6 Chuck1 Chuck 2 5.00
Thanks,
Chuck> What I want to do is return all the Customers but only their highest
> LoanNo
> (like MAX(LoanNo)).
One method is with a derived table. Untested example:
SELECT
Customers.ID,
Customers.Name,
Customers.LoanNo,
Customers.Amount
FROM Customers
JOIN
(SELECT
CustomerID,
MAX(LoanNo) AS LoanNo
FROM dbo.Customers
GROUP BY CustomerID) AS MaxLoanNos ON
MaxLoanNos.CustomerID = Customers.CustomerID AND
MaxLoanNos.LoanNo = Customers.LoanNo
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Charles A. Lackman" <Charles@.CreateItSoftware.net> wrote in message
news:eBja%23rl7GHA.3760@.TK2MSFTNGP02.phx.gbl...
>I have the following table:
> TableName: Customers
> ID - Integer
> CustomerID - VarChar
> Name - VarChar
> LoanNo= Int
> Amount = Money
> ID CustomerID Name LoanNo Amount
> 1 Chuck1 Chuck 1 2.00
> 2 Mike1 Mike 1 4.00
> 3 Dinah1 Dinah 1 6.00
> 4 James1 James 1 1.00
> 5 James1 James 2 3.00
> 6 Chuck1 Chuck 2 5.00
> What I want to do is return all the Customers but only their highest
> LoanNo
> (like MAX(LoanNo)).
> Example:
> ID CustomerID Name LoanNo Amount
> 2 Mike1 Mike 1 4.00
> 3 Dinah1 Dinah 1 6.00
> 5 James1 James 2 3.00
> 6 Chuck1 Chuck 2 5.00
> Thanks,
> Chuck
>

Help with NOT EXISTS query

Newbie here...looking for help with a NOT EXISTS query or suggestions for a
better method.
I have 2 linked tables: CUSTOMERS and ADDRESSES
common fields are CUS_NO and ADR_CD
I need to find records where an address code (ADR_CD) entered into CUSTOMERS
does not have that same ADR_CD existing in the ADRESSES table.

Example:
CUS_NO = 12345
ADR_CD = Ohio01

If the combination of cus_no 12345 and Ohio01 does not exist in the
ADDRESSES table, I need to find them.

Thanks in advance.RDRaider wrote:
> Newbie here...looking for help with a NOT EXISTS query or suggestions for a
> better method.
> I have 2 linked tables: CUSTOMERS and ADDRESSES
> common fields are CUS_NO and ADR_CD
> I need to find records where an address code (ADR_CD) entered into CUSTOMERS
> does not have that same ADR_CD existing in the ADRESSES table.
> Example:
> CUS_NO = 12345
> ADR_CD = Ohio01
> If the combination of cus_no 12345 and Ohio01 does not exist in the
> ADDRESSES table, I need to find them.
> Thanks in advance.

SELECT <select list>
FROM Customers c
WHERE NOT EXISTS (SELECT *
FROM Addresses a
WHERE c.Cus_No = a.Cust_No
AND c.ADR_CD = a.ADR_CD)

Zach|||On Mon, 13 Dec 2004 20:17:45 GMT, RDRaider wrote:

> Newbie here...looking for help with a NOT EXISTS query or suggestions for a
> better method.
> I have 2 linked tables: CUSTOMERS and ADDRESSES
> common fields are CUS_NO and ADR_CD
> I need to find records where an address code (ADR_CD) entered into CUSTOMERS
> does not have that same ADR_CD existing in the ADRESSES table.
> Example:
> CUS_NO = 12345
> ADR_CD = Ohio01
> If the combination of cus_no 12345 and Ohio01 does not exist in the
> ADDRESSES table, I need to find them.
> Thanks in advance.

SELECT Customers.cus_no, Customers.adr_cd
FROM Customers
WHERE NOT EXISTS
(SELECT *
FROM ADDRESSES
WHERE Customers.cus_no = ADDRESSES.cus_no
AND Customers.adr_cd = Addresses.adr_cd )

Alternative method:

SELECT Customers.cus_no, Customers.adr_cd
FROM Customers
LEFT JOIN Addresses
ON Customers.cus_no = Addresses.cus_no
AND Customers.adr_cd = Addresses.adr_cd
WHERE Addresses.cus_no IS NULL|||Thanks for the quick reply. It works! I don't know why I couldn't get the
same results, need to hit the books I guess.

"nib" <individual_news@.nibsworld.com> wrote in message
news:326chiF3i7ns1U1@.individual.net...
> RDRaider wrote:
>> Newbie here...looking for help with a NOT EXISTS query or suggestions for
>> a better method.
>> I have 2 linked tables: CUSTOMERS and ADDRESSES
>> common fields are CUS_NO and ADR_CD
>> I need to find records where an address code (ADR_CD) entered into
>> CUSTOMERS does not have that same ADR_CD existing in the ADRESSES table.
>>
>> Example:
>> CUS_NO = 12345
>> ADR_CD = Ohio01
>>
>> If the combination of cus_no 12345 and Ohio01 does not exist in the
>> ADDRESSES table, I need to find them.
>>
>> Thanks in advance.
> SELECT <select list>
> FROM Customers c
> WHERE NOT EXISTS (SELECT *
> FROM Addresses a
> WHERE c.Cus_No = a.Cust_No
> AND c.ADR_CD = a.ADR_CD)
> Zach

Help With Making A Query

Hello,
With the following tables how would I create a query that would return all
rows from the Customers Table that have an entry in the Activity Table?
TableName: Customers
ID - Integer
CustomerID - VarChar
StartDate - Date
EndDate - Date
TableName: Activity
ID - Integer
CustomerID - VarChar
Extension - Bit
- Customer Table
ID CustomerID StartDate EndDate
1 Chuck1 9/1/06 9/30/06
2 Mike1 8/25/06 9/15/06
3 Dinah 8/23/06 9/1/06
4 James 7/11/06 8/30/06
- Activity Table
ID CustomerID Extension
1 Chuck1 1
3 James 0
The Query Should Return:
ID CustomerID StartDate EndDate
1 Chuck1 9/1/06 9/30/06
4 James 7/11/06 8/30/06
Thanks,
Chuckselect *
from Customers
inner join Activity on Activity.CustomerID =Customers.CustomerID