Join query not working properly :(

[coding_noobie]

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!

[WolfShade]

Try this:

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

HTH,

[coding_noobie]

Thanks, I actually tried adding table_one.id = (making sure the id was the same as the collect_id) and that sorted it

Thanks though

[DaveMaxwell]

@coding_noobie

Thanks, I actually tried adding table_one.id = (making sure the id was the same as the collect_id) and that sorted it

So did you do

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.

Just a suggestion. Take from it what you will…

[r937]

@DaveMaxwell

The first one does full table scans of both…

[lifts head out of punch bowl] wha??

got proof? a link, perhaps? or is this just an urban myth?

[DaveMaxwell]

@r937

[lifts head out of punch bowl] wha??

got proof? a link, perhaps? or is this just an urban myth?

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…)

[r937]

i don’t dispute that he omitted the join condition, i’m just saying that most optimizers are smart enough not to do two table scans

[DaveMaxwell]

Fair enough. It’s a funky enough statement regardless, and hopefully he put the condition comparing the two or he’s not really doing a join anyways…

[r937]

yup, i agree…

as bob “explicit joins” marley used to say, please don’t you rock my boat

[coding_noobie]

I did actually use the first one but will take your suggestion and use the second one.

Thanks

[DaveMaxwell]

@coding_noobie

I did actually use the first one but will take your suggestion and use the second one.

Good idea because the first one isn’t a join.

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.