Need Help Developing Query.... for the GURU's :)

[tyleri]

ok - i have been working on this for quite some time now, and I have failed.

I have 3 tables I must pass-through to get the information.

TABLE1 contains 1 field. Table1 is the important table because i need to ONLY contain the info that table1 has, no more, no duplicates.

TABLE2 has two fields. It's a linking table to line up the data from table1 to table3.

TABLE3 has many fields, and links with table2.

What happens, after I do the joins, is that TABLE3 will give me more information than what is in TABLE1. This is not logically possible and completely screws up our database (primarily for counting and viewing).

Here is a graphic viewing of what I need:

table1.login_id ---> table2.uid_id

table2.login_id ---> table3.login_id

OR...

Table1--->Table2--->Table3

Table3 contains the information for the unique IDs in Table1.

Can someone possibly write a query for me that does this? I am having the most difficult time doing this simple task.

 

[jballum]

I might be misinterpreting your question but this sounds like what you need.

select *
from
table1 T1
inner join
table2 T2 on T1.login_id = T2.uid_id
inner join
table3 T3 on T2.login_id = T3.login_id

Are you maybe doing outer joins instead of inner joins?

JB

 

[tyleri]

I've actually been trying Left Joins...

 

[jballum]

Left outer joins will return the matching records plus any records that don't match (that come from table(s) on left side of the join).

Try with inner joins only and see what you get.

If you are still getting unwanted data then carefully check the data in each table.

JB

 

[tyleri]

OK this is going to be really hard to explain....

The code you gave me is a lot cleaner, and it kinda works.... there are duplicates.... Let me explain the catch:

Table1 has login_id
table2 has uid_id and login_id
table3 has login_ID

here are some example values:

table1.login_id:
Tyler1
John2
Bob3

Table2.uid_id:
Tyler1
John2
Bob3

Table2.login_id:
tyler1x
john2x
bob3x

table3.login_id
Tyler1
Tyler1
Tyler1
Tyler1
tyler1x
John2
John2
John2
john2x
Bob3
Bob3
bob3x

So you see - what is happening is that for some reason it is not using the middle table (table2) as a link.

I am getting all the "tyler1's" and "Bob2"'s. It's extremely frustrating, and SELECT DISTINCT doesn't work.

I think I need some sort of hierarchy where table2 is passed through AFTER it knows to ONLY include Table1.

Any Ideas?

Thanks

 

[jballum]

tyler1,

I created the 3 tables and entered the same data and ran the query and everything is coming back fine. In the above example it returned 3 rows as it should.

The join should look like this:

from Table1 T1
inner join Table2 T2 on T1.login_id = T2.uid_id
inner join Table3 T3 on T2.login_id = T3.login_id

Not like this:

from Table1 T1
inner join Table2 T2 on T1.login_id = T2.uid_id
inner join Table3 T3 on T1.login_id = T3.login_id

In the join to Table3 are you using T1.login_id to join to T3.login_id?

JB

 

[tyleri]

You're going to want to beat me up (actually my database manager) because he got the middle table mixed up. I figured out the problem with your help thouhg - thanks a ton! table1.login_id is SUPPOSED to go to table2.login_id and so on, not table2.uid_id for the first join. oops!!!!

I have another question though -

suppose I want to throw more joins on to TABLE3 after it has done the other joins, to another ID called ID?

how do I do this in our code?

(here's the version of my code that works...)

SELECT DISTINCT Business_Services_Provider.uid_id, Business_Services_Provider.login_id, vw_olp_userdetail.login_id, vw_olp_userdetail.first_name, vw_olp_userdetail.last_name, DPS_USER.login_id, DPS_USER.user_last_nm

FROM
vw_olp_userdetail
INNER JOIN
Business_Services_Provider
ON vw_olp_userdetail.login_id = Business_Services_Provider.login_id
INNER JOIN
DPS_USER on Business_Services_Provider.uid_id = DPS_USER.login_id;

I want to connect DPS_USER.ID to Pers_Info.ID. How do I add this to our code?

 

[jballum]

.....
INNER JOIN
DPS_USER on Business_Services_Provider.uid_id = DPS_USER.login_id
INNER JOIN
Pers_Info on DPS_USER.ID = Pers_Info.ID;