Exclusive Join?

[jonkemm]

I'm sure I've got the name of this wrong - i'm looking for some SQL code that will return records from a table ONLY when they are linked to records in another table.To put it in real terms, I want to only show the gallery icons for nights that have pictures in them. If you hover your mouse over the folder icons it says how many pics are in each gallery.Here's the current SQL:

set galleryRS=conn.execute("SELECT *  FROM events  WHERE events_archive IS NOT NULL ORDER BY events_date_year DESC, events_date_month DESC, events_date_day DESC")

Here's the page:Nightclub Gallery PageIf I do an INNER JOIN I get far too many results.Please help!Jk

[Yahweh]

I imagine that you have two tables like this:

gallery_table-------------------------galleryID	galleryTitle1			2007-01-012			2007-01-053			2007-02-174			2007-02-21image_tableimageID	  galleryID (foreign key goes back to gallery_table.galleryID)1			12			13			34			45			36			17			28			39			1

Its a helluva lot easier to add a "totalImages" column to your gallery table, and perform a simple "SELECT * FROM gallery_table" to get the number of images in each gallery. When you have 10s of 1000s of records, your queries begin to lag using a subquery or complex join.But, if you really want to go the route of using a join, you can do it two ways:Use a subquery:

SELECT	g.galleryid, 	g.gallerytitle,	(		Select			Count(*)		FROM			image_table i		WHERE			g.galleryID = i.galleryID	) as totalImagesFROM	gallery_table g

Use a join:

SELECT	g.galleryID,	g.gallerytitle,	i.Count(*) as totalImagesFROM	gallery_table gLEFT JOIN	image_table i		ON			(g.galleryID = i.galleryID)GROUP BY	g.galleryID,	g.galleryTitle

The GROUP BY is needed because the count(*) is an aggregate function. As long as you're comparing galleryID's, then you won't get any records from your image table with a null value, so you don't need to use an INNER JOIN.

[jonkemm]

I imagine that you have two tables like this:

picCheck=conn.execute("SELECT COUNT(pics_id) as totalrecords FROM pics WHERE pics_event_no = "&galleryRS("events_id"))

But when I try to test for it:

if picCheck("totalrecords") > 0 then

but that gives a type mismatchSo I tried to convert to a string:

if CLng(picCheck("totalrecords")) > 0 then

Still no good - please help?

[aalbetski]

try using where exists:Select * from Table1 as T1 where exists (Table2 as T2 where T2.id = T1.id)and the reverse:Select * from Table1 as T1 where not exists (Table2 as T2 where T2.id = T1.id)