a most tricky select statement query

[DOGWATCH]

I've been pulling my hair out to figure this query out.
My output is sorted by LNAME, it also must show all the
8 row numbers from TABLEA even though only 6 rows exist in TABLEB. BTW I'm writing this query for access but it should work in SQL just the same. No stored proc or fancy stuff I need ONE query that would select all the data from both tables. There must be 4 rows total. The expected output in one table as shown below. PLEASE HELP!


Here is the data:

TABLE1
row_a row_b
----- -----
1 5
2 6
3 7
4 8

TABLE2
FNAME LNAME GRADE
----- ----- -----
JOE AMES C
JEFF FRANKSON B
BILL BARTSON A
TIM CARRY A
JOHN DOE B
JANE ZIMMER C

HERE EXPECTED OUTPUT:

ROW_A NAME_A GRADE_A ROW_B NAME_B GRADE_B
----- ------ ----- ------ -------- ------
1 AMES,JOE C 5 FRANKSON,JEFF B
2 BARTSON,BILL A 6 ZIMMER, JANE C
3 CARRY, TIM A 7
4 DOE,JOHN B 8

 

[donutman]

Usually there has to be a field in common between two tables in order to link them to one another. Unless you haven't shown the entire structure of the table, there is a serious flaw in your table design. However, it looks to me that you just created Table1 by hand in order to attempt to get two columns of names when you sort by last name as you have shown in your expected output. Is that right? If not, then what does the information in Table1 tell us?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[DOGWATCH]

your right, TABLE1 is quite pointless as there really isn't a relationship between the two tables. but I just really need the query to show row numbers and there has two be two colums of names and wasn' sure how to do it any other other way. If the expected output can be made from TABLE2 only thats even better.

 

[zhavic]

This may something:

SELECT ( SELECT COUNT(*) FROM Table2 AS cnt WHERE cnt.LNAME <= Table2.LNAME ) AS ROW_A,
		RTRIM( Table2.LNAME ) + ',' + Table2.FNAME AS NAME_A,
		Table2.GRADE AS GRADE_A,
		( SELECT COUNT(*) FROM Table2 AS cnt WHERE cnt.LNAME <= Table2.LNAME ) + 4 AS ROW_B,
		RTRIM( Values_B.LNAME ) + ',' + Values_B.FNAME AS NAME_B,
		Values_B.GRADE AS GRADE_B
	FROM table2
		LEFT JOIN ( SELECT FNAME, LNAME, GRADE,
							( SELECT COUNT(*) FROM Table2 AS cnt WHERE cnt.LNAME <= Table2.LNAME ) AS Row_number
						FROM Table2
						WHERE ( SELECT COUNT(*) FROM Table2 AS cnt WHERE cnt.LNAME <= Table2.LNAME ) > 4
				  ) AS Values_B ON Values_B.Row_number = ( SELECT COUNT(*) FROM Table2 AS cnt WHERE cnt.LNAME <= Table2.LNAME ) + 4
	WHERE ( SELECT COUNT(*) FROM Table2 AS cnt WHERE cnt.LNAME <= Table2.LNAME ) < 5

Zhavic


---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[zhavic]

PS: it uses only Table2

Zhavic


---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[DOGWATCH]

sounds almost right but I am getting a syntax error on this section it says:


Values_B.Row_number = ( SELECT COUNT(*) FROM Table2 AS cnt WHERE cnt.LNAME <= Table2.LNAME ) + 4
WHERE ( SELECT COUNT(*) FROM Table2 AS cnt WHERE cnt.LNAME <= Table2.LNAME ) < 5

so the first half of the table the syntax seems correct.
Any ideas?

 

[zhavic]

hmmm, it's not giving any errors to me
I am using this:

CREATE TABLE [table2] (
	[FNAME] [char] (8),
	[LNAME] [char] (10),
	[GRADE] [char] (1)
)

INSERT INTO Table2 VALUES( 'JOE'  ,  'AMES'    , 'C' )
INSERT INTO Table2 VALUES( 'JEFF' ,  'FRANKSON', 'B' )
INSERT INTO Table2 VALUES( 'BILL' ,  'BARTSON' , 'A' )
INSERT INTO Table2 VALUES( 'TIM'  ,  'CARRY'   , 'A' )
INSERT INTO Table2 VALUES( 'JOHN' ,  'DOE'     , 'B' )
INSERT INTO Table2 VALUES( 'JANE' ,  'ZIMMER'  , 'C' )


SELECT ( SELECT COUNT(*) FROM Table2 AS cnt WHERE cnt.LNAME <= Table2.LNAME ) AS ROW_A,
        RTRIM( Table2.LNAME ) + ',' + Table2.FNAME AS NAME_A,
        Table2.GRADE AS GRADE_A,
        ( SELECT COUNT(*) FROM Table2 AS cnt WHERE cnt.LNAME <= Table2.LNAME ) + 4 AS ROW_B,
        RTRIM( Values_B.LNAME ) + ',' + Values_B.FNAME AS NAME_B,
        Values_B.GRADE AS GRADE_B
    FROM table2
        LEFT JOIN ( SELECT FNAME, LNAME, GRADE,
                            ( SELECT COUNT(*) FROM Table2 AS cnt WHERE cnt.LNAME <= Table2.LNAME ) AS Row_number
                        FROM Table2
                        WHERE ( SELECT COUNT(*) FROM Table2 AS cnt WHERE cnt.LNAME <= Table2.LNAME ) > 4
                  ) AS Values_B ON Values_B.Row_number = ( SELECT COUNT(*) FROM Table2 AS cnt WHERE cnt.LNAME <= Table2.LNAME ) + 4
    WHERE ( SELECT COUNT(*) FROM Table2 AS cnt WHERE cnt.LNAME <= Table2.LNAME ) < 5

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[DOGWATCH]

My hats off to you, it works perfect inside SQL there must be sublte differences in the SQL for access. I still hickup on this section in access:

Values_B.Row_number = ( SELECT COUNT(*) FROM Table2 AS cnt WHERE cnt.LNAME <= Table2.LNAME ) + 4

 

[DOGWATCH]

Finally found a solution very closely based on your query thanks again!!!