Need A Join?

The_PHP_Newb

I think I need a join but have never done them before.

Table A fields: foo, bar
Table B fields: id, payment, approved, string, extra

select id, sum(payment) as total from tableB where approved = '1' and extra != '1' and string >= '6' and string <= '10' group by id

That gives me what I need but I also need to add this:

from tableA WHERE foo = '1' and bar != ''

Any help is appreciated.. thanks!

UPDATE: Table A fields also has ID in it. I think it would need something like this:

from tableA WHERE foo = '1' and bar != '' and id = (id from table😎

johanafm

SELECT fields, tableA.ambigousName, tableB.ambigousName
FROM tableB
INNER JOIN tableA ON tableB.id = tableA.id
WHERE approved=1 AND extra != 1

Or perform a left join if you also want to include rows from tableB that have no matching rows in tableA (columns selected from tableA will then be null)

The_PHP_Newb

johanafm;10979551 wrote:
SELECT fields, tableA.ambigousName, tableB.ambigousName
FROM tableB
INNER JOIN tableA ON tableB.id = tableA.id
WHERE approved=1 AND extra != 1
Or perform a left join if you also want to include rows from tableB that have no matching rows in tableA (columns selected from tableA will then be null)

Thank you for the response. I'm not sure I'm any closer than I was though.

Here's what I'm trying.

SELECT id, sum(payment) as total, tableA.ambigousName, tableB.ambigousName
FROM tableB
INNER JOIN tableA ON tableB.id = tableA.id
WHERE approved='1' AND extra != '1' and string >= '$start_date' and string <= '$end_date' group by id

COMES FROM TABLE A
approved='1' AND extra != '1'

COMES FROM TABLE B
string >= '$start_date' and string <= '$end_date' group by id

johanafm

What isn't working as you want? The query you have says something like

Take any rows from B where $start_date <= string <= $end_date
Link this together with any rows from A where A.id = B.id. If no such rows eixsts, the rows from B that were found in 1. will be discarded
Discard any rows from 2. that do not have approved = 1 and extra = 1

The exact order in which the tables are queried and the search/join criteria are applied depends on the data and indices you have. Your DBMS will try to optimize the query plan.

But to summarize, you should get any row for which all these things are true
string between start and end date, and approved = 1 and extra = 1, and having an id match in both tables. Only rows where all these things are true will be retrieved.

The_PHP_Newb

Exactly what you're describing is what I need in terms of your #1-#3 process. The problem is I get his as output:

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given

I've checked and rechecked the code and it's exactly as it should be with regard to what you've given me to work with.

johanafm

mysql_query returnes either a result set resource, on success, or false on failure. You should always check for success/failure when performing queries, and this also gives you the possibility of getting useful debug information.

$q = 'this is garbage';
$result = mysql_query($q);

if (!$result)
{
	echo mysql_errno() . ': ' . mysql_error() . '<br/>' . $q;
}
else
{
	# success
}

echo or error_log as needed, although never display errors in production, just in test environment. Also, it's possible that you can live with errors for some queries, while you should die/exit on errors for other queries (such as validating user for login purposes).