Need SQL Query Help

[Guest]

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?

[Dan_Bracuk]

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

[Guest]

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).

[Dan_Bracuk]

It's hard to make suggestions without knowing what you intend to do with the data. What's your desired end result?

[Guest]

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>

[The_ScareCrow]

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

[-__cfSearching__-]

> 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

[Guest]

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

[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

[Guest]

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__-]

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

[Guest]

quote:

---

Originally posted by: -==cfSearching==-
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

---

Yes, the first example is correct.
Row#, IndividualID, CompanyID
1, 1, 7

[-__cfSearching__-]

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

The sql you posted looks right. But that error sounds like you have an incorrect column reference somewhere. This is not tested, but what happens if you try this


SELECT
t1.document_main_account_id,
t1.document_main_company_account_id,
t2.client_name as IndiName,
t3.client_name as CompanyName
FROM
document_main AS t1
INNER JOIN users_account AS t2 ON t1.document_main_account_id = t2.account_id
INNER JOIN users_account AS t3 ON t1.document_main_company_account_id = t3.account_id

Update I do not know if you need an INNER or LEFT JOIN, but try the syntax first.

[Guest]

quote:

---

Originally posted by: -==cfSearching==-

Update I do not know if you need an INNER or LEFT JOIN, but try the syntax first.

---

Hi Azadi, Update Oops, sorry, I meant cfSearching

I'm still getting:
"Invalid column name 'document_main_account_id'. "

[Newsgroup_User]

do not use AS to define table aliases in FROM clause:

FROM
document_main t1
INNER JOIN users_account t2 ON t1.document_main_account_id = t2.account_id
INNER JOIN users_account t3 ON t1.document_main_company_account_id =
t3.account_id


Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[-__cfSearching__-]

Azadi wrote:
> do not use AS to define table aliases in FROM clause:

Why not?

[Dan_Bracuk]

quote:

---

Originally posted by: -==cfSearching==-
Azadi wrote:
> do not use AS to define table aliases in FROM clause:

Why not?

---

doesn't work.

[-__cfSearching__-]

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.

[Guest]

quote:

---

Originally posted by: -==cfSearching==-
he OP did not say which database they are using.

---

Sorry about that. I am using MS SQL.

[Guest]

Ok, call me a moron. The errors I was getting was for an incorrect query a little further on. Of course I should have been doing this in it's own template. Now I am.
The syntax Dan and cfSearching provided does indeed work. Although it returns many more records (much of them NULL), I think this is the right track.

Can anyone recommend a decent site or book where I can start learning about Joins?

[-__cfSearching__-]

quote:

---

Originally posted by: Dan Bracuk
doesn't work.

---

It does with MS SQL

[Newsgroup_User]

-==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/

[Dan_Bracuk]

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.