sql question

[m.a]

Hello,

I want to do a left join on a table and a select statement such as follow:





SELECT tbl1.*, q1.*
FROM tbl1 LEFT JOIN (SELECT tbl2.* FROM tbl2 WHERE (((tbl2.key2)=9)) as
q1 ON tbl1.key1 = q1.key1
WHERE (((tbl1.key1)=20));



Can I do this in Access database? If yes how? I know that I can create a
query for the inner select and use it, but since I am generating the select
string automatically in C#, I don't want to relay on another query on the
system.



Any help is very appreciated.



Regards

 

[Uri Dimant]

Hi
This is SQL Server newsgroup. Perhaps you would be better off to ask the
question in Access forum

 

[John Spencer]

As long as your table names and field names consist of only Letters, Numbers,
and the underscore character this should work.

SELECT tbl1.*, q1.*
FROM tbl1 LEFT JOIN
(SELECT tbl2.* FROM tbl2 WHERE tbl2.key2=9) as q1
ON tbl1.key1 = q1.key1
WHERE tbl1.key1=20

Note that I removed all the extra parentheses and I also removed the
semi-colon in the subquery.

If this does not work try the alternate syntax shown below.
SELECT tbl1.*, q1.*
FROM tbl1 LEFT JOIN
[SELECT tbl2.* FROM tbl2 WHERE tbl2.key2=9]. as q1
ON tbl1.key1 = q1.key1
WHERE tbl1.key1=20

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Philipp Post]

 Can I do this in Access database? If yes how? <

MS Access has the option to create queries in SQL mode i.e. without
using the graphical "help". Click on new query, when it requests to
select tables click on cancel, then select view -> SQL mode.

Derived tables are possible in Access, but you are having a semicolon
in the middle of your query - that does not work as the semicolon is
the statement terminator.

SELECT tbl1.*, q1.*
FROM tbl1
LEFT OUTER JOIN (SELECT tbl2.*
FROM tbl2
WHERE tbl2.key2 = 9) as q1
ON tbl1.key1 = q1.key1
WHERE tbl1.key1=20;

brgds

Philipp Post

 

[Rick]

Hi
This is SQL Server newsgroup. Perhaps you would be better off to ask
the question in Access forum

SQL is a language, not a Microsoft product. Sadly, it's become a
hijacked term for the product. (It was a poor chice for a name.)

 

[Tibor Karaszi]

SQL is a language, not a Microsoft product. Sadly, it's become a

hijacked term for the product. (It was a poor chice for a name.)

Perhaps I am missing something, but IU fail to see your point. As far as I can see (my nntp reader
address field), this post was addressed to two groups:

microsoft.public.access
microsoft.public.sqlserver.programming


I don't see only "SQL" anywhere above. I do see "sqlserver", though. Btw, the product name was
Sybase in the very early days and I guess one could also say the same for IBM's "SQL/DS" or "DB/2"
(having "database" in the name). Etc.

 

[Rick]

Perhaps I am missing something, but IU fail to see your point. As far
as I can see (my nntp reader address field), this post was addressed
to two groups:

microsoft.public.access
microsoft.public.sqlserver.programming


I don't see only "SQL" anywhere above. I do see "sqlserver", though.
Btw, the product name was Sybase in the very early days and I guess
one could also say the same for IBM's "SQL/DS" or "DB/2" (having
"database" in the name). Etc.

I misunderstood the question, since I didn't see that it was cross-
posted. Doh! (it's just a general pet-peeve of mine when people in my
workplace ask me a SQL question then procedd to ask a Microsoft product
question. It's like developing a new product and calling it English.)

 

[Tibor Karaszi]

I misunderstood the question, since I didn't see that it was cross-

posted. Doh! (it's just a general pet-peeve of mine when people in my
workplace ask me a SQL question then procedd to ask a Microsoft product
question. It's like developing a new product and calling it English.)

I know the feeling. I have perhaps stronger feeling when people say "database" when they really mean
the database *management system*.