Jump to content

Monster Query


AV1611

Recommended Posts

table1 = fields a-z

table2 = fields a-z

table3 = fields a-z

 

I need do do this:

 

query part 1:

select fields a-f of table1

 

query part 2:

select fields g,h,I from table2

and add them to query1.

There are multiple rows in the second table, I need to bring all where t1.a=t2.a and t1.b=t2.b

 

(Now I have increased the total rows in table1)

 

query part 3:

 

Now I need to add to table1 fields L,M,N from table 3 where t1.a=t3.a

 

*************************

 

FYI,

What I am doing is taking a top level assy, and pulling in all the subassy's for it (BOM Explosion), then adding some data to that from a third table. I know how to get the answer in multiple steps, but I need to understand how to do it in one step with possibly subqueries, and may even be forces by my boss to write a Crystal Report to do the same, if he won't buy off on my script (He thinks Crystal is the answer to all the worlds problems)

Link to comment
Share on other sites

well, if i understand you right, something like this should do the trick for you:

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] a.a, a.b, a.c, a.d, a.e, a.f, b.g, b.h, b.i, c.l, c.m, c.n FROM table1 a, table2 b, table3 c WHERE a.a = b.a AND a.b = b.b AND a.a = c.a;

[!--sql2--][/div][!--sql3--]

 

now, that's only if i understand the requirements correctly. hope this helps.

Link to comment
Share on other sites

You had to use a, b, and c as your table alises, didn't you? Just to make it even more cryptic that the already-short column names. Yikes -- if somewhen ever showed me that query, I think I would scream at the top of my lungs.

Link to comment
Share on other sites

So sorry...

 

I will post the real thing when I get to work in a couple of hours...

 

 

[!--quoteo(post=333619:date=Jan 5 2006, 02:05 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 5 2006, 02:05 PM) 333619[/snapback][/div][div class=\'quotemain\'][!--quotec--]

You had to use a, b, and c as your table alises, didn't you? Just to make it even more cryptic that the already-short column names. Yikes -- if somewhen ever showed me that query, I think I would scream at the top of my lungs.

 

Link to comment
Share on other sites

Sorry to all, I did a poor job of asking that question... Here is the QUERY I ran that worked...

 

SELECT
    Part_Master."PRTNUM_01", Part_Master."TYPE_01", Part_Master."COMCDE_01", Part_Master."PMDES1_01", Part_Master."BOMUOM_01", Part_Master."STAENG_01", Part_Master."FRMPLN_01", Part_Master."DELSTK_01", Part_Master."ORDPOL_01", Part_Master."MINQTY_01", Part_Master."MULQTY_01", Part_Master."MFGPIC_01", Part_Master."MFGOPR_01", Part_Master."MFGSTK_01", Part_Master."PURPIC_01", Part_Master."PUROPR_01", Part_Master."PURSTK_01", Part_Master."COST_01", Part_Master."CSTUOM_01", Part_Master."CSTCNV_01", Part_Master."MATL_01", Part_Master."BUYER_01", Part_Master."SERTRK_01", Part_Master."RTEDTE_01",
    Product_Structure."COMPRT_02", Product_Structure."EFFDTE_02", Product_Structure."QTYPER_02",
    Part_Master_1."TYPE_01", Part_Master_1."PLANID_01", Part_Master_1."COMCDE_01", Part_Master_1."PMDES1_01", Part_Master_1."BOMUOM_01", Part_Master_1."STAENG_01", Part_Master_1."FRMPLN_01", Part_Master_1."DELSTK_01", Part_Master_1."ORDPOL_01", Part_Master_1."MINQTY_01", Part_Master_1."MULQTY_01", Part_Master_1."MFGPIC_01", Part_Master_1."MFGOPR_01", Part_Master_1."MFGSTK_01", Part_Master_1."PURPIC_01", Part_Master_1."PUROPR_01", Part_Master_1."PURSTK_01", Part_Master_1."CSTTYP_01", Part_Master_1."CSTUOM_01", Part_Master_1."CSTCNV_01", Part_Master_1."MATL_01", Part_Master_1."BUYER_01", Part_Master_1."RECVEN_01",
    Part_Vendor."COST1_07", Part_Vendor."BREAK1_07"
FROM
    "MAXDAT"."Part Master" Part_Master INNER JOIN "MAXDAT"."Product Structure" Product_Structure ON
        Part_Master."PRTNUM_01" = Product_Structure."PARPRT_02" INNER JOIN "MAXDAT"."Part Vendor" Part_Vendor ON
        Product_Structure."COMPRT_02" = Part_Vendor."PRTNUM_07" INNER JOIN "MAXDAT"."Part Master" Part_Master_1 ON
        Product_Structure."COMPRT_02" = Part_Master_1."PRTNUM_01"
WHERE
    (Part_Master."TYPE_01" = 'S' OR
    Part_Master."TYPE_01" = 'P' OR
    Part_Master."TYPE_01" = 'M' OR
    Part_Master."TYPE_01" = 'A') AND
    Part_Master."STAENG_01" < '5' AND
    Product_Structure."QTYPER_02" > 0

 

 

 

 

[!--quoteo(post=333927:date=Jan 6 2006, 06:56 AM:name=AV1611)--][div class=\'quotetop\']QUOTE(AV1611 @ Jan 6 2006, 06:56 AM) 333927[/snapback][/div][div class=\'quotemain\'][!--quotec--]

So sorry...

 

I will post the real thing when I get to work in a couple of hours...

 

Link to comment
Share on other sites

First, my comment about the table aliases was directed at obsidian; but given the final query you've posted, I would recommend that you use even shorter table aliases as well -- it keeps the code tighter, and gives the parser less work to do. Second, what's with all the quotes? And why do you have column names with spaces?!? Third, you could use in a IN clause in the WHERE clause, which also improves legibility. Just a few suggestions.

Link to comment
Share on other sites

I'm pulling from Pervasive, not MySQL on this one... that's why the " instead of `

 

[!--quoteo(post=334078:date=Jan 6 2006, 01:49 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 6 2006, 01:49 PM) 334078[/snapback][/div][div class=\'quotemain\'][!--quotec--]

First, my comment about the table aliases was directed at obsidian; but given the final query you've posted, I would recommend that you use even shorter table aliases as well -- it keeps the code tighter, and gives the parser less work to do. Second, what's with all the quotes? And why do you have column names with spaces?!? Third, you could use in a IN clause in the WHERE clause, which also improves legibility. Just a few suggestions.

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.