Simple JOIN question

Hey all :)


I am trying to use this query with an Access database from VB, and having lots of problems:

Code:

---

SELECT DISTINCT Landscape_Name FROM (
    (SELECT Landscape_Name FROM Colour WHERE Colour='blue' ORDER BY Priority ASC)
  INNER JOIN
    (SELECT Landscape_Name FROM Land_type WHERE Land_Type='desert' ORDER BY Priority ASC)
ON Landscape_Name);

---

I'm not having much luck unfortunately :( I've only really used SQL for simple stuff before, and never used joins. I'm not sure if I can use those subqueries in one statement.

If you can suggest a way to rewrite this so it will work I'd be very appreciative :)

you've gone about it the wrong way a bit

Is Colour and Land_Type the names of the tables?

Yes, they're the table names. The names should probably be made more distinct. Land_type (with a 't') is the table name and that has a field called Land_Type (with a 'T').

I've just had another look at it...

It looks like you are trying to join the recordsets to product 1 recordset, like say you have a table called Customers and another table called Employees, and you're trying to combine the two to produce a "Customers and Employees" query

Quote:

---

Customers
-------------
Name Address
Brian 12 South Street
Fred 13 West Street

Employees
-------------
Name Address
Bob 999 Letsbe Avenue
Jim 123 Elm Street

---

and you want to produce:

Quote:

---

Customers and Employees
----------------------------------
Name Address
Brian 12 South Street
Fred 13 West Street
Bob 999 Letsbe Avenue
Jim 123 Elm Street

---

If that is the case, you need to use a union join rather than the usual type of join. Sorry that was long winded, i'm just trying to work out what you're trying to do

ere is da example of da Union query dat me main man chrisjk wos nattain abou'

VB Code:

SELECT Landscape_Name FROM Colour WHERE Colour='blue' ORDER BY Priority ASC;
UNION ALL
SELECT Landscape_Name FROM Land_type WHERE Land_Type='desert';

Joins da recordsets togeter, aye

Thanks guys, but it was definitely inner join I was needing. This has been resolved now anyway, thanks to some database restructuring and SQL malarkey, courtesy of the Mr Ian P Baker we all know and love. Well, we know him anyway.

Cheers :)

Respeck to me main men HarryW and Ianpbaker

Boyakasha