SQL joining tables

Hey Everyone,

I am having problems with the sql for the following in coldfusion. I have 2 tables that I want to join, and then get a count of the total number of records from those two tables, not based on any specific field. So basically I want a count of all the records in one table plus a count of all the records from another table, but as one variable, so that I can then output that variable. The reason why I want to understand this join is because I will be later selecting a few fields from a totally different table to compare to these tables that I want joined together, so I want to base one table against two tables that are joined together so they seem like there is just a join between 2 tables, not three. Thanks for the help in advance.

I'm not understanding what you are asking for. Can you create a couple of "dummy" table data lists and then explain what you want for a result?

Thanks for the reply so soon. I have two tables with 30,000+ doctors listed in each table. I want to join the two tables together, get a total count from the two tables combined into one query, and then later be able to have those two tables joined, and then cross match those tables with another seperate table and get a count, based on a few fields that all the first two have in common with the thrid, but only after the first two are joined, so it is as if they are one table together matching against a second table, and not three. I want a result of the total number of doctors that have the same last name, first name, and state, from a seperate table vs. those two tables joined together. Thanks for the help.

Sorry man, that is all a jumble to me. What I need to see is something like:

Table 1
fieldA, fieldB, fieldC
fieldA data|fieldB data|fieldC data
kdfalsdf|39853|3585

Table 2
fieldX, fieldY
faldskf|l098
skdjhfa;d|353

Now this is what I want in the result....

Without something concrete to look at I can't follow your description.

Table 1
firstname, lastname, state

Table 2
firstname, lastname, state

I need table 1 and table 2 joined together, to get a total of their results, and not where they match, just a total count of their results.

table 3
firstname, lastname, state

then I need a list of results and a count based on all three matching fields for table 3 and (tables 1 and 2), once tables one and two have been combined, not based on anything, so that table one and two are basically the same table, because they have a combination of all the same results.

I really do appreicate your help. Thanks

OK I'll take a stab at it.

select distinct t3.firstname, t3.lastname, t3.state
from table3 t3,
(select t1.firstname, t1.lastname, t1.state
from table1 t1
union all
select t2.firstname, t2.lastname, t2.state
from table2 t2
) u
where t3.firstname = u.firstname
and t3.lastname = u.lastname
and t3.state = u.state


I'm still unsure of where the counts come into play, but that should be a start.

That works perfect. THanks a lot. The count is the only real information I need from the query though. I want to know how many doctors match, I don't need to know anything else, other than how many of them match. I tried adding a count statement, but it fails everytime. Is this possible? By the way, I used to live in Chapel Hill, I saw that your from the RTP area. Nice to get some help from home

If all you need is the count of the matches, what about this?

select count(*) as matchCount
from table3 t3,
(select t1.firstname, t1.lastname, t1.state
from table1 t1
union all
select t2.firstname, t2.lastname, t2.state
from table2 t2
) u
where t3.firstname = u.firstname
and t3.lastname = u.lastname
and t3.state = u.state

Works, Great thanks

I was trying to put all the distinct fields within a count, and this is the way to go. Thanks again

One more question. is it possible to bring the table name in from a form. Because when I try this it doesn't work.

<cfquery name="crossmatch" datasource="datasource">
select count(*) as doctortotal
from '#form.list#' t3,
(select t1.FIRST_NAME, t1.LAST_NAME, t1.STATE
from Verispan t1
union all
select t2.FIRST_NAME, t2.LAST_NAME, t2.STATE
from Accent t2
) u
where t3.FIRST_NAME = u.FIRST_NAME
and t3.LAST_NAME = u.LAST_NAME
and t3.STATE = u.STATE
</cfquery>

Don't put it in quotes.

You're the man.