Is it possible to run a Left and Right Join at the same time? 2

[Rmck87]

I was wondering if i could run a LEFT and RIGHT JOIN at the same time. Because i need to run a query that will come up with null values for a few columns when they are supposed to be null at some times, and not null at other times. Here is my code:


SELECT DISTINCT member.Member_ID, company.Company_Name, pm_project.Project_ID, te_period.Date_Start, te_period.Date_End, time_entry.hours_actual, te_charge_code.description
From te_period, time_sheet, pm_project, member, company, time_entry left JOin te_charge_code on time_entry.te_charge_code_recid = te_charge_code.te_charge_code_recid, time_entry as [te] right join te_charge_code as [tecc] on [te].te_charge_code_recid = [tecc].te_charge_code_recid
Where te_period.te_period_recid = time_sheet.te_period_recid and time_sheet.time_sheet_recid = time_entry.time_sheet_recid and company.company_recid = time_entry.company_recid and pm_project.pm_project_recid = time_entry.pm_project_recid and member.member_recid = time_entry.member_recid



I was trying to run a LEFT and RIGHT JOIN. But it did not work completely.

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[ipupkin]

hi
i'm not a guru neither profi, but what if you try to give test tables only with columns you need to test your query and only 5 or 10 rows, and give an example of "wrong" behaviour of right/left join, then an example what "correct" query result should be?

 

[Rmck87]

Ok. Good idea. Lets see here...

SELECT tblA.fld1, tblB.fld1, tblC.fld2
From tblA Left Join tblB on tblA.fld2 = tblB.fld2, tblA Right Join tblB on tblB.fld2 = tblA.fld2, tblC, tblD
Where tblC.fld1 = tblD.fld3 and tblA.fld2 = tblC.fld1


This should bring up something like this.....


tblA.fld1 tblB.fld1 tblC.fld2
---------------------------------------------------
name 01 null
name null name
name null name
name 02 null
name 03 null
name null name


There will always be a value in tblA.fld1 but sometimes in tblB.fld1 and sometimes in tblC.fld2, Whenever there is a value in tblB.fld1 there is a null value in tblC.fld2, and vice versa... But, it is coming up with something like this...


tblA.fld1 tblB.fld1 tblC.fld2
---------------------------------------------------
name 01 null
name 02 null
name 03 null
name 04 null
name 05 null
name 06 null
name 07 null


It comes up with values in both tblA.fld1, and tblB.fld1 but not in tblC.fld2, it comes up all null. How can i fix this? Any ideas.....

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[scriverb]

Let's try a UNION query with seperate LEFT and RIGHT joins:

SELECT tblA.fld1, tblB.fld1, tblC.fld2
From tblA LEFT Join tblB on tblA.fld2 = tblB.fld2, tblC, tblD
Where tblC.fld1 = tblD.fld3 and tblA.fld2 = tblC.fld1
UNION
SELECT tblA.fld1, tblB.fld1, tblC.fld2
From tblA RIGHT Join tblB on tblB.fld2 = tblA.fld2, tblC, tblD
Where tblC.fld1 = tblD.fld3 and tblB.fld2 = tblC.fld1

Post back with the results and questions.




Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[scriverb]

Or, this one might be a little better for you. I only included tblA, tblB, and tblC. Because I really don't know the relationship of these tables I only used these three. This seems to work on a set of tables that I created here. Let me know if this is getting closer.

SELECT tblA.fld1, tblB.fld1, tblC.fld2
From (tblA LEFT Join tblB on tblA.fld2 = tblB.fld2) Left Join tblC on tblA.fld2 = tblC.fld1
UNION
SELECT tblA.fld1, tblB.fld1, tblC.fld2
From (tblA RIGHT Join tblB on tblB.fld2 = tblA.fld2) Left Join tblC on tblB.fld2 = tblC.fld1

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[Rmck87]

This is what im trying right now. I dont know if i could do what you were most recently sayinig scriverb because i have a lot more tables than just 3 that i need to join. But i am just using 3 tables as an example, for easier reading.

SELECT DISTINCT tblA.fld1, tblB.fld1, tblC.fld2
From tblA Left Join tblB ON tblA.fld2 = tblB.fld2, tblC, tblD
Where tblC.fld1 = tblD.fld3 and tblA.fld2 = tblC.fld1
Union
Select Distinct tblA.fld1, tblB.fld1, tblC.fld2
From tblA right join tblB on tblB.fld2 = tblA.fld2, tblC, tblD
Where tblA.fld2 = tblC.fld1



This should bring up records like this....


tblA.fld1 tblB.fld1 tblC.fld2
---------------------------------------------------
name 01 null
name null name
name null name
name 02 null
name 03 null
name null name


But some odd reason it isn't. it brings up nulls for tblB.fld1, when tblC.fld2 has a value in it. But not the opposite.

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[Rmck87]

Thanks for the help guys, you got me started and i managed to finish it off. I figured it out. I was putting the wrong tables on the wrong sides of the joins. Thanks again.

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.