Trouble Writing a Complicated Query with MySQL

I need a query that'll get me results, using two different tables.

The input is $tagID, given from a开发者_如何学编程 $_GET. The first table, coupons_tags, has a field for tagID and a field for couponID. The second table, coupons, has a field for couponID and the rest of the fields are the information that I want.

How would I run a query that can find all of the coupons that match the input variable from the $_GET, going by the information I provided? I had this semi-working using multiple queries but the code was terrible, as I'm new to multi-table queries.

I'm using MySQL and PHP.

---

While the solution jkj has provided you with is working, it uses an older way of joining tables, which in particular mixes join conditions and filter conditions in one clause, namely the WHERE clause.

The currently standard way of joining proposes dedicated keywords that allow you to separate the join logic from the filter logic, thus making your query clearer, I dare say.

Here's how you would go according to the current standards:

SELECT c.*
FROM coupons_tags ct
  INNER JOIN coupons c ON ct.couponID = c.couponID
WHERE ct.tagID = $tagID

Here you are specifying the joining with the INNER JOIN clause and putting the join condition, ct.couponID = c.couponID, after the ON keyword, thus leaving the filter condition, ct.tagID = $tagID, in the WHERE clause.

This solution is also featuring table aliases (ct, c), which, too, help you to make your script more readable when you have to query multiple tables.

If, when querying multiple tables, you specify certain columns for selecting instead of just * or alias.*, like I did, remember to include the table alias too. It is not always necessary, as some columns are only present in only one of the tables you are querying and the DB engine won't be confused figuring out which table you meant that column to belong to, but that's always a good practice to have the alias attached, as it, again, improves the readability as well as the maintainability of your scripts.

---

Here is a started for the SQL-query. I'm sure how to call it in PHP though.

SELECT coupons.something, coupons.somethingelse
FROM coupons_tags, coupons
WHERE coupons_tags.coupons_id = coupons.coupons_id
  AND coupons_tags.tag_id = $tagID

You can think that the tables are put "side by side" so that each horizontal row has the same coupons_id in both tables.

The second where clause filters out the tags that you don't want.