MYSQL - selecting data from one table, and then using it to select data from another table

Joined
12 Feb 2006
Posts
17,174
Location
Surrey
i'm struggling to figure this one out.

i'll try to keep it brief.

i have a table which stores all customer quotes along with their details (email address, quote number etc)

what i'm working on is to have another table which tracks follow up email history, so if i send day 1 follow up it tracks this, day 3 follow up etc.

I have the quote number, and what i want to do, is say:

select from quotes where quote number = quote number, get the email address, and then, select from email_history, where email = got_email

i've excluded some of bits like where date is within last 3 days etc to keep it simplified.

how do i go about this? i'ts the joining, and then using the selected email i'm struggling with.
 
Soldato
Joined
3 Jun 2005
Posts
3,027
Location
The South
Thinking out loud, JOIN quote to emailHistory table and then filter by quote number, ie -

SQL:
SELECT
  `emailHistory`.*
FROM `emailHistory`
JOIN `quote` ON `quote`.`quoteEmailAddress` = `emailHistory`.`emailAddress`
WHERE
  `quote`.`quoteNumber` = 5678;
http://sqlfiddle.com/#!9/eb91e44/6

Something like that any way, although you definitely want some indexes on this.
 
Soldato
Joined
6 Feb 2019
Posts
17,293
Not familiar with MySQL but with oracle or MS you would do this using the Union function. Doing the join to get the first set of data then Union to join the table with the second join
 
Last edited:
Back
Top Bottom