1

Update: Added Schema to the bottom...

I have a table of contracts: tbl_contract

And a table of users associated with the contract: tbl_contract2user

There can be any number of entries in tbl_contract2user, in which an entry existing means that the relationship exists (along with a pending column where 1 = pending and 0 = approved).

My goal here is to select all contracts where there is 1 (or more) active users within the time frame specified (see below).

The problem I'm having is the ability to sort out these contracts properly. The date range is working fine... For some reason I'm having trouble understanding when the number of users is 1 or more...(vs. 0) and yes - I'll be working with that data set (After the query).

See below for the start of the query...

$result = mysql_query("SELECT tbl_contract.id 
    FROM        tbl_contract
    LEFT JOIN   tbl_contract2user ON tbl_contract.id = tbl_contract2user.contractID
    WHERE       tbl_contract2user.pending = 0
    AND         tbl_contract.startDate <= {$billing['start_time']}
    AND         tbl_contract.endDate    >= {$billing['end_time']}");

Schema:

tbl_contract: id, startDate, endDate, value, name, dateCreated

tbl_contract2user: id, contractID, userID, pending

Shackrock
  • 4,601
  • 10
  • 48
  • 74
  • Please include the table schema (as `CREATE TABLE` statements), sample data (as `INSERT` statements) and the desired results for the sample data. Note the mysql extension is outdated and on its way to deprecation. Instead, use mysqli or PDO and prepared statements. – outis Oct 28 '11 at 23:33
  • @outis These are really simple tables. I adeded the schema above (not as create/insert statements, sorry - I'm a little unsure about that). Also I didn't know that the mysql statements in PHP were going to be depreciated - why is that? Does this mean it will no longer work once depreciated? – Shackrock Oct 28 '11 at 23:47
  • Could you clarify what you mean by 1 or more active users? According to your schema you have no flag to indicate that, do you mean active contracts? – Mike Purcell Oct 29 '11 at 00:01
  • @DigitalPrecision existance in the `tbl_contract2user` table, with `pending = 0`, indicates an active user. – Shackrock Oct 29 '11 at 00:06
  • @Shackrock: MySQL statements aren't *[deprecated](http://en.wikipedia.org/wiki/Deprecation)* (not deprec*i*ated), the old mysql *extension* is being deprecated. It's a "soft" deprecation, which means "officially not recommended and on its way to hard deprecation". When it's a hard deprecation, using it will cause warnings. Eventually, the mysql extension will be dropped. The transition period is to give developers time to update to mysqli or PDO. New code shouldn't use the mysql extension at all. – outis Oct 29 '11 at 00:30
  • ... Using SQL statements to show table structure and for [sample](http://sscce.org/) data results in a [better question](http://sscce.org/) because it unambiguously and clearly illustrates what you're working with, what you want and gives others something to test against. As it stands, it isn't clear what your problem (the difference between what you have and what you want) is. – outis Oct 29 '11 at 00:34

1 Answers1

2

What is the actual problem? Do you get all records instead of only those with a related user? If yes, turn the LEFT JOIN into a INNER JOIN and all contracts without a relation are gone...

The real issue is that if I have 6 users in one contract, I get 6 rows returned instead of ONE row for that contract

This is exactly what a JOIN does. It takes all records from the left side and joins them with the records on the right side by using a specific condition. If you only want to know how many users a contract has, you can you a GROUP BY clause and a COUNT(*):

SELECT      tbl_contract.id, COUNT(*) AS userCount
FROM        tbl_contract
LEFT JOIN   tbl_contract2user ON tbl_contract.id = tbl_contract2user.contractID
WHERE       tbl_contract2user.pending = 0
AND         tbl_contract.startDate <= {$billing['start_time']}
AND         tbl_contract.endDate    >= {$billing['end_time']}
GROUP BY    tbl_contract.id

If you need more information about the user, you really need all these 6 rows...

CodeZombie
  • 5,367
  • 3
  • 30
  • 37
  • ah, good point. That was a stupid mistake.... The real issue is that if I have 6 users in one contract, I get 6 rows returned instead of ONE row for that contract... – Shackrock Oct 29 '11 at 00:07
  • Thanks man, I think this is working. I'm having trouble seeing the difference between the LEFT and INNER joins though... I thought I understood these, but in this case I'm a little confused - care to explain it in this case? Thanks man! – Shackrock Oct 29 '11 at 23:52
  • 1
    @Shackrock: Take a look at this: http://stackoverflow.com/questions/38549/sql-difference-between-inner-and-outer-join – CodeZombie Oct 30 '11 at 00:00
  • aha! So I've been using inner joins all along and didn't realize it I guess (`FROM a,b` is inner by default)...lol. Thanks man. – Shackrock Oct 30 '11 at 14:31
  • @Shackrock: if you haven't already, also see the various articles that use images of [Venn diagrams to illustrate joins](http://www.khankennels.com/blog/index.php/archives/2007/04/20/getting-joins/); that question links to some, and others can be found via web searches. Note that the Venn diagrams (as generally applied) are only analogies–the sets in the Venn diagrams shouldn't be the tables (which is what you usually see) but rather the left and right joins. An inner join, for example, is the intersection of the left and right joins, not the intersection of the tables. – outis Nov 03 '11 at 02:51