May 3, 2010 at 2:33 am
Not sure if the above subject makes any sense nor exactly how to ask my question, but here goes.
I need to list two partners with their information in each row of a view/query (vwComps_Master), the results coming out similar to the following:
select Comp1 and his info fields, select Comp2 and his info fields
Question: What is the syntax for putting two select statements on one row, or is that even possible? If not, what would be an alternative statement structure?
More detail: The query is derived from a table (Comps) which basically only has foreign key information that feeds from another main table (EQ); in other words, I want to more or less use the Comps table as a "placeholder" that uses vwComps_Master to expand from Comps with foreign key info from EQ.
The SQL statement below returns what I want for the first few columns of the row (for Comp1), but I don't know how to pick up and list the info for the second partner (Comp2).
SELECT
c.CompsID, c.DistID, q.EQID,
c.Comp1_ID, q.L AS Comp1_L, q.F AS Comp1_F, q.P1 AS Comp1_P1, q.P2 AS Comp1_P2, q.E1 AS Comp1_E1, q.E2 AS Comp1_E2, q.A AS Comp1_A,
c.Comp2_ID, q.L AS Comp2_L, q.F AS Comp2_F, q.P1 AS Comp2_P1, q.P2 AS Comp2_P2, q.E1 AS Comp2_E1, q.E2 AS Comp2_E2, q.A AS Comp2_A
FROM Comps AS c, 0_EQ AS q, 0_EQ as r
WHERE c.Comp1_ID=q.EQID and c.Comp2_ID = r.EQID
May 3, 2010 at 3:26 am
Quite possible, buddy.. but you will have to tell us atleast the description of the house; we ourselves will find out the address and the fastest way getting there.. now if that sentence confused you, please go thro the following link and post us the table schemas and other necessary code to get us started
The link : CLICK HERE FOR FORUM POSTING ETIQUETTES - JEFF MODEN[/url]
Cheers!!
May 3, 2010 at 11:54 am
reg 18234 (5/3/2010)
The SQL statement below returns what I want for the first few columns of the row (for Comp1), but I don't know how to pick up and list the info for the second partner (Comp2).SELECT
c.CompsID, c.DistID, q.EQID,
c.Comp1_ID, q.L AS Comp1_L, q.F AS Comp1_F, q.P1 AS Comp1_P1, q.P2 AS Comp1_P2, q.E1 AS Comp1_E1, q.E2 AS Comp1_E2, q.A AS Comp1_A,
c.Comp2_ID, q.L AS Comp2_L, q.F AS Comp2_F, q.P1 AS Comp2_P1, q.P2 AS Comp2_P2, q.E1 AS Comp2_E1, q.E2 AS Comp2_E2, q.A AS Comp2_A
FROM Comps AS c, 0_EQ AS q, 0_EQ as r
WHERE c.Comp1_ID=q.EQID and c.Comp2_ID = r.EQID
Appears to me that you're close, but your second list of fields (supposedly from Comp2) is still referencing the row from 0_EQ that you've aliased as 'q' and not as 'r'. So, for example, 'q.L AS Comp1_L' will give you the same data as 'q.L AS Comp2_L'. It should be 'r.L AS Comp2_L'.
It looks like you're also using an old ANSI-89 join syntax. I'd rewrite the last part as:
FROM Comps AS c,
JOIN 0_EQ q on c.Comp1_ID = q.EQID
JOIN 0_EQ r on c.Comp2_ID = r.EQID
You'll get the same results, but the latter is the newer ANSI 92 standard.
Rob Schripsema
Propack, Inc.
May 3, 2010 at 3:09 pm
Rob Schripsema (5/3/2010)
reg 18234 (5/3/2010)
The SQL statement below returns what I want for the first few columns of the row (for Comp1), but I don't know how to pick up and list the info for the second partner (Comp2).SELECT
c.CompsID, c.DistID, q.EQID,
c.Comp1_ID, q.L AS Comp1_L, q.F AS Comp1_F, q.P1 AS Comp1_P1, q.P2 AS Comp1_P2, q.E1 AS Comp1_E1, q.E2 AS Comp1_E2, q.A AS Comp1_A,
c.Comp2_ID, q.L AS Comp2_L, q.F AS Comp2_F, q.P1 AS Comp2_P1, q.P2 AS Comp2_P2, q.E1 AS Comp2_E1, q.E2 AS Comp2_E2, q.A AS Comp2_A
FROM Comps AS c, 0_EQ AS q, 0_EQ as r
WHERE c.Comp1_ID=q.EQID and c.Comp2_ID = r.EQID
Appears to me that you're close, but your second list of fields (supposedly from Comp2) is still referencing the row from 0_EQ that you've aliased as 'q' and not as 'r'. So, for example, 'q.L AS Comp1_L' will give you the same data as 'q.L AS Comp2_L'. It should be 'r.L AS Comp2_L'.
It looks like you're also using an old ANSI-89 join syntax. I'd rewrite the last part as:
FROM Comps AS c,JOIN 0_EQ q on c.Comp1_ID = q.EQID
JOIN 0_EQ r on c.Comp2_ID = r.EQID
You'll get the same results, but the latter is the newer ANSI 92 standard.
Thanks, Rob, for taking the time to help. You're exactly right--both references display the same person. I only wrote in the incorrect stuff because I didn't have anything else, and I do need to change it but don't know how. What other information should I supply?
And thanks for the ANSI-92 standard. Unfortunately, the statement returned "Syntax error in FROM clause." What else might we need to do to it? I'm actually doing this under Access 2003, so don't know if that makes a difference or not.
May 3, 2010 at 3:12 pm
ColdCoffee (5/3/2010)
Quite possible, buddy.. but you will have to tell us atleast the description of the house; we ourselves will find out the address and the fastest way getting there.. now if that sentence confused you, please go thro the following link and post us the table schemas and other necessary code to get us startedThe link : CLICK HERE FOR FORUM POSTING ETIQUETTES - JEFF MODEN[/url]
Cheers!!
Hmm, should I trust this Jeff guy? Sounds like he wants the addresses of my friends...:w00t: OK, I'll take my chances and post the schemas, etc. there.
May 3, 2010 at 3:20 pm
reg 18234 (5/3/2010)
ColdCoffee (5/3/2010)
Quite possible, buddy.. but you will have to tell us atleast the description of the house; we ourselves will find out the address and the fastest way getting there.. now if that sentence confused you, please go thro the following link and post us the table schemas and other necessary code to get us startedThe link : CLICK HERE FOR FORUM POSTING ETIQUETTES - JEFF MODEN[/url]
Cheers!!
Hmm, should I trust this Jeff guy? Sounds like he wants the addresses of my friends...:w00t: OK, I'll take my chances and post the schemas, etc. there.
Heh... "this Jeff guy". He's alright for a newbie. 😛 And I wouldn't worry about him collecting addresses of your friends... he probably already has them. :hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
May 3, 2010 at 3:49 pm
reg 18234 (5/3/2010)
Rob Schripsema (5/3/2010)
reg 18234 (5/3/2010)
The SQL statement below returns what I want for the first few columns of the row (for Comp1), but I don't know how to pick up and list the info for the second partner (Comp2).SELECT
c.CompsID, c.DistID, q.EQID,
c.Comp1_ID, q.L AS Comp1_L, q.F AS Comp1_F, q.P1 AS Comp1_P1, q.P2 AS Comp1_P2, q.E1 AS Comp1_E1, q.E2 AS Comp1_E2, q.A AS Comp1_A,
c.Comp2_ID, q.L AS Comp2_L, q.F AS Comp2_F, q.P1 AS Comp2_P1, q.P2 AS Comp2_P2, q.E1 AS Comp2_E1, q.E2 AS Comp2_E2, q.A AS Comp2_A
FROM Comps AS c, 0_EQ AS q, 0_EQ as r
WHERE c.Comp1_ID=q.EQID and c.Comp2_ID = r.EQID
Appears to me that you're close, but your second list of fields (supposedly from Comp2) is still referencing the row from 0_EQ that you've aliased as 'q' and not as 'r'. So, for example, 'q.L AS Comp1_L' will give you the same data as 'q.L AS Comp2_L'. It should be 'r.L AS Comp2_L'.
It looks like you're also using an old ANSI-89 join syntax. I'd rewrite the last part as:
FROM Comps AS c,JOIN 0_EQ q on c.Comp1_ID = q.EQID
JOIN 0_EQ r on c.Comp2_ID = r.EQID
You'll get the same results, but the latter is the newer ANSI 92 standard.
Thanks, Rob, for taking the time to help. You're exactly right--both references display the same person. I only wrote in the incorrect stuff because I didn't have anything else, and I do need to change it but don't know how. What other information should I supply?
And thanks for the ANSI-92 standard. Unfortunately, the statement returned "Syntax error in FROM clause." What else might we need to do to it? I'm actually doing this under Access 2003, so don't know if that makes a difference or not.
My bad on the code sample -- had a spurious comma in it.
Here's more or less how your query ought to look:
SELECT
c.CompsID, c.DistID, q.EQID,
c.Comp1_ID, q.L AS Comp1_L, q.F AS Comp1_F, q.P1 AS Comp1_P1, q.P2 AS Comp1_P2, q.E1 AS Comp1_E1, q.E2 AS Comp1_E2, q.A AS Comp1_A,
c.Comp2_ID, r.L AS Comp2_L, r.F AS Comp2_F, r.P1 AS Comp2_P1, r.P2 AS Comp2_P2, r.E1 AS Comp2_E1, r.E2 AS Comp2_E2, r.A AS Comp2_A
FROM Comps AS c -- note, no comma
JOIN 0_EQ q on c.Comp1_ID = q.EQID
JOIN 0_EQ r on c.Comp2_ID = r.EQID
Rob Schripsema
Propack, Inc.
May 3, 2010 at 4:18 pm
Rob, I receive the same error message without the comma.
May 3, 2010 at 4:27 pm
Jeff, I read your "Emily Post" etiquette guide on how to properly post schemas, etc. Seriously, it all looks good but I'm doing these tables in Access 2003 (should I be writing to a different forum site?), which apparently doesn't have scripting mechanisms like SQL Server. I saw mention of XML export methods for Access in various fora but I hesitate to do so since I don't think Emily would approve. Am I right? Is there a way to script an Access table, similar to the utilities offered in SQL Server?
May 3, 2010 at 4:31 pm
I'm not up on Access 2003, so it may be that it is not ANSI-92 compliant. Go back to using the FROM clause you initially supplied (without the JOIN...ON syntax) and see if that solves the problem.
Rob Schripsema
Propack, Inc.
May 3, 2010 at 8:21 pm
reg 18234 (5/3/2010)
I'm doing these tables in Access 2003 which apparently doesn't have scripting mechanisms like SQL Server.
Reg, for Access 2003, you will have to use the brackets to enclose the table names and join clauses.
something like:
FROM ( table1 A join table2 B ) ON (A.ID = B.ID)
This way u can stop that annoying "Incorrect Syntax" msg..
Side Note:I have seen my colleague doing this in Access 2007 and i hope this method holds good for 2003 as well
should I be writing to a different forum site?
Not any other forum site, we do have a sub-topic exclusively for Access here at SSC.com. Post your Access related question at the following link"
Hope this helps!
Cheers!!
May 4, 2010 at 11:55 pm
Hmm, unfortunately the above query statement gives me syntax errors also.
I'll close this thread and post to the Access forum.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply