Jump to content
TapeGun007

Join or Union?

Recommended Posts

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:

1624731621_ScreenShot2019-04-21at7_54_33PM.png.a019f597aacc60e10da01ce12c3cf7a3.png1806086938_ScreenShot2019-04-21at7_55_04PM.png.28f685a3f488601f613a17d83b040fee.png

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?

Share this post


Link to post
Share on other sites

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).

Share this post


Link to post
Share on other sites
Posted (edited)
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 by TapeGun007

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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'

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.