complex query help..

[softwareguy74]

Hi,

I'm trying to contruct what I consider a rather complex query statement.

Here's what I'm trying to accomplish:

I have 2 tables - tblDetail and tblCustomers

tblDetail has the following fields:

SiteID
LocationID

tblCustomer also includes those same 2 fields.

I want to append some data from tblDetail into tblCustomers if and only if the same data DOES NOT exist in tblCustomers. But the tricky part is, I need to test both fields (SiteID and LocationID) in tblCustomers.

For example,

tblDetail contains the following data:

SiteID | LocationID
-------------------
s20 | 1
s20 | 1
s20 | 2

tblCustomers contains the following data:

SiteID | LocationID
-------------------
s20 | 1

When I run the append query, it should only insert the last record from tblDetail as shown above into tblCustomers because that combination of data does not exist in tblCustomers.. Does that make sense?

Any help would be greatly appreciated..

Dan

[razzaj]

one way is like that :


SELECT *
FROM tblDetail
WHERE tblDetial.locationID NOT IN
(SELECT locationID
FROM tblCustomers)
AND tblDetail.SiteID NOT IN
(SELECT siteID from tblCustomers)


now to insert that into the table you can add in the Query (beginning ) the Command INSERT INTO tblCustomers ... then all the rest of the query...

or if you are using it i ASP or VB you can as well loop through the REcordset and add then one by one ... but that defies the whole purpose doesnt it ?

[Zaf Khan]

I want to append some data from tblDetail into tblCustomers if and only if the same data DOES NOT exist in tblCustomers. But the tricky part is, I need to test both fields (SiteID and LocationID) in tblCustomers.

For example,
tblDetail contains the following data:
SiteID | LocationID
-------------------
s20 | 1
s20 | 1
s20 | 2

tblCustomers contains the following data:
SiteID | LocationID
-------------------
s20 | 1





So,
using the above data to create the struct....

'this code in the declarations section of a code module

Type CommonDataInfo
  SiteId as Long
  LocId as Long
End Type
Public tblDetail() as CommonDataInfo
Public tblCustomer() as CommonDataInfo

And...

'this code in the form module (if your using one)

'set up main loop
For DetailCounta = 1 to Ubound(tblDetail)
  'set up inner loop
  For SearchCounta = 1 to Ubound(tblCustomers)
    'if first field doesn't match
    if tblDetail(DetailCounta).SiteId <> tblCustomers(SearchCounta).SiteId then
       'if second field doesn't match
      if tblDetail(DetailCounta).LocId <> tblCustomers(SearchCounta).LocId then
        'its a unique entry - so lets add it
        Redim Preserve tblCustomers(Ubound(tblCustomers+1))
        tblCustomers(Ubound(tblCustomers)).SiteId = tblDetail(DetailCounta).SiteId
        tblCustomers(Ubound(tblCustomers)).LocId = tblDetail(DetailCounta).LocId
      End If
    End If
  Next
Next

I think this may help point you in the right direction


DocZaf
{;->

[softwareguy74]

razzaj - I think we're headed in the right direction but it doesn't do exactly what I want..

Using your code, if the following record exists in tblCustomers:

SiteID | LocationID
-------------------
s20 |1

No records with the value of s20 for SiteID will appear in the query results.. But as I explained (or at least tried to explain), as long as SiteID and LocationID don't match, I want it to show up in the query results.. So if the following records existed in tblCustomers:

SiteID | LocationID
-------------------
s20 |1

and the following existed in tblDetail:

SiteID | LocationID
-------------------
s20 |1
s20 |2
s20 |3

The query should only show:

s20 |2
s20 |3

Do you see what I'm saying?

Zaf Khan, thanks for the help but I want to keep this as an SQL statement rather than in VB code.

Any one have any ideas?

Dan

[Guinness]

You could always run multiple querys:

(pseudocode)




for each entry in tblDetails

SELECT SiteID, LocationID
FROM tblCustomers
WHERE tblCustomers.SiteID = entry.SiteID
AND tblCustomers.LocationID = entry.LocationID

(at this point, if nothing is returned from the query, insert entry's data into tblCustomers)

next entry




Sometimes simpler is better. If there are too many entrys
in tblDetails, this might not be the most efficient solution.

Guinness

possibly you can solve you problem with a single SQL Statement:

INSERT INTO tblOrders (SiteID, LocationID)
SELECT DISTINCT c.SiteID, c.LocationID
FROM tblCustomers AS c 
    LEFT JOIN tblOrders AS o 
        ON c.SiteID = o.SiteID AND c.LocationID = o.LocationID
WHERE o.SiteID Is Null

good luck

sascha

[Edited by Sascha on 12-05-2000 at 06:32 PM]