Clever Geek Handbook

Nullable primarykey

I have two tables.

Purchase:

  • PurchaseId

  • SenderId

  • ReceiverId

Clients:

  • Customerid

    (primary-unique)
  • CUSID

    (Not primary - not unique - Allow null)
  • FirsName

  • LastName

I want to do multiple reports from all purchases with username and lastnames in customers table. I cannot do any relationship. All my codes are giving me error or returning wrong results. Please help me (receiver and sender values ​​are equal to CUSID in customer table) Relationship is wrong: as I cannot make any primary key as my field allows null.

(select (tbl_Customers.FirstName + '-' +  tbl_Customers.LastName) as receiver
FROM         tbl_PurchaseForms
INNER JOIN
tbl_Customers ON tbl_PurchaseForms.ReceiverUId = tbl_Customers.CUSID) 
or
(select (tbl_Customers.FirstName + '-' +  tbl_Customers.LastName) as sender
FROM         tbl_PurchaseForms
INNER JOIN
tbl_Customers ON tbl_PurchaseForms.SenderUId = tbl_Customers.CUSID)
+3


source to share


2 answers


I am admittedly a little confused by your question, but it looks like you are trying to return all customers who have either received or sent something from your purchasing table by binding their cusid field?

If so, something like this should work (I've included an extra column called Purchase Type if you need to differentiate between senders and recipients):



SELECT 
    'Sender' as PurchaseType, 
    C.FirstName, 
    C.LastName
FROM Customers C 
    JOIN Purchase P ON C.CUSID  = P.SenderId
UNION 
SELECT 
    'Receiver' as PurchaseType, 
    C.FirstName, 
    C.LastName
FROM Customers C 
    JOIN Purchase P ON C.CUSID  = P.ReceiverId

Good luck.

0


source 


Select p.*
,(Select  c.FirstName + '-' +  c.LastName  from customers c where c.CUSID  = P.ReceiverId) as  receiver
,(Select  c.FirstName + '-' +  c.LastName  from customers c where c.CUSID  = P.SenderId) as  Sender
from purchase p

while a purchase must be designed to include this



Select p.*
,(Select  c.FirstName + '-' +  c.LastName  from customers c where c.Customerid  = P.ReceiverId) as  receiver
,(Select  c.FirstName + '-' +  c.LastName  from customers c where c.Customerid  = P.SenderId) as  Sender
from purchase p
0


source






More articles:


All Articles