Page 1 of 1

get non matching results from one table compared to another

Posted: Fri Jan 23, 2004 1:40 pm
by kendall
Hello,

I have a table in which i one to display the information of table 1 based on the fact that their id doesnt exist in another table

Code: Select all

table 1
ID FIELD 1 yada yada

table 2
ID FIELD 2 yada yada

SELECT banner_images.ID, file, description, class, banner_images.grouping FROM banner_images LEFT JOIN banner_stats ON banner_images.ID = banner_stats.ID GROUP BY banner_images.ID HAVING class = 'A' AND banner_images.grouping != 'B' ORDER BY RAND() LIMIT 1
I had the above query but i still get results whose values ID that appears in both tables

I thought that a LEFT-JOIN gave results that DONT match???!! when it comes to understanding the types of joins i really am stumnped as to how they work and event a simple
WHERE table1_id != table2_id
gives me results that exists in both tables.
Kendall

Posted: Fri Jan 23, 2004 10:07 pm
by fractalvibes
Do your left join where the matching predicate from that other table is equal to null. Sort of an inverse left join, really.

fv

Posted: Tue Jan 27, 2004 12:53 pm
by kendall
fractalvibes,

What the hell do you mean man?????

Kendall

Posted: Tue Jan 27, 2004 2:07 pm
by kendall
ok,

I let me explain the scenario....

i have 2 tables i created for a banner ad system...banner_info banner_stats

when a banner is displayed it writes the stats to the banner_stat table
What i wanted to do is make sure that all the banners in banner_info are displayed at least once.

so im trying to create a query that will return results from banner_info whose id is not in banner_stats LIMIT 1

the problem is im still getting results whose id's are in both tables

Can anyone help me out

Kendall

Posted: Wed Jan 28, 2004 1:07 pm
by Weirdan
if you need the rows from table1 that have no matching rows (based on join condition) in table2 you can do something like:

Code: Select all

select * from table1 left join table2 on table1.field1=table2.field1 where table2.field1 is null

Posted: Thu Jan 29, 2004 8:03 am
by kendall
WEirdan,

thanks.... i was able to get this to work as well

Code: Select all

SELECT banner_images.ID, file, description, class, banner_images.grouping FROM banner_images LEFT JOIN banner_stats ON banner_images.ID = banner_stats.ID WHERE banner_images.ID NOT IN (banner_stats.ID) AND banner_images.class = '".$type."' AND banner_images.grouping != '".$group."' ORDER BY RAND() LIMIT
Funny thing doe...when it has reached the last banner to be displayed...i get a 0 as an id number....(this is when testing the php script) yet when i do a query from the msyql commmand...i will get the last banner...but testing it using the script hmmmm...i dont get it i get and id = 0 when there is an id...hmmm

im working on it

Kendall

Posted: Thu Jan 29, 2004 8:09 am
by kendall
Ok,

I figured it out...it was an error in the data

Thanks

Posted: Sat Jan 31, 2004 1:51 pm
by Weirdan
np. I just translated the fractalvibes' suggestion to SQL. :D

Posted: Sat Jan 31, 2004 5:49 pm
by phoggy
Could you post it? i'm curious. thx.