Query Help Please!

  • Thread starter Thread starter fgwiii
  • Start date Start date
F

fgwiii

SiteNumber PI ContactID Contact
ContactTypeText FaxNumber
===========================================================
=====================
065 Michael Rt 91 Kris A Site
Coordinator 6011292809
065 Michael Rt 92 Rachel S
Study Coordinator 6011292809
066 Eric W 94 Heather W Site
Coordinator 7576687811
068 Frank S 96 Maureen R Nurse
Coordinator 1154174181
071 Emad S 97 Jennifer V
Site Coordinator 1294212111
088 Sandra M 98 Colleen F
Other 4014446104
088 David Bess 101 Kathy F Sub
Investigator 4097712522


I am attempting create a query that will return a row for
each SiteNumber containing the lowest
ContactID for that site. The top table is the source and
the bottom is the result.


SiteNumber PI ContactID Contact
ContactTypeText FaxNumber
===========================================================
=====================
065 Michael Rt 91 Kris A Site
Coordinator 6011292809
066 Eric W 94 Heather W Site
Coordinator 7576687811
071 Emad S 97 Jennifer V
Site Coordinator 1294212111
088 Sandra M 98 Colleen F
Other 4014446104


Thanks for your help!

Fred
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

SELECT S.*
FROM SourceTable As S
WHERE S.ContactID = (SELECT Min(ContactID) FROM SourceTable
WHERE SiteNumber = S.SiteNumber)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQJkg8YechKqOuFEgEQJ6YACeJGWJWG3BX59tvXz88dQyz53u218AnAir
V8LVWiY6kSVUI6BDfdFLo1FE
=0sSN
-----END PGP SIGNATURE-----
 
Thanks for your quick response!

I ran your code and while it worked, it only returned a
single row in the table. What I need is one row per site
number as shown in the bottom table.

Ultimately, I should end up with a hundred or so rows -
one for each site.

Fred
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It should work. Can you copy & paste your query's SQL in a post so we
can see what you've got?

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQJk5TYechKqOuFEgEQIHmwCfUVMYRjqKV5qCDMgXWZTbo19edBMAmwRL
UtvnlbM1GRHF/1Ekbg63WSt2
=qkGb
-----END PGP SIGNATURE-----
 
Not being a SQL guru by any stretch I reworked your code a
bit and attempting to run the following. Unfortunately I
am getting errors!

SELECT A.*, C.*
FROM [Active Sites] AS A LEFT JOIN [dbo_v_Contacts] AS C
ON A.InvID = C.InvID
WHERE (((C.Contact.ID)=(SELECT First (ContactID) FROM [C]
WHERE SiteNumber = A.SiteNumber and SiteNumber <> 'NNPI')))

Active Sites is a query and dbo_v_Contacts is a SQL table

Thanks for your help!
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try this:

SELECT A.*, C.*
FROM [Active Sites] AS A LEFT JOIN [dbo_v_Contacts] AS C
ON A.InvID = C.InvID
WHERE C.ContactID = (SELECT MIN(ContactID)
FROM dbo_v_Contacts
WHERE SiteNumber = A.SiteNumber and SiteNumber <> 'NNPI')

SiteNumber must be a column of the query [Active Sites] and the table
dbo_v_Contacts.

Don't use the First() function it's a very unreliable function (i.e.,
worthless). Since you want the lowest ContactID use the MIN() function.

Why are you LEFT JOINing? What does the query [Active Sites] do? Why
not use INNER JOIN? If you just want the query [Active Sites] to help
filter dbo_v_Contacts for active sites, the JOIN should be an INNER
JOIN. If you want a list of all the Active Sites, no matter if there is
a contact for that site or not, then use a LEFT JOIN.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQJlVdYechKqOuFEgEQIqmACgrB8leORbqwM++UYPnt1F7cKUciIAoLku
YfnAykxIVJSdczI3oPqxM7os
=JQVU
-----END PGP SIGNATURE-----

Not being a SQL guru by any stretch I reworked your code a
bit and attempting to run the following. Unfortunately I
am getting errors!

SELECT A.*, C.*
FROM [Active Sites] AS A LEFT JOIN [dbo_v_Contacts] AS C
ON A.InvID = C.InvID
WHERE (((C.Contact.ID)=(SELECT First (ContactID) FROM [C]
WHERE SiteNumber = A.SiteNumber and SiteNumber <> 'NNPI')))

Active Sites is a query and dbo_v_Contacts is a SQL table

Thanks for your help!
-----Original Message-----
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It should work. Can you copy & paste your query's SQL in
Click to expand...

a post so we
can see what you've got?

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQJk5TYechKqOuFEgEQIHmwCfUVMYRjqKV5qCDMgXWZTbo19edB
MAmwRL

UtvnlbM1GRHF/1Ekbg63WSt2
=qkGb
-----END PGP SIGNATURE-----


fgwiii wrote:


site

21
===========================================================
===========================================================


.
Click to expand...
Click to expand...
 
While this query does work, it only lists the first site
that the criteria matches. I need this to list multiple
sites, each site/row with only one contact from the site.
site fname lname cfname clname cfax
=============================================
001 jon smith Jill Sharky 5551515
002 F Marks Tammy Hill 5551212
-----Original Message-----
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try this:

SELECT A.*, C.*
FROM [Active Sites] AS A LEFT JOIN [dbo_v_Contacts] AS C
ON A.InvID = C.InvID
WHERE C.ContactID = (SELECT MIN(ContactID)
FROM dbo_v_Contacts
WHERE SiteNumber = A.SiteNumber and
Click to expand...
SiteNumber said:
SiteNumber must be a column of the query [Active Sites] and the table
dbo_v_Contacts.

Don't use the First() function it's a very unreliable function (i.e.,
worthless). Since you want the lowest ContactID use the MIN() function.

Why are you LEFT JOINing? What does the query [Active Sites] do? Why
not use INNER JOIN? If you just want the query [Active Sites] to help
filter dbo_v_Contacts for active sites, the JOIN should be an INNER
JOIN. If you want a list of all the Active Sites, no matter if there is
a contact for that site or not, then use a LEFT JOIN.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQJlVdYechKqOuFEgEQIqmACgrB8leORbqwM++UYPnt1F7cKUci IAoLku
YfnAykxIVJSdczI3oPqxM7os
=JQVU
-----END PGP SIGNATURE-----

Not being a SQL guru by any stretch I reworked your code a
bit and attempting to run the following. Unfortunately I
am getting errors!

SELECT A.*, C.*
FROM [Active Sites] AS A LEFT JOIN [dbo_v_Contacts] AS C
ON A.InvID = C.InvID
WHERE (((C.Contact.ID)=(SELECT First (ContactID) FROM [C]
WHERE SiteNumber = A.SiteNumber and SiteNumber
Click to expand...
Active Sites is a query and dbo_v_Contacts is a SQL table

Thanks for your help!
in

a post so we
dB

MAmwRL
3u

21

===========================================================

===========================================================
Click to expand...

.
Click to expand...
 
Back
Top