get non matching results from one table compared to another

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

get non matching results from one table compared to another

Post 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
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post 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
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Post by kendall »

fractalvibes,

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

Kendall
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Post 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
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Post 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
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Post by kendall »

Ok,

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

Thanks
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

np. I just translated the fractalvibes' suggestion to SQL. :D
phoggy
Forum Newbie
Posts: 20
Joined: Tue Sep 16, 2003 2:03 pm

Post by phoggy »

Could you post it? i'm curious. thx.
Post Reply