A "not equal" query... (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

milo -> A "not equal" query... (5/5/2006 17:58:00)

Here's a new challenge/problem I'm having.

I have two tables which need to link (somehow) in two ways...

1) (Dates) TableA.acct_month needs to be between TableB.flow_month and TableB.inv_month
2) (Locations) TableA.bp_name cannot equal TableB.bp_name

Essentially, I'm looking for the receipts that don't match up for a given month.

I've tried this... (but doesn't work at all) 
SELECT [Receipt Commodity Charges].station_num, [Receipt Commodity Charges].station_name, [ProServ NOVA].acct_month

FROM [ProServ NOVA] INNER JOIN [Receipt Commodity Charges] ON ([ProServ NOVA].receipt_location <> [Receipt Commodity Charges].station_name) AND ( [Receipt Commodity Charges].inv_month > [ProServ NOVA].acct_month > [Receipt Commodity Charges].flow_month)

GROUP BY [Receipt Commodity Charges].station_num, [Receipt Commodity Charges].station_name, [ProServ NOVA].acct_month;


I was thinking about a "Union" or "Where" join, but don't know exactly how to create these properly...

(An Example of my failed "Where" join) 
SELECT [Receipt Commodity Charges].station_num, [Receipt Commodity Charges].station_name, [Receipt Commodity Charges].flow_month, [Receipt Commodity Charges].inv_month

FROM [ProServ NOVA], [Receipt Commodity Charges]

WHERE (([ProServ NOVA].receipt_location<>[Receipt Commodity Charges].[station_name]) AND ([ProServ NOVA].acct_month BETWEEN [Receipt Commodity Charges].[flow_month] AND [Receipt Commodity Charges].inv_month))

GROUP BY [Receipt Commodity Charges].station_num, [Receipt Commodity Charges].station_name, [Receipt Commodity Charges].flow_month, [Receipt Commodity Charges].inv_month;


Any thoughts?



rubyaim -> RE: A "not equal" query... (5/5/2006 19:54:49)

What's the GROUP BY doing? It sounds like you just need a to Select, Join and specify Where.

Are you getting any specific errors or just no data?



yb2 -> RE: A "not equal" query... (5/6/2006 8:07:02)

First thing - use aliases, they make everything much easier to read

SELECT R.station_num, R.station_name, P.acct_month

FROM [ProServ NOVA] as P
INNER JOIN [Receipt Commodity Charges] as R
ON (P.receipt_location <> R.station_name) AND ( R.inv_month > P.acct_month > R.flow_month)

GROUP BY R.station_num, R.station_name, P.acct_month;

Phew, so much better![:)]

quote:

given month.

This little nugget of info means you need a WHERE clause.

quote:

don't match up

This nugget means you are looking for an OUTER join

Try

SELECT R.station_num, R.station_name, P.acct_month
FROM [ProServ NOVA] as P
LEFT OUTER JOIN [Receipt Commodity Charges] as R
ON P.receipt_location = R.station_name
WHERE P.acct_month = varGivenMonth AND R.inv_month > varGivenMonth AND R.flow_month < varGivenMonth
AND R.station_name IS NULL
GROUP BY R.station_num, R.station_name, P.acct_month;

I assume you're using Access from other posts you've made. varGivenMonth is an input variable like you'd use in a query. This SQL statement will bring back all the P.receipt_location rows, and so they will have some that don't match with R.station_name, so you search for those in the WHERE clause. The other part of the WHERE is taken from your earlier join attempts.



milo -> RE: A "not equal" query... (5/11/2006 18:33:26)

OK, I got this code to not throw up errors anymore, but it doesn't return anything at all...

I am absolutely positive that there should be approximately 25 records returned for each month.

This is exactly what I'm trying to do (just in case there is any confusion or questions):
WHERE (R.inv_month > P.acct_month > R.flow_month) AND (R.station_num <>  P.receipt_id)


and I believe we are on the right track, but we're missing something...

latest code, in all it's glory:
SELECT P.receipt_id, P.acct_month, R.station_num, R.inv_month, R.flow_month

FROM [Receipt Commodity Charges] AS R LEFT JOIN [ProServ NOVA] AS P
 ON R.station_num = P.receipt_id

WHERE ([R].[inv_month]>[P].[acct_month]>[R].[flow_month])
 AND (R.station_num<>[P].[receipt_id])

GROUP BY P.receipt_id, P.acct_month, R.station_num, R.inv_month, R.flow_month;




rubyaim -> RE: A "not equal" query... (5/11/2006 19:55:17)

quote:

WHERE ([R].[inv_month]>[P].[acct_month]>[R].[flow_month])

Hi Milo, in your first post you mentioned that TableA.acct_month needs to be between TableB.flow_month and TableB.inv_month, so maybe try that in your where, eg:

WHERE [acct_month] Between [flow_month] And [inv_month]

If you are doing a fair bit of consolidation you may find these tutorials helpful:

http://www.vb123.com/toolshed/98docs/consolidate.htm

http://www.w3schools.com/sql/sql_groupby.asp

http://www.w3schools.com/sql/sql_functions.asp




yb2 -> RE: A "not equal" query... (5/12/2006 4:37:20)

I'd try removing all of the WHERE clause and then adding it bit by bit to check that the main statement and the clauses you add are bringing back the data you expect.



yb2 -> RE: A "not equal" query... (5/16/2006 13:20:09)

quote:

SELECT P.receipt_id, P.acct_month, R.station_num, R.inv_month, R.flow_month

FROM [Receipt Commodity Charges] AS R LEFT JOIN [ProServ NOVA] AS P
ON R.station_num = P.receipt_id

WHERE ([R].[inv_month]>[P].[acct_month]>[R].[flow_month])
AND (R.station_num<>[P].[receipt_id])

GROUP BY P.receipt_id, P.acct_month, R.station_num, R.inv_month, R.flow_month;


I found out today that you can use a range for a join - you learn something new every day!

It means you could try this

SELECT P.receipt_id, P.acct_month, R.station_num, R.inv_month, R.flow_month
FROM [ProServ NOVA] AS P
INNER JOIN [Receipt Commodity Charges]
ON P.acct_month BETWEEN R.inv_month AND R.flow_month
WHERE R.station_num <> P.receipt_id

If that brings back any data you could play around with the join and the where to see if you can get the right stuff back.




milo -> RE: A "not equal" query... (5/17/2006 13:02:31)

OK, so it seems that I got all excited because I thought we were close to finished...

It seems that there's a flaw in the logic.

So, there are two tables with a location id in each (P.receipt_id & R.station_num). The [P] table is always missing approximately 25 locations (every month). These locations should match up exactly, alas, they don't...

So, this query is trying to find the locations that are not in [P] that are in [R]... (for a given month, i.e. [R].[inv_month]>[P].[acct_month]>[R].[flow_month])

When I run this query... 
SELECT P.receipt_id, P.acct_month, R.station_num, R.inv_month, R.flow_month

FROM [ProServ NOVA] AS P 

INNER JOIN [Receipt Commodity Charges] AS R 

ON P.receipt_id <> R.station_num

WHERE (P.acct_month BETWEEN R.inv_month AND R.flow_month) 
AND (P.acct_month <> R.inv_month) AND (P.acct_month <> R.flow_month);

Because there are about 300 locations each month, of course these 300 don't match 299 locations in the other table. Consequently, I get 300! (factorial) results with this query.

THE NUTSHELL:
How do I create a query that shows which locations present in the [R] table that are not present in the [P] table?

Thank you guys. You've been a real help.



milo -> RE: A "not equal" query... (5/17/2006 17:51:34)

So, I'm slightly a genius... I figured this baby out.

Check this out:
SELECT DISTINCT R.station_num, R.station_name, R.inv_month, R.flow_month

FROM [Receipt Commodity (Normalize)] AS R

WHERE (((Exists (SELECT P.receipt_id, P.acct_month  FROM [ProServ (Normalize)] AS P  
WHERE (R.station_num = P.receipt_id) AND 
(P.acct_month BETWEEN R.inv_month AND R.flow_month) AND 
(P.acct_month <> R.inv_month) AND 
(P.acct_month <> R.flow_month)))=False));


Originally I had simpler code...
SELECT * FROM R
WHERE NOT EXISTS
(SELECT * FROM P
WHERE A.X = B.X AND A.Y > B.Y AND ... etc)

But Access automatically rewrote the code when I ran the query and looked at it in design view.

But that's the answer (above).



milo -> RE: A "not equal" query... (5/17/2006 17:52:54)

P.S. you guys were excellent!

Thank you for your help. I really appreciate all you've done.



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.09375