2 Replies - 368 Views - Last Post: 01 August 2013 - 09:39 AM Rate Topic: -----

#1 Keylogger  Icon User is offline

  • D.I.C Regular

Reputation: 7
  • View blog
  • Posts: 344
  • Joined: 14-February 11

Joins returns nulls/duplicates

Posted 08 May 2013 - 09:57 AM

Hi guys!

I have the following:

Table Products

ID  | IDClient | Description
 1  |    1     |  Red roses
 2  |    1     |  Blue Jeans
 3  |    2     |  Motocycle
 4  |    3     |  Car

Table Clients

ID  | IDClient | Name
1   |    1     | John
2   |    2     | Queen
3   |    3     | Mariah


So, as you can see, I have duplicated info, and I need it!

So, I need to join the table Products with Clients. If I use the following join: Products.IDClient = Clients.ID it retrieves me Null values.
SELECT clients.IDClient AS 'The ID', P.description AS 'Product', clients.Name AS 'Name'
FROM clients
LEFT JOIN Products AS P ON P.IDClient = clients.ID

The ID |   Product    | Name
   1   |    NULL      | John
   2   |  Motocycle   | Queen
   1   |    NULL      | John

If I join Products.IDClient -> Clients.IDClient
SELECT clients.IDClient AS 'The ID', P.description AS 'Product', clients.Name AS 'Name'
FROM clients
LEFT JOIN Products AS P ON P.IDClient = clients.IDClient

The ID |   Product    | Name
   1   |  Red Roses   | John
   1   |  Blue Jeans  | John
   2   |  Motocycle   | Queen
   3   |     Car      | Mariah
   1   |  Red Roses   | John
   1   |  Blue Jeans  | John 


I thought that could be because of the GROUP BY clause. So I added:
SELECT clients.IDClient AS 'The ID', P.description AS 'Product', clients.Name AS 'Name'
FROM clients
LEFT JOIN Products AS P ON P.IDClient = clients.IDClient
GROUP BY clients.IDClient, P.description, clients.Name

But only changes the order of the values..it continues to duplicate them.

Ahh, forget. This is the problem of being programming since the morning. At this hours it seems I don't pay attention to things. I found now that I had a duplicated value in Clients..

I tested on SQL Management Studio, and with the code I told here, it worked nicely.
But I will need to test with more info. I will not give this topic as solved..because I could have further problems.

Sorry, and thanks.

This post has been edited by Keylogger: 08 May 2013 - 09:53 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Joins returns nulls/duplicates

#2 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: Joins returns nulls/duplicates

Posted 18 May 2013 - 11:11 AM

The way you are currently trying to set this up, a product can only be bought by one client. Assuming that is what you want (it probably isn't, more on that in a minute), here is how to fix it:

Clients should not have an "IDClient" field. Products needs to have one; that is the "foreign key" that you use to join the tables. So, get rid of that field entirely, and set up your SQL like this:
SELECT clients.ID AS 'The ID', P.description AS 'Product', clients.Name AS 'Name'
FROM clients c
JOIN Products p ON p.ClientID = c.ID


(You don't need to do a left join here, unless you want clients that haven't bought any products.)

A more typical scenario is this: one client can order multiple products, and one product can be sold to multiple clients. In this case, Products and Clients are in a "many-to-many" relationship. In order to resolve a many-to-many relationship, you have to create another table that is in one-to-many relationship with each, such as Orders in this case. The table contains each of the others' primary keys, and any fields specific to a single order, such as date, cost, etc. You can either make the two foreign keys, taken together, be the primary key for the Orders table (I don't generally recommend this), or you can create another primary key field (which I prefer).

I often use the example of a library to explain many-to-many relationships. You have members and books. Each member borrows any number of books over time, and each book gets borrowed any number of times over time. The Books table has an ID field that is its primary key, and the Members table does also. Loans has an ID, a BookID, and a MemberID. To get a list of who borrowed which book when, you would do something like this:
SELECT m.MemberName, b.BookName, l.CheckoutDate, l.ReturnDate
FROM Members m
JOIN Loans l on m.ID = l.MemberID
JOIN Books b on b.ID = l.BookID
If you wanted just one name, you would add a WHERE clause at the end. (Note: INNER JOIN and JOIN are equivalent.)

Finally, GROUP BY is used when you're doing calculations or aggregates. For example, if you wanted a list of how many books each member has borrowed, you would do this:
SELECT m.MemberName AS Name, Count(l.ID) AS [Count]
JOIN Loans l on m.ID = l.MemberID
GROUP BY m.MemberName
What you are saying here is that you want a count of the loans for each membername value separately. Otherwise, the count of l.ID would be a count of all the records in the loans table. SQL Server will return an error if you forget the GROUP BY clause in this situation; in fact, it will return an error if any member of the select list is not referenced either in an aggregate function or in the GROUP BY clause.

This post has been edited by BobRodes: 18 May 2013 - 02:22 PM

Was This Post Helpful? 2
  • +
  • -

#3 Keylogger  Icon User is offline

  • D.I.C Regular

Reputation: 7
  • View blog
  • Posts: 344
  • Joined: 14-February 11

Re: Joins returns nulls/duplicates

Posted 01 August 2013 - 09:39 AM

I read today your answer, because I had a similiar problem..and it was very helpful. I implement you advice of another table for when we have "many-to-many" relations. It's never to late to say thank you!

This post has been edited by Keylogger: 01 August 2013 - 09:39 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1