Crazy selection help

Hi everyone -

I have two tables, one table (A) that holds a field called location that
has the partial path information of the file

and the second table (B) holds the full path including the filename

I only want to display the records from the partial path (A)
table that do not reside in the second (B) table


Table A =
imagefilename, description, directory, companyname, location
"96.jpg","test","Prog\2006_02","Marey, John","Prog\2006_02\96.jpg"
"274.JPG","disney","Prog\2006_07","Amy Gross","Prog\2006_07\274.JPG"
"570.jpg","sdfsdf","Prog\2007_06","Lof3,"Test3","Prog\2007_06\570.jpg"
"850.JPG","222","Prog\2007_08","Malis, Mark","Prog\2007_08\850.JPG"

Table B =
imagefilename
"d:\webdata\web\uploads\qfimages\Prog\2006_02\96.jp g"
"d:\webdata\web\uploads\qfimages\Prog\2006_03\112.p df"
"d:\webdata\web\uploads\qfimages\Prog\2006_03\127.j pg"

I was thinking about using a cross join with a like condition,
but it fails (go figure)

could someone offer a little help to the query that i need to
perform??

thanks
tonyuse a LEFT OUTER JOIN from A (left table) to B (right table)

the ON condition will match columns (using LIKE or string functions or whatever)

the WHERE clause will test the PK of the right table being NULL (indicating no match found)|||Why don't you post the SQL you have tried so far?|||SELECT *
FROM tbl2 INNER JOIN
tbl1 ON tbl2.imagefilename LIKE '%' + tbl1.location|||DECLARE @.TableA table (imagefilename varchar(100), [description] varchar(100)
, directory varchar(100), companyname varchar(100), location varchar(100))

DECLARE @.TableB table (imagefilename varchar(100))

INSERT INTO @.TableA (imagefilename, description, directory, companyname, location)
SELECT '96.jpg','test','Prog\2006_02','Marey, John','Prog\2006_02\96.jpg' UNION ALL
SELECT '274.JPG','disney','Prog\2006_07','Amy Gross','Prog\2006_07\274.JPG' UNION ALL
SELECT '570.jpg','sdfsdf','Prog\2007_06','Lof3,Test3','Pr og\2007_06\570.jpg' UNION ALL
SELECT '850.JPG','222','Prog\2007_08','Malis, Mark','Prog\2007_08\850.JPG'

INSERT INTO @.TableB(imagefilename)
SELECT 'd:\webdata\web\uploads\qfimages\Prog\2006_02\96.j pg' UNION ALL
SELECT 'd:\webdata\web\uploads\qfimages\Prog\2006_03\112. pdf' UNION ALL
SELECT 'd:\webdata\web\uploads\qfimages\Prog\2006_03\127. jpg'

SELECT 'TableA' AS Source, Location
FROM @.TableA
UNION ALL
SELECT 'TableB' AS Source, imagefilename
FROM @.TableB b
WHERE NOT EXISTS (SELECT * FROM @.TableA a
WHERE b.imagefilename LIKE '%'+a.location)|||SELECT *
FROM tbl2 INNER JOIN
tbl1 ON tbl2.imagefilename LIKE '%' + tbl1.locationnow change it to a LEFT OUTER JOIN and add this --

... where tbl1.location IS NULL|||GREAT !!!!

you folks are the best!

thank you x10000

Counting Unique Values?

I have a database that holds information about the location of our members. Their location is held as an ID that it gets from another table in the databse.

Is there an SQL SELECT query I can write that will return a result listing the ID and the number of times it appears? I am sure this is rather simple but I just am unaware of the code?

thanks.Assuming that you are using SQL, you could use the GROUP BY clause to do what you've described.

What tool(s) are you using, and what is your table design?

-PatP|||I am using MS Access. The table has fields holding member data. All items that will potentially be repeated ie gender, location, member package, member category, etc are populated with a unique ID referencing another table containing the data that is pertinent to those. All the IDs are held in number fields.

I assume that is the info you were after?|||yes, Access supports COUNT(*) and GROUP BYselect ID,count(*) as rows from sometable group by ID

Counting occurences

Hi,
Can anyone tell me how I would produce a table that holds just e.g. the
countries that occur more than once in another table?
This is what I have:
SELECT
city.country, count(*) as count
FROM city
GROUP BY city.country having count > 1;
But this gives me a table with 2 columns which is not what I want!
Help required and appreciated.
Hi,
Try the staging table method mentioned in:
'INF: How to Remove Duplicate Rows From a Table'
http://support.microsoft.com/?id=139444
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"M McEvoy" <mmcevoy@.iolfree.ie> wrote in message
news:c8lpvv$5n0$1@.kermit.esat.net...
> Hi,
> Can anyone tell me how I would produce a table that holds just e.g. the
> countries that occur more than once in another table?
> This is what I have:
> SELECT
> city.country, count(*) as count
> FROM city
> GROUP BY city.country having count > 1;
> But this gives me a table with 2 columns which is not what I want!
> Help required and appreciated.
>

Counting occurences

Hi,
Can anyone tell me how I would produce a table that holds just e.g. the
countries that occur more than once in another table?
This is what I have:
SELECT
city.country, count(*) as count
FROM city
GROUP BY city.country having count > 1;
But this gives me a table with 2 columns which is not what I want!
Help required and appreciated.Hi,
Try the staging table method mentioned in:
'INF: How to Remove Duplicate Rows From a Table'
http://support.microsoft.com/?id=139444
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"M McEvoy" <mmcevoy@.iolfree.ie> wrote in message
news:c8lpvv$5n0$1@.kermit.esat.net...
> Hi,
> Can anyone tell me how I would produce a table that holds just e.g. the
> countries that occur more than once in another table?
> This is what I have:
> SELECT
> city.country, count(*) as count
> FROM city
> GROUP BY city.country having count > 1;
> But this gives me a table with 2 columns which is not what I want!
> Help required and appreciated.
>