4

This is Datagridview which I need to display on the screen like these.
(Datagrid view is binding with the "main table" (RECEIVE_PLAN))

enter image description here

Concept Design database:

if you want to import something into warehouse, you must plan for receiving in a day. Receiving Plan is made from "Purchase Order" directly Or sometime your warehouse has a receiving schedule before ordering.

Database has three tables, including Table A and Table B and Main Table.

They have a relation like these.

Note: the main table has two options for getting value for display to the screen

  1. get "PO_LIST_NO" and "PO_NO" from A table(PURCHASE ORDER table) directly.

  2. get "PO_LIST_NO" and "PO_NO" from B table(RECEIVE SCHEDULE table) then get value from A table.
    enter image description here

Important Conditions

  1. In main table (RECEIVE PLAN) must have a value in either "PO_ID" or "RS_ID"

  2. If main table (RECEIVE PLAN) has a value at PO_ID column, RS_ID column must be NULL. On the other hand, If main table (RECEIVE PLAN) has a value at RS_ID column, PO_ID column must be NULL

  3. Main Table (RECEIVE PLAN) must not NULL both PO_ID and RS_ID

  4. Main Table (RECEIVE PLAN) must not has a value at both PO_ID and RS_ID

RECEIVE PLAN's example like below.

enter image description here

(PO_TRAN_ID) is PO_ID

(RS_TRAN_ID) is RS_ID in this case.

QUESTION :How to query for getting value from either Table A or B?

How to join between a main table,A and B table for display like these.

enter image description here

This datagridview Properties.

BindingSource : main table (RECEIVE_PLAN)

"PO LIST NO" column : getting from A table (PURCHASE_ORDER)

"PO NO" column : getting from A table (PURCHASE_ORDER)

"PLAN QTY" column : getting from main table (RECEIVE PLAN)

Community
  • 1
  • 1
Fame th
  • 1,018
  • 3
  • 17
  • 37
  • 1
    I appreciate the visual diagrams and effort, but I think your question would be more clear with just sample inputs and outputs in plain text. – Tim Biegeleisen Sep 09 '15 at 04:17
  • http://weblogs.sqlteam.com/jeffs/archive/2007/04/03/Conditional-Joins.aspx – Praveen Prasannan Sep 09 '15 at 04:22
  • @ThanapipatPacharatanakoson, please use plain text instead of images. Better yet, post a create table script and insert statements. – Felix Pamittan Sep 09 '15 at 04:27
  • +1 from me. I actually liked the images very much. It helps a lot to visualize things. Of course it's complementary to the text description (in order to copy and paste) – Alkis Kalogeris Sep 09 '15 at 04:33

2 Answers2

1

Something like this?

SELECT A_TABLE.PO_LIST_NO, A_TABLE.PO_NO, SUM(MAIN_TABLE.PLAN_QTY) FROM A_TABLE
INNER JOIN B_TABLE ON A_TABLE.PO_ID = B_TABLE.PO_ID
INNER JOIN MAIN_TABLE ON MAIN_TABLE.PO_ID = B_TABLE.PO_ID OR MAIN_TABLE.RS_ID = B_TABLE.RS_ID
WHERE (MAIN_TABLE.RS_ID IS NOT NULL OR MAIN_TABLE.PO_ID IS NOT NULL) AND NOT (MAIN_TABLE.RS_ID IS NOT NULL AND MAIN_TABLE.PO_ID IS NOT NULL) 
GROUP BY A_TABLE.PO_LIST_NO, A_TABLE.PO_NO
Alkis Kalogeris
  • 17,044
  • 15
  • 59
  • 113
1

I can solved it by using this query.

( SELECT   MAIN_TABLE.*,   A_TABLE.PO_LIST_NO, A_TABLE.PO_NO
  FROM          MAIN_TABLE  
                LEFT OUTER JOIN A_TABLE
                ON MAIN_TABLE.PO_ID = A_TABLE.TRAN_ID
  WHERE  (MAIN_TABLE.RS_ID IS NULL) 
)  

  UNION 


( SELECT     MAIN_TABLE.*   , A_TABLE.PO_LIST_NO , A_TABLE.PO_NO
  FROM          MAIN_TABLE  
                LEFT OUTER JOIN B_TABLE 
                ON MAIN_TABLE.RS_ID = B_TABLE .TRAN_ID
                             LEFT OUTER JOIN A_TABLE  
                             ON B_TABLE .PO_ID = A_TABLE.TRAN_ID
  WHERE      (MAIN_TABLE.PO_ID IS NULL)  
 )
Fame th
  • 1,018
  • 3
  • 17
  • 37