Complicated join question.

Hi there.

I'm running Microsoft Business Solutions GP 8 backed by SQL Server 2000. I need to write a little C#/.NET 2.0 app to go "behind the scenes" to the SQL server in order to fetch and alter some data.

Unfortunately, the tables that GP 8 has created seem strange to me; I've encountered an issue with a SELECT clause featuring two INNER JOINs.

I need to select columns from three different tables. Unfortunately, the key structure of these tables is strange at best. You may view the structure of the three tables from the following URL.

http://pastebin.com/732226

My SQL query is as follows.

SELECT IV30400.DOCNUMBR, IV30300.DOCDATE, IV30200.GLPOSTDT, IV30400.SERLTNUM, IV30400.ITEMNMBR, IV30400.SERLTQTY

FROM IV30400

INNER JOIN IV30300 ON IV30300.DOCNUMBR = IV30400.DOCNUMBR

INNER JOIN IV30200 ON IV30200.DOCNUMBR = IV30300.DOCNUMBR

The key I am joining on is DOCNUMBR. The end result is that I get multiple copies of the same joint row in my result set. I only want one copy of each joint row.

Curiously, the DOCNUMBR key column does not contain unique values. In several rows (in each of the tables, mind you), the value in the DOCNUMBR column is identical. I don't know of this is the cause of my problem.

If anyone can help me sort out this thorny problem I would greatly appreciate it.

Thank you,

--JT

Curiously, the DOCNUMBR key column does not contain unique values. In several rows (in each of the tables, mind you), the value in the DOCNUMBR column is identical. I don't know of this is the cause of my problem.

I have seen programs like this and it is horrifying at best to work with the data (and this is their goal by making the table names so darn user friendly. I couldn't open your pictures, so you might just want to script them out.

Are there any queries in the program that give you somehting close to what you want? I used canned reports from the program I was working with to figure out the really hairy schema using Profiler (the greatest tool in the SQL Server toolbox.

Also, look to see if there is any table with docnumbr unique, of see if there is some other uniqueness criteria you can use.

|||

GROUP BY?

SELECT IV30400.DOCNUMBR, IV30300.DOCDATE, IV30200.GLPOSTDT, IV30400.SERLTNUM, IV30400.ITEMNMBR, IV30400.SERLTQTY

FROM IV30400

INNER JOIN IV30300 ON IV30300.DOCNUMBR = IV30400.DOCNUMBR

INNER JOIN IV30200 ON IV30200.DOCNUMBR = IV30300.DOCNUMBR

Group By IV30400.DOCNUMBR, IV30300.DOCDATE, IV30200.GLPOSTDT, IV30400.SERLTNUM, IV30400.ITEMNMBR, IV30400.SERLTQTY

Does that help at all?

Adamus

|||

Hi Adamus,

That indeed removed the duplicate result rows. I'm not sure WHY it works: I'm guessing that it's because the GROUP BY clause doesn't contain any sorting criteria, and thus it drops the duplicate rows altogether. Thank you for your suggestion!

Now I wonder why the tables are organized in this fashion. It almost seems counter-productive. Oh well.

Thanks again,

--JT

|||

Sounds like sloppy table design. Must've been crunched for time.

Adamus