SQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
Go Back   ASP Free ForumsDatabaseSQL Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread ASP Free Forums Sponsor:
  #1  
Old November 5th, 2009, 06:06 PM
pc131 pc131 is offline
Registered User
ASP Free Newbie (0 - 499 posts)
 
Join Date: Nov 2009
Posts: 2 pc131 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 5 m 55 sec
Reputation Power: 0
Query - General - LEFT JOIN problem

I have 3 tables:

test1
id name1
1 value1

test2
id name2
1 value2

test3
id name3
3 value3_01
4 value3_02

I want to display all records of table test1 with values of table test2 (test1.id = test2.id) and values of test3 (if test.id match with test1.id) - in this case test3 has no common ids with test1 so NULLs are displayed.
How to make sql query to display:
id1 name1 name2 name3
1 value1 value2 NULL

I constructed query:
SELECT t1.id as id1, t1.name1 as name1, t2.name2 as name2, t3.name3 as name3
FROM (test1 t1) LEFT JOIN (test3 t3, test2 t2)
ON (t1.id=t2.id and t1.id=t3.id)

but it gives me:

id1 name1 name2 name3
1 value1 NULL NULL

name2 is NULL instead of desired "value2". WHY?

LEFT JOIN DEFINITION:
SQL LEFT JOIN Keyword
The LEFT JOIN keyword returns all rows from the left table, even if there are no matches in the right table.

There are no matches in test3 table when joining test1 table so name3 from table3 is NULL in result.

BUT There are matches in table test2 (test2.id=1 and test1.id=1) so why null is displayed?

Hope anyone can help me. I am in big trouble.

Thanks in advace.
Tom

Reply With Quote
  #2  
Old November 5th, 2009, 06:49 PM
June7 June7 is online now
Contributing User
Click here for more information.
 
Join Date: Apr 2009
Location: The Great Land
Posts: 1,097 June7 User rank is Captain (20000 - 30000 Reputation Level)June7 User rank is Captain (20000 - 30000 Reputation Level)June7 User rank is Captain (20000 - 30000 Reputation Level)June7 User rank is Captain (20000 - 30000 Reputation Level)June7 User rank is Captain (20000 - 30000 Reputation Level)June7 User rank is Captain (20000 - 30000 Reputation Level)June7 User rank is Captain (20000 - 30000 Reputation Level)June7 User rank is Captain (20000 - 30000 Reputation Level)June7 User rank is Captain (20000 - 30000 Reputation Level) 
Time spent in forums: 2 Weeks 17 h 51 m 40 sec
Reputation Power: 240
This query displays all records from table1 with other 2 tables each joined to table1 to retrieve those table fields.

SELECT Table1.ID, Table1.Name1, Table2.Name2, Table3.Name3
FROM Table3 RIGHT JOIN (Table2 RIGHT JOIN Table1 ON Table2.ID = Table1.ID) ON Table3.ID = Table1.ID;

Result is:
id1 name1 name2 name3
..1 value1 value2

Last edited by June7 : November 5th, 2009 at 07:01 PM.

Reply With Quote
  #3  
Old November 6th, 2009, 01:02 AM
pc131 pc131 is offline
Registered User
ASP Free Newbie (0 - 499 posts)
 
Join Date: Nov 2009
Posts: 2 pc131 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 5 m 55 sec
Reputation Power: 0
Hi

I managed it:

SELECT t1.id as id1, t1.name1, t2.name2, t3.name3
FROM test1 t1
LEFT JOIN test2 t2 ON t1.id=t2.id
LEFT JOIN test3 t3 ON t1.id=t3.id


now when t2.id is found in t1 it displays

1 value2 NULL

but if t2.id not found in t1 it displays
1 NULL NULL

earlier my query displayed 1 NULL NULL even if t2.id existed in t1 table, because I was somehow inner joining t2.id nad t3.id:

FROM (test1 t1) LEFT JOIN (test3 t3, test2 t2)
ON (t1.id=t2.id and t1.id=t3.id)

which means that to results to test2 and test3 columns go values where t1.id=t2.id and t1.id=t3.id ant it is the same as t2.id=t3.id


thanks anyway

Reply With Quote
  #4  
Old November 10th, 2009, 11:43 PM
sakthi.tnj sakthi.tnj is offline
Registered User
ASP Free Newbie (0 - 499 posts)
 
Join Date: Sep 2009
Posts: 24 sakthi.tnj User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 8 h 45 m 13 sec
Reputation Power: 0
Re: Left join Problem

HI , I hope that the below query will be very useful for you..


Code:
select * from 
test1 
left outer join test2 on test1.id1= test2.id2
left outer join test3  on test1.id1=test3.id3


Thanks & Regards
Sakthimeenakshi.S

Reply With Quote
Reply

Viewing: ASP Free ForumsDatabaseSQL Development > Query - General - LEFT JOIN problem


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump





 Free IT White Papers!
 
Create the Optimal Architecture for your Critical Applications
Warburton's the largest independently owned bakery in the UK faced a number of difficult challenges in providing the most robust yet efficient IT infrastructure for their organization's success. IBM's services combined with their xSeries servers created the perfect platform for their SAP environment with sufficient flexibility, and did so in very time effective fashion.

 
Five Best Practices for Deploying a Successful Service-Oriented Architecture
This white paper describes the benefits you can expect with SOA, and how IBM can help take your business there.

 
Gartner Magic Quadrant for Application Delivery Controllers
Gartner summarizes its view on Application Delivery Controllers, evaluates strengths and weaknesses of solutions, and provides Magic Quadrant reporting for a quick comparison across all vendors. Learn from Gartner how you can benefit from an all-in-one device like Citrix NetScaler that delivers the highest levels of availability, performance and security.

 
Knowledge is Power
What you don't know can hurt you, and is likely costing you money and increasing your security risks during an era of scarce resources. This white paper proposes six key strategies that enterprise security managers can use to improve their network defense posture.

 
Rationalizing the Multi-Tool Environment
The rationalized multi-tool approach is flexible, scalable and cost effective. It provides the necessary input to the IT service management business processes. It preserves prior investments in monitoring tools, empowers technologists to select the best tools with which to do their jobs, and enhances effective response to incidents.

 

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2010 by Developer Shed. All rights reserved. DS Cluster 11 Hosted by Hostway
For more Enterprise Application Development news, visit eWeek