CODEWITHSUNDEEP
mysqlsqlsql-serversql-server-2008sql-server-2005
Jannat Arora
Jannat Arora

Reputation: 2989

Join of tables returning incorrect results

There are 4 sql tables:

Listings(Amount, GroupKey, Key, MemberKey),
Loans(Amount, GroupKey, Key, ListingKey),
Members(City, GroupKey, Key)
Groups(GroupRank, Key, MemberKey)

Now, if one wants to find out the loans which are also listings and find the members city and GroupRank for the members in the loan table. Here, the group table contains information about grous of which members are a part of. and also perform a select operation as given below:

select Listings.Amount, Members.City, Groups.GroupRank
from listings, loans, members, groups
where Listings.Key=Loans.ListingKey and
    Members.Key=Listings.MemberKey and
    Listings.GroupKey=Groups.Key

The above join is giving an incorrect result, please point out where I am going wrong. Also I am new to SQL so please excuse the novice question.

Upvotes: 0

Views: 90

Answers (3)

Bohemian
Bohemian

Reputation: 424983

I would firstly change your query to use the more modern join syntax, which allows outer joins. Tr this:

select Listings.Amount, Members.City, Groups.GroupRank
from listings
left join loans on Listings.Key=Loans.ListingKey
left join members on Members.Key=Listings.MemberKey
left join groups on Listings.GroupKey=Groups.Key 
    and/or Loans.GroupKey=Groups.Key
    and/or Members.Key=Groups.MemberKey

You may need to play with the criteria on the last join (maybe they should be "or" not "and" etc).

Upvotes: 0

fancyPants
fancyPants

Reputation: 51868

Note: The following is just a guess what your problem is. Like others said, clearify your question.

You want to JOIN ( http://dev.mysql.com/doc/refman/5.1/de/join.html ) those tables. What you write is just another form of a join, meaning it has the same effect. But you "joined" a bit too much. To make things clearer a syntax has been invented to make things clearer and avoid such mistakes. Read more about it in the link given above.

What you want to achieve can be done like this:

SELECT
Listings.Amount, Members.City, Groups.GroupRank
FROM
Listings
INNER JOIN Groups ON Listings.GroupKey=Groups.Key
INNER JOIN Members ON Members.Key=Listings.MemberKey

You don't do a SELECT on the Loans table, you don't need it in this query.

This is the INNER JOIN which will give you a result where every row in table A has an according entry in table B. When this is not the case, you have to use the LEFT or RIGHT JOIN.

Upvotes: 1

Peter Kiss
Peter Kiss

Reputation: 9319

Maybe the problem is related to the join type (INNER). Try LEFT JOIN for example but Mark has right: you should clearify your question.

Upvotes: 0

Related Questions