Jump to content

Pulling data from two tables


TapeGun007
 Share

Recommended Posts

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 by TapeGun007
Link to comment
Share on other sites

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.

Link to comment
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.

 Share

×
×
  • 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.