I’ve got two tables in a database and want to select the results from one but only if a particular field is set to 1 in the other.
I’ve got this:
$query="select table_two.id,table_two.title,table_two.description,table_two.link from table_two,table_one where table_two.visible=1 and collect_id = 8 and table_one.sub = 1 order by table_two.position";
but it still gets the results even if the value of table_one.sub is 0
I’m probably missing something somewhere but I’ll be damned if I can find it!
SELECT tt.id, tt.title, tt.description, tt.link
FROM table_two tt JOIN table_one t1 ON t1.id = tt.[related column in table_two]
WHERE tt.visible = 1
AND [give table alias for].collect_id = 8
AND t1.sub = 1
select table_two.id
, table_two.title
, table_two.description
, table_two.link
from table_two, table_one
where table_two.visible = 1
and collect_id = 8
and table_one.id = 8
and table_one.sub = 1 order by table_two.position
or did you do
SELECT tt.id
, tt.title
, tt.description
, tt.link
FROM table_two tt
JOIN table_one t1 ON t1.id = tt.collect_id
WHERE tt.visible = 1
AND tt.collect_id = 8
AND t1.sub = 1
If you did the first one, I would suggest you consider switching to the second for performance sake and future proofing. The first one does full table scans of both, which the second filters out and only uses those records which match on BOTH tables, which will improve performance the more records you have.
If you don’t compare the two table queries, it will (won’t it?) If you look, there’s no where table_one.id = table_two.collector_id (which based on how he worded his response to WolfShade seemed to be how he solved his problem…)
It was returning all rows from table one that met one criteria and all rows from table_two which met the other criteria, and not if the records are related between the tables. If, by happenstance, you set the queried values to different ids, you’d end up looking at totally unrelated records, or if you happen to have more than one row of each, you’ll end up with a multiple of the number of rows you’re supposed to.