Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help with generating a table of reactions to test 2

Status
Not open for further replies.
MattGreer

MattGreer

Technical User
Joined
Feb 19, 2004
Messages
471
Location
US
I have a list of elements. For this, let's say they are Fire, Water, Earth, and Air. Each of these elements can be combined in a way such that the *may* react and will produce something new. This is inclusive, or it includes repeats. So Air + Air = Wind, for example, and Air + Earth = Dust, and so forth.

So I have a table called 'Elements', and I have two columns in the table, an ID which is the Primary Key, and ElementName, which is unique. I suppose I could just use the ElementName as the primary key, but whatever.

I want to be able to generate a list of reactions to test. As I test them I'll update another table with the results of the reactions. If a reaction produces a new element, I'll add that to the 'Elements' table, and generate a new list of reactions to test.

I hope this makes sense so far!

So I generated three queries, two of them did a SELECT DISTINCT from the original table to produce a list of all unique elements, and the third query performed a query on the first two:

[tt]Query3
SELECT *
FROM Query1, Query2
WHERE (((Query1.ElementName)<>[Query2].[ElementName]));[/tt]

But this produces a table of 12 reactions to try. The reason is because it gives me Air + Water as a reaction, and Water + Air as a reaction. In this case, this is the same reaction and need not be repeated.

So I know I'm totally doing this wrong. There must be an easier way to do this.

I originally looked at this as a math problem and found this information on Wikipedia: The query should produce a set of 10 reactions to try, 10 combinations, from the original 4 elements.

I know a bit about queries but it's been a long time since I had a project like this, so don't assume I know too much! :)

Thank you for your help!!

Thanks!!


Matt
 
Sort by date Sort by votes
What about this ?
SELECT *
FROM Query1, Query2
WHERE Query1.ElementName<Query2.ElementName

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Thanks for the suggestion.

When I do that, I get a total of 6 reactions to test. The ones I'm missing are Air + Air, Earth + Earth, etc.

If I add <=, I get the 10 reactions. Fantastic!

Thank you!

Thanks!!


Matt
 
Upvote 0 Downvote
OK, so here's my next problem. I want to generate/update/append to a results table so that I have a report of what I've tried, and what I haven't tried.

I created a new table called 'Results'. I want to update the Results table with all the new combinations.

The update query (Query3) looks like this:

Code: 
INSERT INTO Results ( Reagent1, Reagent2, UniqueCombo )
SELECT Query1.ElementName, Query2.ElementName, [Query1].[ElementName]+[Query2].[ELementName] AS Expr1
FROM Query1, Query2
WHERE (((Query1.ElementName)<=[Query2].[ElementName]))
ORDER BY Query1.ElementName;

The "UniqueCombo" ensures that I don't append reactions that are already in the database, to prevent duplicates.

Problem is, as soon as the query sees duplicates, it stops and won't add the records.

So I'm guessing I need a fourth query that looks at Query3 and the Results table, but I'm not sure about the kind of "join" to use. I need only the records from Query3 that don't match the Results table.

Suggestions?

Thanks!!


Matt
 
Upvote 0 Downvote
A common way is to use a NOT IN (SELECT ...) operaror

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Thank you! I started a somewhat duplicate thread in the Access forum and someone posted something similar.

Thanks!!


Matt
 
Upvote 0 Downvote
Just a note, when using [tt]NOT IN (SELECT ...)[/tt] , beware of NULL's in the sub-select.
Example:
[tt]
SQL>create table t1 (c1 integer primary key, c2 varchar(10));
SQL>create table t2 (c1 integer);
SQL>insert into t1 values (1,'Alfa');
SQL>insert into t1 values (2,'Bravo');
SQL>insert into t1 values (3,'Charlie');
SQL>insert into t2 values (2);
SQL>select * from t1 where c1 not in (select c1 from t2);
c1 c2
=========== ==========
1 Alfa
3 Charlie

2 rows found

SQL>insert into t2 values (null);
SQL>select * from t1 where c1 not in (select c1 from t2);

0 rows found

SQL>select * from t1 where c1 not in (select c1 from t2 where c1 is not null);
c1 c2
=========== ==========
1 Alfa
3 Charlie

2 rows found[/tt]
 
Upvote 0 Downvote
Interesting. I will review this.

I used to be so much more comfortable with SQL, but I haven't touched it for 7 years...

Thanks!!


Matt
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

BobThornton
  • Locked
  • Question Question
windows command in a sql script
Replies
1
Views
603
carp
carp
BobThornton
  • Locked
  • Question Question
Count records until a record value changes
Replies
2
Views
819
carp
carp
sal21
  • Locked
  • Question Question
SORT by part of filed
Replies
5
Views
782
2009luca
2009luca
mmiah1982
  • Locked
  • Question Question
Extracting mutiple data items from DB with JSON string format column
Replies
2
Views
563
mmiah1982
mmiah1982
Bill Doe
  • Locked
  • Question Question
PDO UPDATE too long to execute
Replies
1
Views
552
mikrom
mikrom

Part and Inventory Search

Sponsor

Back
Top