Skip to main content
A
Anonymous
November 5, 2008
Question

Need SQL Query Help

  • November 5, 2008
  • 7 replies
  • 2024 views
I need help combining two queries into one. This is what I have now:
<CFQUERY NAME="getInfo" datasource="myDS" BLOCKFACTOR="100">
SELECT
status.status_doc_id,
status.date_accepted,
status.date_received,
document_main.document_main_doc_type,
document_main.document_main_doc_id,
document_main_account_id,
document_main_company_account_id,
document_main_period_of_report,
users_account.client_name,
users_account.account_type
FROM
status,
document_main, users_account
WHERE
status.filing_status_user_id = '2'
AND status.Statustype = 'Live'
AND status.Currentstatus = 'Accepted'
AND status.status_doc_id = document_main.document_main_doc_id
AND users_account.account_id = document_main.document_main_company_account_id
</CFQUERY>



<CFQUERY NAME="getStuff" datasource="myDS" BLOCKFACTOR="100">
SELECT
users_account.client_name,
FROM
users_account
WHERE
account_id = '#getInfo.document_main_account_id#'
AND account_type = 'Individual'
</CFQUERY>

The problem is that the account_id referred to in the users_account table can be either a Company or an Individual. So in my html table the output would list both. A co-worker suggested a UNION ALL and to alias the client_name fields. But that query seems to ignore the alias after the union.

Can anyone help me out?
This topic has been closed for replies.

7 replies

D
Dan_Bracuk
Inspiring
November 8, 2008
I've heard good things about "Teach Yourself SQL in 10 Minutes" by Ben Forta, but, based on this thread, you seem to be doing ok.
N
Newsgroup_User
Inspiring
November 7, 2008
-==cfSearching==- wrote:
> Azadi wrote:
>> do not use AS to define table aliases in FROM clause:
>
> Why not?

it is not supported by all rdbms


Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/
-
-__cfSearching__-
Inspiring
November 7, 2008
Azadi wrote:
> do not use AS to define table aliases in FROM clause:

Why not?
D
Dan_Bracuk
Inspiring
November 7, 2008
quote:
Originally posted by: -==cfSearching==-
Azadi wrote:
> do not use AS to define table aliases in FROM clause:

Why not?

doesn't work.
-
-__cfSearching__-
Inspiring
November 7, 2008
Azadi wrote:
> it is not supported by all rdbms

Yes, I see your point. The OP did not say which database they are using. I mixed it up with another thread and thought they were using MS SQL, where the "AS" keyword is supported for table aliases.
D
Dan_Bracuk
Inspiring
November 6, 2008
Maybe something like this will help.

select stuff
from table1 t1
join table2 t2 on something
join table2 t3 on something else

where whatever
order by something
A
Anonymous
November 6, 2008
quote:
Originally posted by: Dan Bracuk
Maybe something like this will help.

select stuff
from table1 t1
join table2 t2 on something
join table2 t3 on something else

where whatever
order by something


Hi Dan,

Thanks again for trying to help me (and thanks to everyone else as well). I'm really, really, trying but now I get a sql error. I did what you suggested and got the errors below. I then made a very simple query and I still get the same error(s).

The simple query is :
SELECT
t1.document_main_account_id,
t1.document_main_company_account_id
FROM
document_main t1
JOIN users_account t2 ON
t1.document_main_account_id = t2.account_id

The error is:
"The column prefix 't1.document_main' does not match with a table name or alias name used in the query."

If I take out the prefixes the error is:
"Invalid column name 'document_main_account_id'. "
-
-__cfSearching__-
Inspiring
November 6, 2008
robs67 wrote:
> Yes, that's correct.
> #1 - Bob
> #7 - John's Company

Just so I do not lead you down the wrong path, you are saying _one_ record in document main would contain both id's like this:

Row#, IndividualID, CompanyID
1, 1, 7


.. rather than this

Row#, IndividualID, CompanyID
1, 1, NULL
1, NULL, 7
-
-__cfSearching__-
Inspiring
November 5, 2008
> So in the same record in document_main,
> document_main_account_id refers back to an individual while
> document_main_company_account_id refers back to a
> company account.

The _same_ record contains two id values that refer back to (2) separate records in the users_account table?

users_account:
#1 - Bob
#3 - Bob's Company

A
Anonymous
November 6, 2008
quote:
Originally posted by: -==cfSearching==-
The _same_ record contains two id values that refer back to (2) separate records in the users_account table?

users_account:
#1 - Bob
#3 - Bob's Company



Yes, that's correct. But Individual and Company don't necessarily go hand in hand. It may be:
#1 - Bob
#7 - John's Company
D
Dan_Bracuk
Inspiring
November 5, 2008
It's hard to make suggestions without knowing what you intend to do with the data. What's your desired end result?
A
Anonymous
November 5, 2008
I need to write the output to an html table like:

<table>
<cfouput query="getinfo">
<tr>
<td>
#document_main_doc_id#
</td>
<td>
#IndieName#
</td>
<td>
#companyName#
</td>
</tr>
</cfoutput>
</table>
T
The_ScareCrow
Inspiring
November 5, 2008
quote:
A co-worker suggested a UNION ALL and to alias the client_name fields. But that query seems to ignore the alias after the union.


When doing a UNION all queries have to have the same number of columns and all columns will have the name associated with the column in the first query.

So, do the UNION ALL

Then do a switch in the cfoutput of the query or you could cfoutput group by the account_type.

Ken
D
Dan_Bracuk
Inspiring
November 5, 2008
If your db supports case contstructs, that seems to be the way to go.

select case when somefield = 1 then 'abc' else 'def end alias,
etc
A
Anonymous
November 5, 2008
Thanks for your help Dan.

I added this to the query:
CASE users_account.account_type
WHEN 'Company' THEN users_account.client_name
END AS COMPANYNAME,
CASE users_account.account_type
WHEN 'Individual' THEN users_account.client_name
END AS IndiNAME

The problem I have is that because of:
AND users_account.account_id = document_main.document_main_company_account_id
the output is empty for "IndiNAME".

Users_account.account_id can refer to a record with"individual" or "company" as the account type. So in the same record in document_main, document_main_account_id refers back to an individual while document_main_company_account_id refers back to a company account.

So of course if I change the above to:
AND users_account.account_id = document_main.document_main_account_id
then "COMPANYNAME" is empty in the output.

I can't figure out how to get those two values without one or the other being empty (or breaking the link altogether from the document_main table to the users_account table).
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices