how to join 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

General SQL Server Forums

New to SQL Server Programming

how to join table

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-07-17 : 16:43:21

THe working SQL
SELECT InvoiceNumber, COUNT(*)
FROM Transactions
GROUP BY InvoiceNumber
HAVING COUNT(DISTINCT CustomerID) > 1

i want to join a table to it.
the other table is 'customermappings'

it has also a customerid but also has column Datasourcecustomerid
which i want to include inthe result set.

I have no expe. in joining like that.

ebingeorge.sqlserver
Starting Member

7 Posts

Posted - 2011-07-17 : 16:53:25

SELECT t.InvoiceNumber, COUNT(t.CustomerID)
FROM Transactions t
INNER JOIN CustomerMappings cm
ON t.CustomerID = cm.CustomerID
GROUP BY t.InvoiceNumber
HAVING COUNT(t.CustomerID) > 1

I hope this query works. :)

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-07-17 : 20:57:31

It works but it only returns the invoicenum. i want to show the customermappings columns as well.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-17 : 21:34:12

how is the relationship between invoicenum and customermappings like ? one to one ? one to many ?

KH
[spoiler]Time is always against us[/spoiler]

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-07-17 : 21:41:27

this is customermappings.
DataSource (PK,FK,Char(10)notnull)
DataSourceCustomerId(PK,char(50),notnull)
CustomerId(FK,int,Notnull)

We want to join i guess on the customerid and get the other 2.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-17 : 21:48:16

it seems that in your customermappings table, there might be more than 1 record with the same CustomerID. If this is the case, which record do you want to show ?

KH
[spoiler]Time is always against us[/spoiler]

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-07-17 : 21:49:47

quote:
Originally posted by AdamWest

this is customermappings.
DataSource (PK,FK,Char(10)notnull)
DataSourceCustomerId(PK,char(50),notnull)
CustomerId(FK,int,Notnull)

We want to join i guess on the customerid and get the other 2.

Transacation.
Transactionid, int
Customerid, int
Department, int
Itemid, int
Categoryid, int
Quantity, numeric, (19,2)
Cost, Numberics (19,4)
DateCreated, datetime
Invoicenumber, char(20)
Datasource, char(10)
Datasourceid, char(20)
IMportid, int
Lastupdate, datetime
itemmappingid, int
Vendor, char(150)

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-07-18 : 07:16:07

Khatn, if possible to show all the records. this situation is something i want to show.

quote:
Originally posted by khtan

it seems that in your customermappings table, there might be more than 1 record with the same CustomerID. If this is the case, which record do you want to show ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-18 : 07:22:57

[code]
SELECT *
FROM (
SELECT InvoiceNumber
FROM Transactions
GROUP BY InvoiceNumber
HAVING COUNT(DISTINCT CustomerID) > 1
) D
INNER JOIN Transactions T ON D.InvoiceNumber = T.InvoiceNumber
INNER JOIN customermappings M ON T.CustomerId = M.CustomerId
[/code]

KH
[spoiler]Time is always against us[/spoiler]

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-07-18 : 08:04:01

HI KHtan, I ran this but it does not return any results.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-18 : 08:59:55

does this have any result ?


          SELECT InvoiceNumber
          FROM  Transactions
          GROUP BY InvoiceNumber
          HAVING COUNT(DISTINCT CustomerID) > 1

KH
[spoiler]Time is always against us[/spoiler]

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-07-18 : 09:17:45

yes it returns invoicenumber only.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-18 : 20:18:18

Then this should give you result also


SELECT *
FROM   (
          SELECT InvoiceNumber
          FROM  Transactions
          GROUP BY InvoiceNumber
          HAVING COUNT(DISTINCT CustomerID) > 1
       ) D
       INNER JOIN Transactions T       ON D.InvoiceNumber = T.InvoiceNumber

Check that for those CustomerID, there are records in customermappings table.

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources