SELECT from multiple tables
-
Hopefully this question will be easier to answer than my last one. I have a select statement like so:
SELECT DRid, DRtagNumber, DSnameLast, DSnameFirst, DRclaimNumber
FROM SCHOOLNET.dbo.dmvrequest
LEFT OUTER JOIN SCHOOLNET.dbo.dmvsubject
ON DRdmvSubjectID = DSid
This works by itself, but I need to include another column from another table, but it screws up the query. I need to do this(red=new item):
SELECT DRid, DRtagNumber, DSnameLast, DSnameFirst, DRclaimNumber, CMclientName
FROM SCHOOLNET.dbo.dmvrequest, SCHOOLNET.dbo.clientmaster
LEFT OUTER JOIN SCHOOLNET.dbo.dmvsubject
ON DRdmvSubjectID = DSid
But whenever I do that, I get this error:
Invalid column name 'DRdmvSubjectID'
After getting this error, I tried prefixing the columns with their associated parent, but that didn't fix anything.
Any ideas?
Thanks,
M
-
Add the new table like
SELECT DRid, DRtagNumber, DSnameLast, DSnameFirst, DRclaimNumber, CMclientName
FROM
SCHOOLNET.dbo.dmvrequest
LEFT OUTER JOIN
SCHOOLNET.dbo.clientmaster
ON clientmaster.KeyField = dmvrequest.KeyField
LEFT OUTER JOIN
SCHOOLNET.dbo.dmvsubject
ON DRdmvSubjectID = DSid
Regards,
gova -
Govinn,
Thank you for your reply. However, this won't work, because there is no relation between the clientmaster and dmvrequest table.
Now you may ask, then why am I doing this. I'm doing this because I need the CMclientName column from the clientmaster table because that column is a field in a custom search form I am creating.
So later down in the query, I'll be doing a WHERE CMclientName='JoeBob Computer Shop'
Thanks!
M
-
Yes I was about to tell if there is no relationship then for each row in clientmaster all the rest of the rows will be repeated.
Why do you want to join the table for a value you already know. Just eliminate the join and use it as
SELECT DRid, DRtagNumber, DSnameLast, DSnameFirst, DRclaimNumber, 'JoeBob Computer Shop' CMclientName
FROM
SCHOOLNET.dbo.dmvrequest
LEFT OUTER JOIN
SCHOOLNET.dbo.dmvsubject
ON DRdmvSubjectID = DSid
Regards,
gova -
Govinn,
I just made up JoeBob Computer Shop. It was just an example.
Actually CMclientName can be any number of different names. Thanks,
M
-
So can you write the SELECT query for the unrelated table separately? Eg something like this:
select CMclientName
from SCHOOLNET.dbo.clientmaster
WHERE CMclientName = 'whatever'
If you can, and (essential) it returns only one row, you can add this to your original query as a subquery.
Regards
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply