Subject of flowers

S

Sarma

Registered User.
Local time
Today, 20:56
Joined
Nov 16, 2009
Messages
75
In Table 1, I have a list of 12 flower names.

In Table 2, I have a list of 2 flower names.

I want a list that contains 10 flower names (12-2).

Possible in MS Access ?
 
Assuming the 2 flowers in Table 2 are in Table 1, then you would need a LEFT JOIN from Table1 to Table2 and then only show the flowers from table 1 that return a null value from Table2.

Code: 
SELECT Table1.FlowerName
FROM Table1 LEFT JOIN Table2 ON Table1.FlowerName = Table2.FlowerName
WHERE (((Table2.FlowerName) Is Null));
 
Thanks, Plog. The result I got is a blank table, whereas I should have got 10 names of flowers.
 
Can you post the SQL of the query you ran?
 
SELECT [Table1].Flower
FROM [Table1] LEFT JOIN [Table2] ON [Table1].[Flower] = [Table2].[Flower]
WHERE ((([Table2].Flower Is Null));
 
In Maths, we have Union and Intersection. This is the case of Intersection.
 
see this for an example of left join
http://www.w3schools.com/sql/sql_join_left.asp

Is this an sql problem/example, or is it part of a real world problem?
You question is so specific that it seems like an sql syntax question.

Do your tables have primary keys? What field(s) link table 1 and 2?
 
This is a real world problem for me. I suspect my Syntax.

I have not used any Primary Key.
 
This is the case of Intersection.
Click to expand...

Actually what you asked for is the set difference. An intersection would return the 2 flowers in both tables.

Could you have transposed the tables? That is, is it possible Table2 is the table with 12 records and Table1 is the table with 2 records? If so, swap all references to the 2 tables in the SQL. That's the only possiblity that I can think of that would let your query run without erros and return no records.
 
I can swap the tables. In that case left table will have two names and the right table will have 12. I need 10 names. Then what will be the Syntax ?
 
In the SQL you posted replace 'Table1' with 'Table2' and vice versa.
 
Can you post the actual database? or at least the tables in question?
 
I created a mockup of your situation.
Attached jpg shows each table, the query sql and the result.
 

Attachments

  • FlowersTable1and2.jpg
    FlowersTable1and2.jpg
    99.6 KB · Views: 133
Nice job. Happy to see you getting the answer. Can you please the SQL with Syntax ? I will copy and paste to see if it works for me. Rgds.
 
Flowers... tis the right season for it... Valentines day! :)

You said you want an Intersection, an intersection would be an INNER JOIN.

Edit: I haven't read the entire thread. It was just your post #6 that caught my eye.
 
Ha.

I said I needed intersection. But what I need is not Intersection.

I think I must look at Unmatched Items wizard.
 
Sarma said:
Nice job. Happy to see you getting the answer. Can you please the SQL with Syntax ? I will copy and paste to see if it works for me. Rgds.
Click to expand...

Hmmm? The sql is listed in the jpg. ( It's in the textpad part of the jpg)
Code: 
SELECT tbl_One.flower
, tbl_two.flower
FROM tbl_One 
LEFT JOIN tbl_two ON 
tbl_One.flower = tbl_two.flower
WHERE 
(((tbl_two.flower) Is Null));

Yes, you said you needed the 10 flowers that were not those represented in the other table. So LEFT JOIN it is.
 
You must log in or register to reply here.

Similar threads

C
Query Help on "Lookup" Type Function
Replies
12
Views
340
arnelgp
arnelgp
H
Query combining records
Replies
13
Views
624
theDBguy
theDBguy
K
Solved Insert Into - Select
Replies
18
Views
778
arnelgp
arnelgp
B
Pad a column alias' with a leading space.
Replies
9
Views
425
Pat Hartman
P
S
Update Data Based on plat number and Date
Replies
7
Views
282
sandy70
S

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom