Matching columns from one table to another

[Squiggy2]

I've got a database column that contains a person's name. I've got another table that has a column that contains names separated by a comma.

I want to match records where any records from the two columns have a matching name. I tried this:

<cfquery name="MyQuery" datasource="myds">

SELECT *

FROM TBL_A

WHERE TBL_A.customername IN (#DifferentQuery.ListOfNames#)

</cfquery>

Everything I try results in only getting one record. Basically, I want to get all records from one table where one of its columns contains names from a column in a different table.

[Adam Cameron.]

Have a look at the resultant SQL that is actually being passed to the DB (after all the CF side of things has been resolved), and that might give you a clue).

BTW: you should not be hard-coding your dynamic filter values into your SQL string, you should be passing them as parameters.

--

Adam

[Squiggy2]

Thanks Adam. How would I look at the resulting SQL - CFDump?

[Dan_Bracuk]

cfdump metainfo="yes" will work if you are on version 9.

However, if you have debugging turned on, it will also show up in that area.

[-__cfSearching__-]

another table that has a column that contains names separated by a comma.

I want to match records where any records from the two columns have a matching name.

Ouch. It would be much easier if the second table was normalized and did not store delimited values.  Do you have any choice about the table structure?

[Squiggy2]

cfsearching: I can re-arrange the db as needed. I'm just not that great at db normalization yet!

[-__cfSearching__-]

What's the structure of your second table? ie What does your "list" of customer names relate to ...?

-Leigh

[Squiggy2]

Leigh - One table is a list of editorials that appear on the website. The other table is a list of advertisers.

When someone views an editorial, we want advertisers that are relevant to that editorial to show on that web page.

To accomplish that, I made a system where my client can decide which ads get related to which editorials. The advertiser names get thrown into a column of the Editorials table. So, for each record in the Editorials table, there is a column showing which ads relate to that editorial, separated by commas.

I know there's a better way, but that's all I could come up with.

[-__cfSearching__- · Accepted solution]

That is a many-to-many relationship. ie One editorial can be related to many ads and vice versa.  It's best represented with three tables. Such as:

       Table:  [Editorial]    Columns: [ EditorialID (PK), EditorialText, ... ]

       Table:  [Advertiser]   Columns: [ AdvertiserID (PK), AdvertiserName ]

       Table:  [Editorial_Advertisers] Columns:  [EditorialID, AdvertiserID]

The advertiser names get thrown into a column...

my client can decide which ads get related to which editorials

The third table stores the relationships between editorials and ads.  So instead of storing a list of advertisers, you'd insert one record for each EditorialID + AdvertiserID combination. Then you could use JOINs to retrieve all the ads for EditorialID 123.

SELECT  a.AdvertiserName
FROM     Editorial_Advertiser ea INNER JOIN Advertiser a ON ea.AdvertiserID = a.AdvertiserID
WHERE   ea.EditorialID = 123

[Squiggy2]

That makes sense. It means having to re-create pretty much the whole application, but the way you laid it out makes much more sense than what I've got right now.

[-__cfSearching__-]

Yep, the new structure is far more flexible and better overall (ie performance, maintainence, ... ). When you store lists you end up paying for it sooner or later... usually in more ways than one 😉