I posted before askin to join 3 tables and ARWinner gave me a solution of this

SELECT A.ID, A.SCHEDSTARTDATE, A.DBID, B.NAME, ...12 columns all from A..., C.NAME_HOST
FROM FirstTable A INNER JOIN ON (a.state = b.ID)
LEFT OUTER JOIN ThirdTable C
ON A.DBID = C.DBID

Well it turns out I joined it incorrectly as I have to go through a parent/child links table.

so for every A.DBID there is a corresponding LINKS.PARENT_DBID that has a list of CHILD_DBIDs. And each CHILD_DBID has an associated TYPE. So when LINKS.CHILDTYPE = 16781452 then LINKS.CHILD_DBID is to be joined with table ThirdTable and when LINKS.CHILDTYPE = 16781502 its supposed to be joined with FourthTable...

I am completely lost on this.

Plus, ontop of that, I have no idea what type of join to do now...
For example, say I have one row from Table A, its DBID gives me 4 rows from Table C and 4 rows from Table D.
What I need is to have it return 8 rows
The first 4 rows have the 4 names Im grabbing from Table C and have 4 blanks in the column Im grabbing from Table D.
Then the last 4 rows have 4 blanks in the column Im pulling from Table C and have the 4 names Im grabbing from Table D.

So it would look like:
DBID | Table A's data | TableC's Name | TableD's data
1234_____blah___________App1__________________
1234_____blah___________App2__________________
1234_____blah___________App3__________________
1234_____blah___________App4__________________
1234_____blah_________________________Host1___
1234_____blah_________________________Host1___
1234_____blah_________________________Host1___
1234_____blah_________________________Host1___

Can that even be done since DBID is being used as a key??

I think it should look something like this:

select a.dbid, a.data, c.data, d.data
from
tableC c, tableD d, tableA a
left outer join links l1 on a.dbid=l1.parent_dbid
left outer join links l2 on a.dbid=l2.parent_dbid
where
l1.CHILDTYPE = 16781452
and l2.CHILDTYPE = 16781502
and l1.child_dbid=c.dbid
and l2.child_dbid=d.dbid
and coalesce(c.data, d.data) is not null;

Thats really close but its not pulling enough documents =( It gets 400something when the original got 900something.

Did this:

SELECT A.DBID, B.NAME, C.NAME_HOST, D.APPLICATIONNAME
FROM
udbucm.STATEDEF B, udbucm.HOST C, udbucm.APPLICATION D, udbucm.InfraChange A
left outer join udbucm.PARENT_CHILD_LINKS l1 on A.dbid=l1.parent_dbid
left outer join udbucm.PARENT_CHILD_LINKS l2 on A.dbid=l2.parent_dbid
where
l1.CHILD_ENTITYDEF_ID = 16781452
and l2.CHILD_ENTITYDEF_ID = 16781502
and l1.child_dbid = c.dbid
and l2.child_dbid=d.dbid
and coalesce(C.NAME_HOST, D.APPLICATIONNAME) is not null
and A.STATE = B.ID
-----------------------------

That is what pulls 400 =(

it is pulling repeat A.DBIDs (Id say there are only 50 unique A.DBIDs in the whole query when the original pulled 900+) the NAME_HOST are all correct but the majority of APPLICATIONNAME's are all blank

i really need help =(

I was using this:

SELECT DISTINCT A.ID, A.SCHEDSTARTDATE, A.TITLE, B.NAME, like 20 more cols from A, C.NAME_HOST, D.APPLICATIONNAME
FROM
udbucm.InfraChange A
INNER JOIN udbucm.StateDef B ON (A.STATE = B.ID)
left outer join udbucm.PARENT_CHILD_LINKS l1 on (A.dbid=l1.parent_dbid)
left outer join udbucm.HOST C on (l1.child_dbid = c.dbid and l1.CHILD_ENTITYDEF_ID = 16781452 )
left outer join udbucm.PARENT_CHILD_LINKS l2 on (A.dbid=l2.parent_dbid)
left outer join udbucm.APPLICATION D on (l2.child_dbid = d.dbid and l2.CHILD_ENTITYDEF_ID = 16781502 )

It was grabbing the right amount of documents. about 1500 even. I was grabbing about 1000 before. I was hoping the 500 extra were the repeat rows I wanted. but they turned out to all be unique rows =(

What am I doing wrong??