2 tables, 1 query. Join help appreciated. [SOLVED]

[eluser]codelearn[/eluser]
Another problem with another facet of MySQL/Active Record I am still learning.

I have 2 tables. Table 1 is restaurant coupons with all of the coupon info and a rest_id field which ties into the id field of the restaurant table. For instance a coupon for izza from John's Pizza would have a rest_id value of 10, and the id value of 10 in the restaurant table points to all the info about John's Pizza.

If your still with me, how would I make a query (join statement I believe) which would pull all of the data from restaurant coupons table depending on where statements made in the actual restaurant table.

My exact problem is that I need to show coupons based on which school they are at, and that information is only in the restaurant table - not the coupon table.

Thanks guys.

[eluser]zauber[/eluser]
This should work (in principle) to show you a table with all the coupons in one column, and all the schools from another.

SELECT
coupon.name AS coupon,
restaurant.school AS school
FROM
coupon
INNER JOIN restaurant ON coupon.rest_id = restaurant.id

If, for whatever reason there are some coupons with rest_id's that don't match any restaurant.id, then this query will not show them. If what you want is really a list of ALL coupons, showing the school only if there is a matching restaurant, then replace the "INNER JOIN" with a "LEFT JOIN".

If what you want is not a list of all coupons, but just the school for a coupon with a given coupon.id, then try this:

$this->db->query("SELECT restaurant.school FROM coupon INNER JOIN restaurant ON restaurant.id=coupon.rest_id WHERE coupon.id=?",array($myCouponId));

[eluser]codelearn[/eluser]
Zauber...

To clarify:

What I need is all of the coupons from say Harvard University. The "coupon table" only has the rest_id field (the ID field in the "restaurant" table). The restaurant table however will let me know that an ID value of 10 is from Harvard University whereas a value of 12 is another school.

If this was all in one table all I would have to do is:

Code:

$this->db->where('school_id',$this->uri->segment(2));
$this->db->get('rest_coupons');

Do you see what I mean and/or did you answer that exact question and I'm just slow?

Thanks!!!

[eluser]ejangi[/eluser]
So, wouldn't you just go:

Code:

$this->db->join('restaurant', 'coupon.rest_id = restaurant.id');
$this->db->where('school_id',$this->uri->segment(2));
$this->db->get('rest_coupons');

or

Code:

$this->db->join('restaurant', 'coupon.rest_id = restaurant.id', 'left');
$this->db->where('school_id',$this->uri->segment(2));
$this->db->get('rest_coupons');

... untested of course, but that "should" do the trick.

[EDIT]: Wait, I think I'm the slow one - I didn't read the post correctly, so the above is pretty much a waste... Sorry. :S

[eluser]codelearn[/eluser]
.

[eluser]codelearn[/eluser]
[quote author="codelearn" date="1198126265"][quote author="ucantblamem" date="1198124777"]So, wouldn't you just go:

Code:

$this->db->join('restaurant', 'coupon.rest_id = restaurant.id');
$this->db->where('school_id',$this->uri->segment(2));
$this->db->get('rest_coupons');

[/quote]
No, you read my post right.

Your officially the man. Thanks.

[eluser]zauber[/eluser]
[quote author="codelearn" date="1198122203"]
Do you see what I mean and/or did you answer that exact question and I'm just slow?
[/quote]

No, you're not slow, I was in fact answering the exact opposite question. From what I now understand you want to fetch all the coupons for a given school. I was telling you how to get the school for a given coupon.

[quote author="codelearn" date="1198122203"]
The "coupon table" only has the rest_id field (the ID field in the "restaurant" table)
[/quote]

Really? Only one column containing rest_id, and nothing else (name, coupon number, whatever)? If a coupon is nothing more than a restaurant number, then how do you tell coupons apart? Or are you saying that the coupon table is simply a list of restaurants that "have coupons"? If this is the case, I would strongly advise that you change your database layout to instead have just a true/false column in the restaurant table called "has_coupon".


[quote author="codelearn" date="1198122203"]

Code:

$this->db->where('school_id',$this->uri->segment(2));
$this->db->get('rest_coupons');

[/quote]

Sorry, but I myself am quite a noob to codeigniter, so I'm not familiar with the database interface. I am however reasonably proficient with SQL, so I just tend to use the following syntax:

Code:

$q = $this->db->query("SOME QUERY HERE WITH param1= ?, param2=?", array($myParam1,$myParam2));
$result = $q->result();

I'll do it that way, and then perhaps someone can fill you in on how to do the same thing with the "implicit syntax" (just made the term up, hope you get it)

Nevertheless, it's apparent you're trying to fetch a list of coupons for a given school, so I will assume that you have some way of telling coupons apart - like a coupon code. So I will assume your coupons-table has (at least) two columns: "coupon_code" - the identifier of a coupon, and "rest_id" containing the identifier for the restaurant with which a coupon is associated.

In that case, this is what you want:

Code:

$q = $this->db->query("
    SELECT coupon.coupon_code
    FROM coupon INNER JOIN restaurant ON  restaurant.id = coupon.rest_id
    WHERE restaurant.school_id=?",
    array(
        $this->uri->segment(2),
    )
);
$result = $q->result();

If instead, what you mean was, "tell me the restaurants for school X that have coupons", then this should do it:

Code:

$q = $this->db->query("
    SELECT id
    FROM restaurant
    WHERE
        id IN (SELECT rest_id FROM coupon) AND
        restaurant.school_id=?",
    array(
        $this->uri->segment(2),
    )
);
$result = $q->result();

[eluser]codelearn[/eluser]
Zaub,

Thanks! The problem is solved and to clarify the coupon table has all the other information too - I just mentioned the rest_id because its the only one that matters in this situation.

Solved!