Select Query

[vijay2482]

I'm working on a vb.net application, where I use sql queries to get the datas from a dbase file.

There are cases where there can be 2 similar rows with the PARENT and ENFANT fields same in E_Ensart table, but the other columns may ahve different datas.

I use the below query to retrive datas from the database:

 Private Function GetSQLExactMatch(ByVal name As String, ByVal artTbl As String) As String
        Dim sb As StringBuilder = New StringBuilder("")
        sb.Append(GetSQLSelectColumns())
        sb.Append(" from E_Ensemb e,  ")
        sb.Append("  E_Ensart a ")
        sb.Append(" where a.PARENT = '" & name & "' ")
        sb.Append(" and e.ITEM_NBR = a.ENFANT ")
        sb.Append("   and a.VALIDITE = 'O'")
        Return sb.ToString()
    End Function

When I use the above query, I get only one line from E_Ensart table instead of 2 lines.
I think its because I assign the e.ITEM_NBR =a.ENFANT in my query.
But even if I remove this line, I get only one row picked instead of 2 rows and I also find that the query is running indefinitly and the same line is repeated n number of times...

Kindly help me to find out where I'm wrong.
Hoping for suggestions.
Thanks in advance.

[JuggaloBrotha]

What happens when you put the output of sb.ToString() into the clipboard and you run the query directly in the database? Do you still only get 1 record or do you get both? Does the query even look right?

[vijay2482]

Thanks for your reply.
I get only 1 line.
I think the query is wrong as I said before.

I think its because I assign the e.ITEM_NBR =a.ENFANT in my query.
But even if I remove this line, I get only one row picked instead of 2 rows and I also find that the query is running indefinitly and the same line is repeated n number of times...

Thanks in advance for any help.

[JuggaloBrotha]

You do realize that other than the questions I've already asked, we have no idea how to help you unless you post the query & an example of the data, right?

[vijay2482]

I'm out of office now.
I don't have the data now.
Will get back to you tomorrow with the details.
Please do have a look at my post tomorrow.

Thanks.

[vijay2482]

Data from my table:

PARENT	          ENFANT	SECT_GRP	NPR_DC_NBR	CD_ST	UTIL_IND	IND_REP	BALOON	DNF	DATE_REEL	ADD_SUP	UTIL_QTE	DC_ANNU	DATE_ANNU	VALIDITE	FLAG_ALT	CODE_MODIF
4353742M92	3778094M1	AA	F28900051	E	D	N			2/24/2011	A	008			O
4353742M92	4298199M1	AA	F28900051	E	D	N			2/24/2011	A	002			O
4353742M92	4348342M1	AA	F28900051	E	D	N			2/24/2011	A	002			O
4353742M92	4348661M2	AA	F28900051	E	D	N			2/24/2011	A	002			O
4353742M92	4349709M1	AA	F28900051	E	D	N			2/24/2011	A	002			O
4353742M92	4353749M1	AA	F28900051	E	D	N			2/24/2011	A	002			O
4353742M92	4353745M2	AA	F28900051	E	D	N			2/24/2011	A	002			O
4353742M92	4356943M1	A	F28900051	E	D	N	A	4353742M92	2/24/2011	A	001			O
4353742M92	4353745M2	B	F28900051	E	D	N	B	4353742M92	2/24/2011	A	001			O

The lines marked in red has to appear 2 times in my output, whereas, I get only one line(the 1st occurance of the line).

Thanks for any suggestion.

[leinad31]

Try querying the database directly for each table, then both tables using a DB tool to check that your query is logically correct before trying to implement it at your front-end.

If parent table only has one relevant row, and child has only one rows with VALIDITE = 'O' then it is expected that you will get only one row in result of join e.ITEM_NBR = a.ENFANT.

[vijay2482]

Thanks for the reply.
To get all the rows where a.PARENT='" & name & "' ( as value passed)
what should be done?

sb.Append(" where a.PARENT = '" & name & "' ")

Thanks

[leinad31]

if you want to retain all child records then do a left join on e.ITEM_NBR = a.ENFANT and remove validite condition.

[vijay2482]

Thanks for the suggestion.
I did a left join as follows:

sb.Append(" from E_Ensemb e ")
sb.Append(" left join E_Ensart a on a.ENFANT = e.ITEM_NBR  ")
sb.Append("   where a.PARENT = '" & name & "'")

but still I get only one line instead of 2.

Please help.

[leinad31]

Try running your query dirctly on the database. Check if the result is correct. It's possible that query is correct but front end control property or code is filtering out the other rows.

Divide and conquer.... eliminate possibilities.

[vijay2482]

yes, I ran the query directly on the database and I get 9 rows where 2 rows has the same value in the ea.ENFANT.
When I do the same query in vb.net, I get 8 rows and only 1 row instead of 2 rows where there is same value in the ea.ENFANT column.

How to handle it with the vb.net front end.

query used directly on access tables:

SELECT ea.PARENT, ea.ENFANT, ea.BALOON, ea.DNF, ea.UTIL_QTE, e.DES_F, e.DES_AD_F, e.DES_A, e.DES_AD_A
FROM E_Ensart1 AS ea, E_Ensemb1 AS e
WHERE ea.ENFANT=e.ITEM_NBR and ea.PARENT='4353742M92';

Any help will be helpful.
working on this for the past 4 days with no result...

Thanks in advance.

[leinad31]

If the query is correct in the database then the problem is at your front-end. Check the properties of your controls; maybe it is set to display distinct rows only. Check your triggered events; they might be rewriting the query. Related to dynamic rewrite, check text of query just before execution in the database (dump it so you can view the query), don't base exclusively on SQL you are building elsewhere in code. If you are not using data bound controls then check your algorithm for transferring info fr0om recordset to display grid. If you are using a non-standard control then it might have bugs. etc, etc

AGAIN if the query returned correct resultset when ran directly against the database then the problem is NOT the query. It is something else at your front-end.