Need Help Writing a Query

[tyleri]

Hi - I am having problems writing this query in MS-ACCESS - I am using pass-through queries to a SQL-Server database, so everything is in SQL. everytime I do what I think I'm supposed to do, it freezes the computer.... so I need to try this approach and I am not sure how to go about this...

I have 3 tables that I need to link together in a hierarchy.

Table1 is the highest priority. IF the UniqueID is not on Table 1, then we don't want to see it. If it IS on Table1, then we want to see it even if there is nothing on the other tables.

Table2 is the linking table to get to table 3. I want the records that match up in Table1 and Table2 ONLY to pull from Table3.

HOw can I write a query like this?

I'll give an example of the fields I am using in each table so that maybe it will help someone with an example or the solution!

Table1.login_id ---> Table2.ui_id ---> Table3.login_id

I really really appreciate any help anyone has to offer!

 

[braindead2]

if I understand what you are trying to do then you need to use a left join
select table1.index,table3.data,table3.moredata
FROM (table1 LEFT JOIN table2 ON table1.index= table2.index) LEFT JOIN [table3] ON table2.index= [table3].index;

replace index with the fields you are joining on.

 

[tyleri]

Hey - I wrote this, and it worked so far, but it isn't selecting "distinct".

When the table comes out, the vw_olp_userdetail.login_id shows up with like 3 or 4 duplicates... so the count is too high - what can I change?

here is the SQL i entered...

SELECT DISTINCT vw_olp_userdetail.login_id, vw_olp_userdetail.first_name, vw_olp_userdetail.last_name, Business_Services_Provider.uid_id, Business_Services_Provider.login_id, DPS_USER.login_id, DPS_USER.user_last_nm
FROM (vw_olp_userdetail LEFT JOIN Business_Services_Provider ON vw_olp_userdetail.login_id = Business_Services_Provider.uid_id) LEFT JOIN [DPS_USER] ON business_services_provider.login_id= [DPS_USER].login_id;

 

[braindead2]

perhaps select SELECT DISTINCTROW

 

[tyleri]

how do I implement "distinctrow"? I just threw the "row" into the syntax and i got an odbc call error...

 

[tyleri]

I think the problem is is that there are more than 1 match from table1 to table 3, that's why table2 is there, to direct to the correct match in table 3. Right now it is showing ALL matches. Is there any WHERE statements I can put together to solve this?

 

[twilleez]

i need help i have a query with a field call date what i want to do is have another field called reopen in my query but when i do this it only show the reopen date instead of dates and reopen dates together how can i do this