CODEWITHSUNDEEP
sqlsyntaxfirebird
JonBravo
JonBravo

Reputation: 27

SQL - Joining tables with no common column

New at this so any tips would be appreciated!

I have the following query and the last bit of data I need is to bring in CONTACT.DATUS. The problem is that the CONTACT table has nothing in common (that I know of) with any of the tables I am already using. I can link them by going from tables SO -> CUSTOMER -> CONTACT but I haven't the slightest idea if that is even possible. You can see where I am trying to do this in the last join but clearly this wont work.

Thanks for any help you guys can send my way!

Select DISTINCT
 so.num AS Ref
, so.shiptoname AS Recipient_Full_Name
, so.shiptoaddress AS Address_1
, so.shiptocity AS City
, stateconst.name AS State
, so.shiptozip AS Zip
, so.billtoname AS Buyer_Name
, contact.datus AS Buyer_Email
, qbclass.name AS Class
, carrier.name AS Carrier
, CAST(soitem.datescheduledfulfillment as date) AS Fulfillment_Date
From SO
JOIN stateconst
ON so.shiptostateid=stateconst.id
JOIN qbclass
ON so.qbclassid=qbclass.id
JOIN soitem
ON so.id=soitem.soid
JOIN carrier
ON so.carrierid=carrier.id
JOIN contact
ON so.customerid=customer.id
ON customer.accountid=contact.accountid
WHERE CAST(soitem.datescheduledfulfillment as date) = '5/16/16'
AND qbclass.name<>'C- Online' AND qbclass.name<>'InterCompany'

Upvotes: 1

Views: 184

Answers (1)

user4650451
user4650451

Reputation:

Right now, it seems like you would pull back all Contacts for a Customer. I'm assuming that a Customer is a Company (ex. Acme Inc.) and Contacts are the Employees of Acme Inc (Bugs Bunny, Daffy Duck, etc...). Unless you have some sort of flag on the Contacts table indicating which contact you want back, you're current JOIN on Contact is going to pull everyone and likely create a duplicate row per Contact.

Your JOIN logic is pretty close but it looks like you skipped a step: Before you JOIN Contact, you need to JOIN Customer. Right now, it's just kind of being thrown into your JOIN clause; do it explicitly.

Select DISTINCT
 so.num AS Ref
, so.shiptoname AS Recipient_Full_Name
, so.shiptoaddress AS Address_1
, so.shiptocity AS City
, stateconst.name AS State
, so.shiptozip AS Zip
, so.billtoname AS Buyer_Name
, contact.datus AS Buyer_Email
, qbclass.name AS Class
, carrier.name AS Carrier
, CAST(soitem.datescheduledfulfillment as date) AS Fulfillment_Date
, contacts.DATUS AS DATUS
From SO
JOIN stateconst
ON so.shiptostateid=stateconst.id
JOIN qbclass
ON so.qbclassid=qbclass.id
JOIN soitem
ON so.id=soitem.soid
JOIN carrier
ON so.carrierid=carrier.id
--My Change--
JOIN customer
ON so.customerid = customer.id
JOIN contact
--Removed this ON so.customerid=customer.id--
ON customer.accountid=contact.accountid
--Done My Change--
WHERE CAST(soitem.datescheduledfulfillment as date) = '5/16/16'
AND qbclass.name<>'C- Online' AND qbclass.name<>'InterCompany'

Your joins don't all need to be based off your starting table, SO. In this case, start at SO, then JOIN to Customers on SO.customerid = Customers.ID and then JOIN Contacts on Customers.accountid = Contacts.accountid. Again, beware, this will currently pull back ANY Contact who shares an AccountID with your Customer.

Upvotes: 1

Related Questions