MySQL query help (part 3): selecting a record and the records adjacent to it

[stndn]

Update 2:
I guess it's easier to accomplish what i wanted using PHP than using SQL query, so the question can now be safely dismissed...

Thanks for all the help. Really appreciate the lesson -)

Update 1:
To make things more complicated, i added a column in the table called 'status'.
Now, is it possible to find out the adjacent rows with the same status, given the name to search for?

Example, I am given name = 'six', and i'm supposed to find two adjacent rows with the same status='y', which happends to have id=4 and id=6.


Oh yah, just a background information on what i'm trying to do (and no, it's not homework.. I wish i had homework ... )
I'm trying to design a newsletter page with PHP/MySQL, and the user can post new messages to the database. When each message is viewed, it will have a navigation link to the previous and next message, which is the adjacent records in the database.
(The database below is just a significantly simplified version of what it will look like)

If a certain message is disabled (with status = 'n'), then the links to previous and next message should also be dynamically changed.

Now that i think about it ...
Is it easier to just read in all the messages into a PHP array, sort it based on ID and status, then do another SQL query to search for the current record + the two adjacent to it?

Or it's easier to just do it all from SQL?

Thanks.


Original post:

Assume that i have a table as attached in my mySQL table:

How do I select the row with name = 'six', as well as the two rows adjacent to it? Basically searching with name = 'six' will give me Id=6, and i want records with id = 5, 6, and 7

Is it possible without introducing subquery (SELECT within SELECT)?

Thanks.

 

[mAdMaLuDaWg]

Here is something I wrote up real quick... lemme know if it works:

Select A.Name,B.Name,C.Name from table1 A INNER JOIN TABLE1 B ON A.ID=B.ID-1 INNER JOIN TABLE1 C ON A.ID=C.ID+1 WHERE A.Name='SIX'

 

[stndn]

Thanks, mAdMaLuDaWg.
I tried it out and it works! -D

Now the question is, which one is simpler?
1. Doing one simple (or rather, complicated) query with multiple INNER JOIN to find three records, or:
2. Doing one search for table.name = 'six', then doing another query for ID = id+1 OR id = id - 1 WHERE id = id_of_six (in essence, two queries)

I'm leaning towards the first .. the posted solution.

 

[mAdMaLuDaWg]

Simpler in what sense? For the SQL compiler.. well, for queries like the ones you are running, it won't make a difference. Whereas if you had gazillion records or something, then the one I posted would *probally* execute faster.
Also, I don't see why anyone would want to run a query like this... if you are using it extensively, you might need to take another look at your db design.

 

[oog]

generally, you shouldn't be looking for id +- 1 since that means that you have some kind of relationship between the records that is based on the value of the id. it would be better if your ids could conceivably be any unique values and you had this relationship modeled explicitly through some other means.

 

[kamper]

Trying to do not just a +/-1:

select * from table where id = (select max(id) from table where id < 6)
select * from table where id = (select min(id) from table where id > 6)

Those'd be seperate queries after your query for id = 6. I guess if you don't want nested you could break each into two queries: do the inner query first, then put the result into the second. That's a tonne more work but I bet it'd be way faster on large tables (assuming proper indexing on id) than a double join. It also probably wouldn't be too hard to fit the max/min concept into mAdMaLuDaWg's query.

 

[stndn]

oog:
generally, you shouldn't be looking for id +- 1 since that means that you have some kind of relationship between the records that is based on the value of the id. it would be better if your ids could conceivably be any unique values and you had this relationship modeled explicitly through some other means.

That was actually the next step that I want to go to...
The next step would be to add one field in the table (i put 'status' in my original post).
Given the text 'six', i would want to find out the adjacent rows which has the same status as the status of 'six' (in this case, i want to find record number 4 and 10, which have the same status = 'y')

And with this, the SELECT where id=id+1 and id=id-1 won't work

kamper:
Trying to do not just a +/-1:

select * from table where id = (select max(id) from table where id < 6)
select * from table where id = (select min(id) from table where id > 6)

That won't work unless i know the id of the row i'm looking for, which is '6'.
But i was only given the name = 'six', and i'm supposed to figure out that name='six' has id=6, and find the next ones adjacent to it.

 

[stndn]

mAdMaLuDaWg:
Simpler in what sense? For the SQL compiler.. well, for queries like the ones you are running, it won't make a difference. Whereas if you had gazillion records or something, then the one I posted would *probally* execute faster.
Also, I don't see why anyone would want to run a query like this... if you are using it extensively, you might need to take another look at your db design

I was looking for "simpler" in the sense of less CPU cycle. I don't even know why i'm thinking that far when my records won't even hit 1000 rows ever .... But, just in case ,p

And i'm not good at database design, so that's the best i could come up with ... -(
Maybe if i have time this weekend, i'd actually break out the table i want to use into two separate tables and see if things can be easier for me.

<-- always think too far ahead even when sometimes it's not necessary

 

[notfred]

There is no such thing as "adjacent" in SQL.

 

[stndn]

notfred:
There is no such thing as "adjacent" in SQL.

but at least there is simulated adjacent SQL records, right?
Or ... maybe it can be simulated somehow, despite the difficulty? -(

 

[mAdMaLuDaWg]

stndn, the situation you are describing for the status should be done in your program logic instead of the db logic. Manipulate the resultset of your query in PHP to achieve the desired output.

 

[oog]

If you're thinking of adjacent records in SQL, you're thinking of the database incorrectly. You should not have any expectations of the order in which data is stored in the database. Any ordering or relationships in the data should be explicitly modeled in your schema. If you want to indicate that one row contains forum data that is a response to another row of data (which is what I think you're doing), then you should probably be modeling a parent/child relationship between those forum posts. Or at least putting an order number as one of the columns in your table so you know which one is supposed to be earlier in the sort order.

 

[stndn]

mAdMaLuDaWg
stndn, the situation you are describing for the status should be done in your program logic instead of the db logic. Manipulate the resultset of your query in PHP to achieve the desired output.

Yah, you're most likely right. I was thinking of doing minimal PHP and more SQL logic, because i'd hate to query 100+ records and assign to array of 100 members only to retrieve 3 elements in the end. But, if that's how it's done, then that's how it will be done.

oog
If you're thinking of adjacent records in SQL, you're thinking of the database incorrectly. You should not have any expectations of the order in which data is stored in the database. Any ordering or relationships in the data should be explicitly modeled in your schema. If you want to indicate that one row contains forum data that is a response to another row of data (which is what I think you're doing), then you should probably be modeling a parent/child relationship between those forum posts. Or at least putting an order number as one of the columns in your table so you know which one is supposed to be earlier in the sort order.

After giving it some thoughts, i guess i'll follow what's suggested by mAdMaLuDaWg and do the programming in PHP after retrieving all the available data. I should really scrap out my database 'design' and actually read more on database design before attempting something bad -(

I don't have much background in database, and that is possibly why my understanding of database is seriously out of order. But hey, i'm learning new things with the dumb questions i posted, so i guess it's not that bad after all ,)

Thanks for all the replies -)