Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

help with joins Expand / Collapse
Author
Message
Posted Thursday, August 1, 2013 2:37 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 5:04 AM
Points: 79, Visits: 207
i have 2 table with 1 to many relation


table1
hid ,refid cid
500 4 1200
501 4 1200


table2

id description refid cid type
500 de 0 1200 H
501 de 0 1200 H
502 desc1 4 1200 D
503 desc2 4 1200 D
504 desc5 4 1200 D
506 desc8 4 1200 D

1001 desc120 3 1200 D
1002 desc121 3 1200 D

and so on

i have a query with table2 joined with some other tables for which i get the output as

id description refid cid type
502 desc1 4 1200 D
503 desc2 4 1200 D
504 desc5 4 1200 D
506 desc8 4 1200 D


i need to join table1 such that

i get the output as





id description
500 de
501 de
502 desc1
503 desc2
504 desc5
506 desc8

i have the below
select ID, tenderdesc from table2 where cid=1200
and refid in(select refid from table1 where cid=1200 )
or
id in (select hid from table1 where cid=1200)
when executed individually i get the result i need
but when joined with the existing query i dont get the needed output
Post #1479830
Posted Thursday, August 1, 2013 3:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 13,562, Visits: 11,372
You probably need a full outer join.

Also check the first link in my signature on how to ask questions on forums.
It will make life easier for people trying to help you.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1479837
Posted Thursday, August 1, 2013 3:40 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 5:04 AM
Points: 79, Visits: 207
i tried with full outer join but not getting the result
Post #1479845
Posted Thursday, August 1, 2013 3:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 13,562, Visits: 11,372
Can you post the query you tried?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1479846
Posted Thursday, August 1, 2013 3:57 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 5:04 AM
Points: 79, Visits: 207

SELECT
''As RefNo,
'' AS ERPItmDesc,
'' as TDesc,
'' as Item Description,
'' as Date,
0 as ID

FROM TBLDETAIL FM INNER JOIN TBLHDRDETAIL MD
ON MD.CID=FM.CID AND MD.ID=FM.MRPID LEFT OUTER JOIN TBLHDR MH
ON MD.BRNID=MH.BRNID AND MD.CID=MH.CID AND MD.HDRID=MH.ID AND MH.ITMID=FM.ITEMID
FULL OUTER JOIN TBLDESCRIPTION st ON mh.id=st.HID
WHERE
FM.CID=1200 AND FM.YMONTH=201207 AND FM.MONTHSECTION=1


UNION
(SELECT

TENDERREFNO as RefNo,
I.ITNAME AS ERPItmDesc,
MH.TDESC as TDesc,
MH.Item Description,
FM.Date,
MH.ID
FROM TBLDETAIL FM INNER JOIN TBLHDRDETAIL MD
ON MD.CID=FM.CID AND MD.ID=FM.MRPID LEFT OUTER JOIN TBLHDR MH
ON MD.CID=MH.CID AND MD.HDRID=MH.ID AND MH.ITMID=FM.ITEMID
LEFT OUTER JOIN STITMMAS I
ON MH.ITMID=I.ITMID LEFT OUTER JOIN TBLMAS U
ON I.ITMUNITID=U.UNITID
FULL OUTER JOIN TBLDESCRIPTION st ON mh.id=st.HID
WHERE FM.CID=1200 AND FM.YMONTH=201207 AND FM.MONTHSECTION=1
)



this is the query i am having currently

need to modify it to get the result
SELECT mh.ID, mh.tdesc
FROM TBLHDR mh
FULL OUTER JOIN TBLDESCRIPTION st ON mh.id=st.HID
where mh.cid=1200
Post #1479848
Posted Thursday, August 1, 2013 4:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 13,562, Visits: 11,372
Regarding the desired output that you gave in your original question:
you can get that from the second table only, so why would you join at all?




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1479870
Posted Friday, August 9, 2013 5:22 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, November 24, 2014 4:58 PM
Points: 522, Visits: 241
Hi,

What are you trying to achieve?

Do you need only records from Table 2 those exist in Table 1?

Please clarify.
Post #1482980
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse