SQL Query (need help)

Results 1 to 2 of 2

Thread: SQL Query (need help)

  1. #1
    Join Date
    Dec 1969

    Default SQL Query (need help)

    Hi,<BR><BR>I am having problem with SQL statement which should fetch me COMPANY_ID,COMP_NAME,PERS_NAME,PERS_FAM_NAME from three tables.<BR><BR>SAL_SPERS (SP_ID,SP_COMP_ID) holds the information about SALES PERSONS <BR><BR>GLB_COMP(COMP_ID,COMP_NAME,COMP_ADDR) holds COMPANY information, <BR><BR>and GLB_PERSON (PERS_ID,PERS_NAME,PERS_FAM_NAME,PERS_ADDR).<BR><B R>the DB is like this.<BR><BR>SAL_SPERS <BR><BR>SP_ID SP_COMP_ID<BR><BR>1000 100<BR>1001 100<BR>1002 200<BR>1003 &#060;null&#062;<BR>1004 300<BR><BR>GLB_COMP<BR><BR>COMP_ID COMP_NAME COMP_ADDR<BR>100 xyz #21<BR>200 pqr #34<BR>300 lmn #45<BR><BR><BR>GLB_PERSON<BR><BR>PERS_ID PERS_NAME PERS_FAM_NAME PERS_ADDR<BR>1000 michael d&#039souza ca<BR>1001 george bird ny<BR>1002 jonathan edward xy<BR>1003 morris mono mm<BR>1004 john king pp<BR><BR>how we can retrieve all records,if SP_COMP_ID is null it is not fetching all records.<BR><BR>the output should be<BR><BR>COMP_ID COMP_NAME SP_ID PERS_NAME PERS_FAM_NAME<BR>100 xyz 1000 michael D&#039souza <BR>100 xyz 1001 george bird<BR>200 pqr 1002 jonathan edward<BR>null null 1003 morris mono<BR>300 lmn 1004 john king<BR><BR><BR>can any one help me?<BR><BR>thanks<BR>

  2. #2
    Kael Guest

    Default RE: SQL Query (need help)

    I think this will give you what you want...I wasn&#039t sure what DB you was wanting to do this in, but I did this using T-SQL syntax on MS SQL Server...<BR><BR>SELECT <BR> GLB_COMP.COMP_ID, <BR> GLB_COMP.COMP_NAME, <BR> SAL_SPERS.SP_ID, <BR> GLB_PERSON.PERS_NAME, <BR> GLB_PERSON.PERS_FAM_NAME<BR>FROM <BR> GLB_PERSON INNER JOIN SAL_SPERS<BR> ON GLB_PERSON.PERS_ID = SAL_SPERS.SP_ID<BR> LEFT OUTER JOIN GLB_COMP<BR> ON SAL_SPERS.SP_COMP_ID = GLB_COMP.COMP_ID<BR><BR>As you may see...I used a LEFT OUTER JOIN linking your SAL_SPERS and GLB_COMP tables together and a normal INNER JOIN linking the SAL_SPERS and GLB_PERSON tables. The LEFT OUTER JOIN in this case means you will see all records from SAL_SPERS and only those from GLB_COMP wherever they match. The order in which you type syntax in your FROM clause will make a difference how you do the join (LEFT OUTER vs RIGHT OUTER), but the results will be the same.<BR><BR>Should you have any questions about this, feel free to shoot me an email at kael.dowdy@gepex.ge.com<BR><BR>HTH...<BR><BR>Kael

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts