SQL syntax

scambro

I'm in need of some help with creating a query to in mySQL.

Table Structure:

Table 1 --------
table1_id
table1_name
active_ind

Table 2 --------
table2_id
table1_id
table3_id
table2_sequence
active_ind

The design of the database allows me to have 1 parent row in table 1, and multiple child rows in table 2. However, I want to write a query that pulls back the 1 parent row, and then one row from table 2 with the lowest sequence number.

I have done this in two sepearate queries, and then printed it into an html table, but the problem with that was I didn't think I'd be able to resort the table by this value.

Thanks!

travelbuff

SELECT table1., table2. FROM table1, table2 WHERE table1.table1_id=table2.table1_id ORDERY BY table2.table2_id ASC LIMIT 1

Of course, you will want to include some additional criteria in the WHERE clause so that not all records are returned.

Is that what you wanted?

scambro

Originally posted by travelbuff
SELECT table1., table2. FROM table1, table2 WHERE table1.table1_id=table2.table1_id ORDERY BY table2.table2_id ASC LIMIT 1

Of course, you will want to include some additional criteria in the WHERE clause so that not all records are returned.

Is that what you wanted?

No, that only gives me one row period, I want one row from table2 for every record in table1. Is that even possible to do in one statement?

Thanks!

scambro

Originally posted by scambro
No, that only gives me one row period, I want one row from table2 for every record in table1. Is that even possible to do in one statement?

Thanks!

Maybe this would explain it better. If I knew that the lowest sequence value was going to be 1, and there would be no repeats in that, I could write it like this:

SELECT
table1.table1_name,
table2.table2_sequence,
table2.table2_id
FROM
table1, table2
JOIN table2 ON
table1.table1_id = table2.table1_id
WHERE
table2.table2_sequence = 1

So maybe I should look into writing to that table with the values 1,2,3, instead of having the user enter their own numbers, which could be 10,20,30.

Thanks!