Jump to content


Photo

Monster Query


  • Please log in to reply
6 replies to this topic

#1 AV1611

AV1611
  • Members
  • PipPipPip
  • Advanced Member
  • 997 posts

Posted 05 January 2006 - 04:27 PM

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)

#2 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 05 January 2006 - 05:42 PM

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.
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 05 January 2006 - 07:05 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 AV1611

AV1611
  • Members
  • PipPipPip
  • Advanced Member
  • 997 posts

Posted 06 January 2006 - 11:56 AM

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) View Post[/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.
[/quote]


#5 AV1611

AV1611
  • Members
  • PipPipPip
  • Advanced Member
  • 997 posts

Posted 06 January 2006 - 04:08 PM

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) View Post[/div][div class=\'quotemain\'][!--quotec--]
So sorry...

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


#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 06 January 2006 - 06:49 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#7 AV1611

AV1611
  • Members
  • PipPipPip
  • Advanced Member
  • 997 posts

Posted 06 January 2006 - 10:37 PM

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) View Post[/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.
[/quote]





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users