data extract from two table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

data extract from two table

Author

Topic

pareekfranksoul
Starting Member

26 Posts

Posted - 2007-09-02 : 11:19:23

Hello

I have two tables

Table 1 contains records of batsmen (runs,fours,sixes)etc
Table 2 contains records of bowlers (wickets,wide,noballs)etc

if a player is allrounder or if the player has played both the role of bowler and batsmen then his record is present in both the table otherwise in one table or not in any table.

suppose player id = 21 (Yuvraj)
match ID = 50

i can extract the record from both the tables no problem

suppose player id = 22 (Sachin)
match ID = 50

Its record is not present in Table 2 and query results 0 rows.

My query is:

Select a.sc_bowler_wickets,a.sc_Bowler_wide,a.sc_bowler_noball,b.sc_Batsmen_Runs,b.sc_batsmen_fours,b.sc_batsmen_sixes from Bowling_card a join Batting_card b on a.pm_id = b.pm_id and a.mm_match_id = b.mm_match_id where a.mm_match_Id=50 and a.pm_id=22

What i want is it will not show 0 rows but show 0 for respected fields

Thank You in advance

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-02 : 12:13:01

Try using a FULL OUTER JOIN to return results from both tables.

------------------------
Future guru in the making.

pareekfranksoul
Starting Member

26 Posts

Posted - 2007-09-02 : 12:22:37

Would you please modify my query in FULL OUTER JOIN becoz i have not that much experiance in SQL

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-02 : 12:39:05

quote:
Originally posted by pareekfranksoul

Would you please modify my query in FULL OUTER JOIN becoz i have not that much experiance in SQL

Actually looking at this again it seems your query requires that there is always a value in Bowling_card, is this your expectation?

If so, all you need is a left outer join, just type in left outer join where you have join.

Select a.sc_bowler_wickets,a.sc_Bowler_wide,a.sc_bowler_noball,b.sc_Batsmen_Runs,b.sc_batsmen_fours,b.sc_batsmen_sixes from Bowling_card a LEFT OUTER JOIN Batting_card b on a.pm_id = b.pm_id and a.mm_match_id = b.mm_match_id where a.mm_match_Id=50 and a.pm_id=22

------------------------
Future guru in the making.

pareekfranksoul
Starting Member

26 Posts

Posted - 2007-09-02 : 12:49:42

Yes i always want value in bowling_card but if one player is not a bowler or not bowled on that match then its record is not coming on that table and becoz of this my query result shows 0 rows even if player record in table1

example: if player record present in both the tables then query results:

sc_bowler_wickets sc_Bowler_wide sc_bowler_noball sc_Batsmen_Runs sc_batsmen_fours sc_batsmen_sixes
4 3 1 6 1 0

But if player record present in one table only then query results:

sc_bowler_wickets sc_Bowler_wide sc_bowler_noball sc_Batsmen_Runs sc_batsmen_fours sc_batsmen_sixes

but i want:

sc_bowler_wickets sc_Bowler_wide sc_bowler_noball sc_Batsmen_Runs sc_batsmen_fours sc_batsmen_sixes

4 3 1 0 0 0

Thanks

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-02 : 12:56:48

Ok, then it should work - although it will return null where you want 0, you will need to use ISNULL() to replace the nulls with 0.

------------------------
Future guru in the making.

pareekfranksoul
Starting Member

26 Posts

Posted - 2007-09-02 : 13:24:50

Select a.sc_bowler_wickets,a.sc_Bowler_wide,a.sc_bowler_noball,b.sc_Batsmen_Runs,b.sc_batsmen_fours,b.sc_batsmen_sixes from Bowling_card a LEFT OUTER JOIN Batting_card b on a.pm_id = b.pm_id and a.mm_match_id = b.mm_match_id where a.mm_match_Id=50 and a.pm_id=22

Results 0 rows becoz no record with pm_id = 22 is present in Bowling_card table

When i will change it to check for b.pm_id = 22 then it shows lots of results which i don't want

Let me clear you what i want:

Table1:

Pm_id MM_matches_id sc_Batsmen_Runs sc_batsmen_fours sc_batsmen_sixes
21 50 6 1 0
22 50 9 0 1

Table2:

Pm_id MM_matches_id sc_bowler_wickets sc_Bowler_wide sc_bowler_noball
21 50 4 3 1

Now i want my query results for Pm_ID = 21

sc_bowler_wickets sc_Bowler_wide sc_bowler_noball sc_Batsmen_Runs sc_batsmen_fours sc_batsmen_sixes

4 3 1 6 1 0

And Now i want my query results for Pm_ID = 22

sc_bowler_wickets sc_Bowler_wide sc_bowler_noball sc_Batsmen_Runs sc_batsmen_fours sc_batsmen_sixes

0 0 0 9 0 1

Now please can give me exact query which will give result accourding to my requirement.

Thanks

pareekfranksoul
Starting Member

26 Posts

Posted - 2007-09-02 : 15:20:22

please anybody can help

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-02 : 18:27:22

Try this:
SELECT a.sc_bowler_wickets,a.sc_Bowler_wide,a.sc_bowler_noball,b.sc_Batsmen_Runs,b.sc_batsmen_fours,b.sc_batsmen_sixes
FROM Bowling_card a
CROSS JOIN batting_card b
WHERE (a.mm_match_Id=50 and a.pm_id=22)
OR (b.mm_match_Id=50 and b.pm_id=22)

------------------------
Future guru in the making.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources