Query Question

[mxstu]

What do the values in these columns represent ?
REFER_TO_OTHER_PERSON_ID
REFER_TO_OTHER_PERSON_ID_2

[Samall]

I use REFER_TO_OTHER_PERSON_ID and REFER_TO_OTHER_PERSON_ID_2 to refer to the ID. So in my example Martin refers to ID 1 which is Jack.

[mxstu]

In this type of situation you usually use a self-referencing table join, but
why you have two different columns to refer back to the ID column? How are
these records related to each other? (ex.. Is Jack a supervisor and Martin is
his employee and James is an employee of Martin?)

[Samall]

No this is just an example. I will adjust my example to this:

ID
NAME
REFER_TO_OTHER_PERSON_ID


1, jack, NULL
2, bryan, NULL
3, martin, 1

What I want in the output is:
JACK
--martin (REFER_TO_OTHER_PERSON_ID -> JACK which is ID 1)
BRYAN

[mxstu]

....

(SQL is not tested)

If you want to return Jack, even if no other records refer to his ID ....
SELECT p.ID AS SupervisorID, p.Name AS SupervisorName,
s.ID AS EmployeeID, s.Name AS EmployeeName
FROM YourTable p LEFT JOIN YourTable s ON p.ID =
s.REFER_TO_OTHER_PERSON_ID
WHERE p.ID = 1

Otherwise,
SELECT p.ID AS SupervisorID, p.Name AS SupervisorName,
s.ID AS EmployeeID, s.Name AS EmployeeName
FROM YourTable p INNER JOIN YourTable s ON p.ID =
s.REFER_TO_OTHER_PERSON_ID
WHERE p.ID = 1

[mxstu]

Argh! I hate the formatting in the attach code option

[Samall]

No that is not what I'm looking for. I will adjust my example to this:

ID
NAME
REFER_TO_OTHER_PERSON_ID


1, jack, NULL
2, bryan, NULL
3, martin, 1
4, bla, NULL
5, bla2, NULL
6 bla3, 2
etc..

What I want in the output is:
JACK
--martin (REFER_TO_OTHER_PERSON_ID -> JACK which is ID 1)
BRYAN
--bla3 (REFER_TO_OTHER_PERSON_ID -> BRYAN which is ID 2)
BLA
BLA2
etc...

In your example I'm stuck to: WHERE p.ID = 1. I want to show the complete DB
table.

[mxstu]

Okay. That was only an example using the sample data provided. Try the
attached code. As in the last example, only records that do not refer to
another record (i.e. REFER_TO_OTHER_PERSON_ID IS NULL) are returned.

It's important to note that the CFOUTPUT groups the results by the same column
used in the ORDER BY clause of the SQL statement. So if you change the ORDER BY
clause, you need to to change the CFOUTPUT GROUP attribute to match.



<cfquery name="getData" datasource="YourDSN">
SELECT p.ID AS SupervisorID, p.Name AS SupervisorName,
s.ID AS EmployeeID, s.Name AS EmployeeName
FROM YourTable p LEFT JOIN YourTable s ON p.ID = s.REFER_TO_OTHER_PERSON_ID
WHERE p.REFER_TO_OTHER_PERSON_ID IS NULL
ORDER BY p.ID, s.ID
</cfquery>

<cfoutput query="getData" group="SupervisorID">
<b>#SupervisorName#</b><br>
<cfif EmployeeName is not "">
<cfoutput>--------#EmployeeName#<br></cfoutput>
</cfif>
</cfoutput>

[Samall]

Thanks mxstu! That is working fine.
Can I ask one more question. Let's say I want to output this:

What I want in the output is:
JACK
--martin
BRYAN
--some_name_2
------some_name_3
JAMES
etc...

Is that possible on the DB table I now have? or do I need to create an extra
field in my DB table?

[mxstu]

Is two levels the limit or do you forsee a need to display additional levels
..... ?

JACK
--martin
BRYAN
--some_name_2
------some_name_3
------------some_name_4
-----------------some_name_5
JAMES
etc...

[Samall]

Hi,
3 levels is the limit.

JACK level1
--martin level2
BRYAN level 1
--some_name_2 level2
------some_name_3 level3
etc..

Thanks!

[Samall]

Hello, Can someone help me on this point:
I have a simple DB table (ID, NAME, REFER_TO_OTHER_PERSON_ID).
---------------------
The content of the DB table is:
1, jack, NULL
2, bryan, NULL
3, martin, 1
4, bla, NULL
5, bla2, NULL
6 bla3, 2
etc..
---------------------
What I want to output is:
JACK
--martin (REFER_TO_OTHER_PERSON_ID -> JACK which is ID 1)
BRYAN
--bla3 (REFER_TO_OTHER_PERSON_ID -> BRYAN which is ID 2)
etc...
---------------------
The query to do that is:
<cfquery name="getData" datasource="YourDSN">
SELECT p.ID AS SupervisorID, p.Name AS SupervisorName,
s.ID AS EmployeeID, s.Name AS EmployeeName
FROM YourTable p LEFT JOIN YourTable s ON p.ID = s.REFER_TO_OTHER_PERSON_ID
WHERE p.REFER_TO_OTHER_PERSON_ID IS NULL
ORDER BY p.ID, s.ID
</cfquery>

<cfoutput query="getData" group="SupervisorID">
<b>#SupervisorName#</b><br>
<cfif EmployeeName is not "">
<cfoutput>--------#EmployeeName#<br></cfoutput>
</cfif>
</cfoutput>

---------------------

No here is the problem. Let's say I want to output: 3 levels:

JACK
--martin
BRYAN
--some_name_2
------some_name_3
JAMES
etc...

Is that possible on the DB table I now have? or do I need to create an extra
field in my DB table?

[Jaak]

Please can somebody help me with this though query

I have 2 fields in my table: owner and machine
none of them are unique

I want to do a query - as input I have a list of owners
as output I need the machines, but only one

jack radio
jack computer
jack cdplayer
bill radio
bill shoes
larry shoes
larry trouser
larry gps
kate cdplayer
kate car

as input I give 'jack AND bill AND larry'
as output I need 'radio computer cdplayer shoes trouser gps'

Can anybody help me?

[Birthe Gebhardt]

Jaak,
you can use the keyword distinct in your select. Then you don't get
duplicate data rows.

select distinct owner, maschine from table where owner in
('jack','bill','larry')

or if you only need the maschines

select distinct maschine from table where owner in ('jack','bill','larry')

brgd,
Birthe


Jaak wrote:

> Please can somebody help me with this though query
>
> I have 2 fields in my table: owner and machine
> none of them are unique
>
> I want to do a query - as input I have a list of owners
> as output I need the machines, but only one
>
> jack radio
> jack computer
> jack cdplayer
> bill radio
> bill shoes
> larry shoes
> larry trouser
> larry gps
> kate cdplayer
> kate car
>
> as input I give 'jack AND bill AND larry'
> as output I need 'radio computer cdplayer shoes trouser gps'
>
> Can anybody help me?

[hertelt]

I have a query that pulls counts on how many people sign up for my mailing list
each day. If no one signs up on a particular day, I don't get a row for it. How
do I test for the missing days and add them with 0 for the day?





SELECT datevalue(addDate) AS addDate, COUNT(dateValue(addDate)) as dailyTotal
FROM email
WHERE status = 1 AND addDate BETWEEN #startDate# AND #today# GROUP BY
dateValue(addDate)

[jonwrob]

Join your results to a query that selects a series of all dates, such as (for
Oracle):

select trunc (sysdate - rownum) date_series
from all_objects
where trunc (sysdate - rownum) between startDate and today

FYI all_objects is just a table with lots of rows. You can use anything that
has more rows than you expect to require.

JR

[Dan Bracuk]

jonwrob's approach will only work if a table exsists that actually has every
single date you need. I don't know that much about Oracle, so I'll take his
word for it that all_objects has every date you need.

But you might not be on oracle. If that's the case, I suggest a daily job
that looks for records the previous day, and adds the record as necessary.
Once you have that running, write a template for a one off job that fills in
your blanks.

For this one off job, get a list of all the dates you have, compare it all the
dates in your date range of interest, and produce a list of missing dates.
Loop through this list and add your missing records.

[MikerRoo]

There are better ways in PostgreSQL, Oracle and MS SQL 2005.

But, Here's a universal method (in MS Access syntax since that looks to be
what you're using):
You will need to create a new, static table in your DB. Once it's created,
it doesn't ever need to be touched again.
Create the table "tDigits".
It will have one column, "iDigit". Make this column an integer (NOT long
integer) and set it as the primary key.
Fill tDigits with ten rows, containing the digits 0 thru 9. This table must
have EXACTLY those rows and no others.
When that is done, the attached query will do what you seek as long as the
start and end dates are within 99 days of each other.
It should be obvious how to extend the coverage to 999 days but performance
suffers exponentially as the range magnitude is increased.



SELECT
tDaysInQuestion.dtSpanDay AS addDate
, COUNT (EE.addDate) AS dailyTotal
FROM
(
SELECT
DateValue (DateAdd ('d', ONES.iDigit + 10 * TENS.iDigit,
#startDate#)) AS dtSpanDay
FROM
tDigits AS ONES,
tDigits AS TENS
WHERE
DateAdd ('d', ONES.iDigit + 10 * TENS.iDigit, #startDate#) BETWEEN
#startDate# AND #today#
)
AS tDaysInQuestion
LEFT JOIN
email AS EE ON (tDaysInQuestion.dtSpanDay = DateValue (EE.addDate)
AND status = 1)
GROUP BY
tDaysInQuestion.dtSpanDay

[duces_wild]

is it pssable to retrieve a row from my database table querry?
i check to see if the name exists.....
if it does i get his ID, name, pass ,email from his row.......

for example u use
<cfif #QryNmae.recordset# is '1'>
<--- return the existing name with his information --->
</cfif>

[Dan Bracuk]

select yourfields,
from yourtables
where lower(name) = 'john smith'

However, name searches have this pesky little problem of two people having the same name.