Simple JOIN question

[HarryW]

Hey all


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

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

[chrisjk]

you've gone about it the wrong way a bit

Is Colour and Land_Type the names of the tables?

[HarryW]

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').

[chrisjk]

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

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:

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

[Ali G]

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

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

[HarryW]

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

[Ali G]

Respeck to me main men HarryW and Ianpbaker

Boyakasha