CODEWITHSUNDEEP
phpmysqlsqlselect
John Kariuki
John Kariuki

Reputation: 5754

complex mysql Statement

I am having a little issue here. So I have two tables and I need to fetch data in what i think is a complex way. So below is a summary of the two tables

clients

  1. client_id
  2. name
  3. booked [default is 0]

accommodation

  1. accommodation_id
  2. client_id
  3. date
  4. price
  1. What I would like to have is select all client id's from tbl clients where booked is 1
  2. then using the client_ids select all rows in accommodation whose client_id is an of those returned in step 1

What i had in mind proved difficult for me

$select_accomodation = "SELECT * FROM `accommodation` WHERE `booked` = 1";
if($select_accomodation_run = @mysql_query($select_accomodation))
{
    //awesome code that does no 2       
}

What is the best possible way to accomplish tasks 1 and 2. Hopefully in one mysql statement

Upvotes: 2

Views: 81

Answers (3)

AdamMc331
AdamMc331

Reputation: 16730

My thought, is first write a subquery that gets the Ids you want for part 1, which is:

SELECT client_id FROM clients WHERE booked = 1

Then, you can use that subquery inside another query for the accomodations table using the IN clause

SELECT a.* FROM accomodation a WHERE a.client_id IN (SELECT c.client_id FROM clients c WHERE c.booked = 1);

Upvotes: 1

dave
dave

Reputation: 1430

Try this: 

 select t1.client_id, t2.accommodation_id, t2.client_id, t2.data, t2.price from clients t1 JOIN accommodation t2 on t1.client_id = t2.client_id WHERE t1.booked = 1

Upvotes: 3

jcbwlkr
jcbwlkr

Reputation: 7999

If you just want to select all accommodations for booked clients you could do 

SELECT a.*
FROM accommodation a
INNER JOIN clients c ON a.client_id = c.client_ID
WHERE c.booked = 1

Upvotes: 4

Related Questions