Pulling data from two tables

[TapeGun007]

Table "Deals":

DealID (Yeah that's an L and then a capital i)

Product

Price

Date

Status (I only care about the deals that are marked as "won")

ProspectID

Table "Prospects":

ProspectID

FirstName

LastName

Address

 

I could probably code this just fine, but I know it would be a horrible way to code it, so I'm asking for help on the proper way to code it.  I want to display a deal that was sold, and then reference the first, last name and address of that deal.

For example:

Termite Treatment |  $800 | 7-31-22 | Bob | Matthews | 123 Nowhere

I suppose it should show the other way around too with the client info first and then each deal.

I know that I could run the initial mySQL to pull the deal information and when it gets to the prospect id, run another query to pull the rest of the information, however, I am quite certain this would be the completely WRONG way to do it.  Then I discovered the JOIN commands, and I assume it would be an INNER JOIN?  The part where I get a little lost is that there are multiple deals under a Prospect.

Any guidance would be appreciated.

Edited August 10, 2022 by TapeGun007

[requinix]

Are you trying to show prospects with some of the deal information? Or deals with some of the prospect information?

The latter is easier: you SELECT the fields you want FROM the deals table, then JOIN in the prospects table ON the two tables matching their prospect IDs (and also add the fields you want from that to the SELECT list, of course). The fact that the same prospect could show up multiple times for multiple deals isn't important.
The former is a bit trickier because you should think a little harder about precisely what it is you want to do. Show a prospect and then list the associated deals underneath? Somehow summarize all the matching deals?

Your example seems to suggest that you want to focus on the deal information, then show alongside it the information about its prospect.

[TapeGun007]

Yes, the latter.

Ok, so like 

INNER JOIN Deals ON Prospects.ProspectID = Deals.ProspectID;

I mean something like that anyway I presume.

[requinix]

Looks good so far.