getting mulitple records in query result???

[cfcProgrammer]

Hi,

I have a query that I am getting multiple records in my result however, I am only expecting one per id.. I'm unsure why this is happening as there is only one record in each table per id, I just wanted to join the tables so I could access different data from each table. Could someone please help?

Thanks so much

SELECT cn.id as 'ID',
cn.accountnr as 'Account Number',
cn.accountName as 'Account Name',
cn.totalCredit as 'Credit Amount',
cn.created_on as 'Date Created',
cn.area as 'Area',
cn.derivedClass as 'Class',
cn.status as 'Status',
cn.researcher as 'Creator',
c.logdate AS Date,
c.logstatusfrom AS Log_Status_From,
c.logstatusto AS Log_Status_TO,
c.loguserid AS User_Id,
w.forename + ' ' + w.surname AS 'User Name',
c.logcomment AS Comments
FROM
w_user w, Note cn
inner join NoteChangeLog c
ON
cn.id = C.logNoteId
WHERE (status LIKE('%Rejected%') and status <> 'Rejected Billing Research')
OR (logStatusTo LIKE '%Rejected%' and logStatusTo NOT LIKE '%Rejected Billing Research%')
AND c.loguserid = w.id
ORDER BY cn.id, Class

Thanks so much!

cfcprogrammer





cfcProgrammer

 

[bborissov]

You have 3 tables involved here.
Check every table how many IDs have.

Borislav Borissov
VFP9 SP2, SQL Server

 

[bborissov]

SELECT cn.id           as 'ID',
       cn.accountnr    as 'Account Number',
       cn.accountName  as 'Account Name',
       cn.totalCredit  as 'Credit Amount',
       cn.created_on   as 'Date Created',
       cn.area         as 'Area',
       cn.derivedClass as 'Class',
       cn.status       as 'Status',
       cn.researcher   as 'Creator',
       c.logdate       AS Date,
       c.logstatusfrom AS Log_Status_From,
       c.logstatusto   AS Log_Status_TO,
       c.loguserid     AS User_Id,
       w.forename + ' ' + w.surname AS 'User Name',
       c.logcomment    AS Comments
FROM Note cn
inner join NoteChangeLog c ON cn.id = C.logNoteId
INNER JOIN w_user w        ON c.loguserid = w.id 
WHERE (status      LIKE '%Rejected%' and status <> 'Rejected Billing Research')
   OR (logStatusTo LIKE '%Rejected%' and logStatusTo NOT LIKE '%Rejected Billing Research%')
ORDER BY cn.id, Class

Borislav Borissov
VFP9 SP2, SQL Server

 

[Olaf Doschke]

What matters is the record match count for each ID. INNER JOINS don't mean you only get one record per ID.
Besides you have an implicit join by specifying the tables w_user and Note comma separated. You have no condition connecting them by a join or where condition w.fieldX = cn.fieldY, you only indirectly join them via w.id = cn.id = c.lognoteid and w.id = c.loguserid

Borislav has made that two explicit joins. In case you have two w or two c records matching the same cn record you get two result records.

How is the hierarchy? Which table of the three is the main table and has a first record, which two of the three have details for that main table?

Bye, Olaf.

 

[cfcProgrammer]

Hi OlafDoschke,

Thank you very much for your assistance with this issue. I have some experience with SQL but it's not something I do on a regular basis so some of the functionality causes some confusion and joins are one of them.

The main table in this query is NOTE and there is only one record per id in this table, the ID field is the primary key.
The NoteChangeLog table has several records per id as this is a log table and the w_user table just contains the user information and there is only one record per user id.

I thought that by joining the tables with a common field it would allow me to access the information in the table and I am aware that that is still true however, I am still confused on how to just retrieve the records for the ID that are only for rejected notes. The join to the user table is strictly just to get the user first and last names.

I am aware that there may be more than one record per credit note as there are several points along the process that would log a record for rejection.

I have "cherry picked" some data and I believe the data may actually be right but I am still not convinced of it and even if it is... is this the most efficient way I could be writing this sql?

SELECT cn.id as 'Credit Note ID',
cn.accountnr as 'Account Number',
cn.accountName as 'Account Name',
cn.totalCredit as 'Credit Amount',
cn.created_on as 'Date Created',
cn.area as 'Area',
cn.derivedClass as 'Class',
cn.status as 'Status',
cn.researcher as 'Creator',
c.logdate AS Date,
c.logstatusfrom AS Log_Status_From,
c.logstatusto AS Log_Status_TO,
c.loguserid AS User_Id,
w.forename + ' ' + w.surname AS User_Name,
c.logcomment AS Comments
FROM
creditNote cn
inner join creditNoteChangeLog c
ON
cn.id = C.logCreditNoteId
inner join Webx.dbo.w_user w
on
c.loguserid = w.id
WHERE ((status LIKE('%Rejected%') and status <> 'Rejected Billing Research')
OR (logStatusTo LIKE '%Rejected%' and logStatusTo NOT LIKE '%Rejected Billing Research%'))
ORDER BY cn.id, log_status_to,Class, c.logDate asc



cfcProgrammer

 

[Olaf Doschke]

>The main table in this query is NOTE
OK, then Borislav has got that right intuitively.

>The NoteChangeLog table has several records per id as this is a log table and the w_user table just contains the user information
I see, the data hierarchy then is CreditNote, CreditNoteLog and User joined to have the user making the notechange.

Well, your ID "factor" then surely sinmply is the log table. Even if you limit it to a certain type of log changes with certain status texts with your LIKE clauses, you get every changelog matching that rule. What do you want instead? Just the latest note change? Or the first? That would require to pick one of the changelog records by some additional condition. That's what is missing, or any other additonal condition only picking one record. A join will always join ALL records matching the join condition. An INNER join means, if you get no match, you also don't put the creditnote data itself into the result, you're only interested in results with at least one notechange, but you get at least one and in general it can be more.

Besides that your final query has the joins correct from what you described, yes.

Bye, Olaf.

 

[cfcProgrammer]

Thank you very much Olaf for your help, your explanation is greatly appreciated and understood.

Enjoy the rest of your day!!

Cfcprogrammer

cfcProgrammer