Monster Query

[AV1611]

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)

[obsidian]

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.

[fenway]

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.

[AV1611]

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.

 

[AV1611]

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...

 

[fenway]

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.

[AV1611]

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.