Join or Union?

[TapeGun007]

I've been reading so many websites about LEFT JOIN, and now UNION, so before I go off on a major rabbit trail please help me figure out which is best by telling you what I want to accomplish.

I'm going attach two images of my tables:

The first table is called Prospects, and the 2nd table is simply called Deals.

I'm going to try to write what I want in a non working version of code, but I hope it will translate what I want better:

Under Table Deals, the field Status must be equal to "Sold"
Under Tables Deals and Prospects, the ProspectID must match
Under Table Deals, the PIGLead must be equal to 2

I want the table to show the Prospect information (name, address, phone, etc), then under the Prospect information, I want to see the deals sold to them where Status = "Sold".  I want to see the Product and Price.

Best method?

[benanamen]

You would do a JOIN on ProspectID

[TapeGun007]

Ok, so I was on the right track.  I wrote this code:

SELECT
    Prospects.ProspectID,
    Prospects.FirstName,
    Prospects.LastName,
    Prospects.Address,
    Prospects.Address2,
    Prospects.City,
    Prospects.State,
    Prospects.Zip,
    Deals.ProspectID,
    Deals.Product,
    Deals.Price
FROM
Prospects
LEFT JOIN Deals
ON Prospects.ProspectID = Deals.ProspectID

So this works.  But how can I narrow it down to just like WHERE PIGLead = 2 ?

I now see why Union does not work, there would not be an equal amount of rows returned (if I'm understanding this correctly).

[TapeGun007]

ON Prospects.ProspectID = Deals.ProspectID AND Deals.PIGLead = 2 
WHERE Deals.Status = 'Sold'

So... the ON line works sort of, but it lists clients that nothing was sold to.  So I tried the WHERE above and now nothing comes up. LOL.

Edited April 22, 2019 by TapeGun007

[TapeGun007]

Ok never mind.  I got it.  I had to use an INNER JOIN.  I had also made an error above, the Status column was set to "Won" not "Sold".  Thanks for helping me get on the right path.

[benanamen]

 The the left join would have worked if you switched the tables around.

[Barand]

The LEFT JOIN should work with the tables as they are, but the conditions on the deal table need to be in the JOIN ON and not in the WHERE.

IE

FROM
Prospects
LEFT JOIN Deals ON Prospects.ProspectID = Deals.ProspectID 
                   AND Deals.PIGLead = 2 
                   AND Deals.Status = 'Won'

 

[TapeGun007]

Is this a less correct way to write it then?  This gave me the result I was looking for, but being a bit OCD, I like to ensure I'm writing solid code.

SELECT
    *
FROM
Deals
INNER JOIN Prospects
ON Prospects.ProspectID = Deals.ProspectID AND Deals.Status = 'Won' AND Deals.PIGLead = ?
ORDER BY SoldDate DESC

Yeah, I just put the * in there to shorten it.

[Barand]

The point I was making is that if you use "... LEFT JOIN Deals" then any conditions on deals selection must go in the JOIN ON clause. If you put them in the WHERE clause, the LEFT JOIN does not function correctly.

Use A LEFT JOIN B when you want to show all selected record values from A and values from B only if a matching record/s exists.

Use A (INNER) JOIN B when you only want to show records where there is a match in both tables.

Only use LEFT JOIN when necessary - they are much slower than INNER JOINS.

[TapeGun007]

Barand,

Thanks as always.  I did discover through my trials and errors that the conditions in the WHERE clause were causing some issues.  After much reading I had put them in the JOIN ON section, but you confirmed what I wasn't 100% sure of.

So your definition confirms also that I was supposed to use the INNER JOIN because I did only want to show records if the match was in both tables.

I did not know that LEFT JOINS were much slower however, that part I had not yet discovered.