CODEWITHSUNDEEP
phpmysqlsql
Lewis Smith
Lewis Smith

Reputation: 1345

mysql where clause based on another query

I am trying to get a single query to work rather than two where the first makes an array and then loop through the other.

The first query returns a set of users, and then I want to query another table based on these users, with a bit of research I ended up with this but it doesn't work...

SELECT FreeText, (
    SELECT EmailAddress FROM customers WHERE AccessLevel = 'callcentre'
) AS User FROM orders WHERE FreeText = User

I would prefer to do a single query but if thats not possible then I will work around it with the first creating and array then looping through the second

Any help appreciated

Example results, User would contain names, e.g. lsmith, nrowe, pmerle

Then the second query would retrurn the order rows where they are set as the FreeTxt

Example table structure

customers table
Id, Email,  Add1,        Add2    ect...
23, lsmith, someaddress, road...

Orders
Id, customerId, FreeTxt, Product
54, 23,         lsmith,  mob

Output for this would be lsmith + Id from orders I only asked for FreeTxt whilst testing

Upvotes: 1

Views: 392

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

The subquery is not necessary you can do the same with a inner join
but be sure that the orders.FreeText = customers.EmailAddress really match 

  SELECT orders.FreeText, customers.EmailAddress
  FROM orders
  INNER JOIN customer on orders.FreeText  = customers.EmailAddress 
        and customers.AccessLevel = 'callcentre';

Upvotes: 6

Related Questions