MYSQL :: Relational Query And JOINs

M2M Relational Query Help Needed
I have 3 tables, below are a list of the significant fields in each.

Stories
* story_id
* story_date

Editions
* edition_id
* publication_id

Story_Edition_Jct
* story_id
* edition_id

What I need to do is create a list of all the stories that are newer than 30 days and have not been assigned to a given publication_id, but may or may not have been assigned an edition.

Just to clear that up some more, a publication consists of many editions, so we're looking for stories that may have been published in other publications, but want to restrict them from being republished in a given publication.

The following query is what I've come up with:

Relational Database, Normalized, Need Query
So I am working on a project that deals with a large normalized database. The database used to be constructed with one users table that had a location field which contained a comma separated string of location abbreviations signifying which location that employee worked at. However, I have now changed the database to as follows:

CREATE TABLE `location` (
`LocationID` smallint(6) NOT NULL auto_increment,
`LocationName` varchar(120) NOT NULL,
PRIMARY KEY (`LocationID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

CREATE TABLE `users` (
`UserID` int(11) NOT NULL auto_increment,
`fname` varchar(255) NOT NULL,
`minitial` varchar(2) NOT NULL,
`lname` varchar(255) NOT NULL,
`department` mediumtext NOT NULL,
`positionTitle` varchar(255) NOT NULL,
`supervisor` varchar(4) NOT NULL,
PRIMARY KEY (`UserID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=229 ;

CREATE TABLE `locationrelationship` (
`LocRelationID` int(11) NOT NULL auto_increment,
`UserID` int(11) NOT NULL,
`LocationID` int(11) NOT NULL,
PRIMARY KEY (`LocRelationID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;
There are a lot more fields in the users table but the above should do.

My problem is I am now trying to query the users table and include the location name that the employee works at.

The below query should do this if the employee only has one location otherwise it will display multiple of the same user:

SELECT replace(concat_ws(' ', users.fname, null, users.minitial, null, users.lname), ' ', ' ') AS userName, users.department, location.LocationName FROM users JOIN locationrelationship ON users.UserID = locationrelationship.UserID JOIN location ON location.LocationID = locationrelationship.LocationID
An example of this could be:

John Doe, Billing, South Bend
John Doe, Billing, Elkhart
Jane Smith, Sales, South Bend
However I am trying to create a query that joins multiple locations and separate them by a comma then a space then applies it to some locations value in a query.

An example of what I am looking to do is:

John Doe, Billing, "South Bend, Elkhart"
Jane Smith, Sales, South Bend
Any ideas?

Joins Query
I have a page whereby I list all events and flyers for each event. Because each event can have more than one flyer it's obviously a many-to-many relationship, so I have introduced a new table called event_flyers which has the following fields; event_id, flyer_id. How *should* the query look?

SELECT *
FROM calendar,
flyers
LEFT JOIN calendar_flyers
ON calendar_flyers.flyer_id = flyers.id
WHERE calendar_flyers.calendar_id = calendar_id

Design / Query Confusion - Joins?
Sorry for the long post here. I'm trying to give enough info. to explain my situation, and am novice enough to not know how much someone might need to know to help.

I'm very new to SQL, I've read through the Sitepoint "Build Your Own Database Driven Website" book, and though I did follow it, I didn't build the joke database, I just converted it to the specific task I want to do to organize some stuff for my department at work.

Basically what I am doing is tracking design drawings; Job name and number, clients, architects, when drawings leave and return for approvals, to the shop and field, etc... Getting all the information that our detailers might need, in one place.

Where I'm running into problems, is designing the database with some thought on how we set up our job numbers and attempting to keep the tables fairly small by creating tables for each year.

A typical job number would be something like:
B06-064 (B is first initial of the Project Manager - 06 for the year - and 064 for the 64th job that year)

04_jobs, 05_jobs, and 06_jobs all have the same columns:

CREATE TABLE 04_jobs (
job_year YEAR(2) NOT NULL DEFAULT ཀ',
id SMALLINT(3) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
job_name VARCHAR(255),
pm_id TINYINT(3),
client_id SMALLINT(4),
arch_id SMALLINT(4),
finished SET(Ɔ', Ƈ') NOT NULL DEFAULT Ɔ',
PRIMARY KEY ( job_year, id)
);

The job_year seems a bit redundant since the tables are named by year- I was thinking the problem with my select query had to do with the duplicate PKs (id) across tables - this may be a good indication of how little I understand everything - I just added this column and changed the PK this morning.

When I run a query such as:
SELECT * FROM 04_jobs, 05_jobs;

I get rows joined together - each job in each tabled joined on rows.
not what I was expecting. - getting a row for each job - as if the query were on a single table.

When I run a query such as:
SELECT * FROM 04_jobs, 05_jobs WHERE pm_id="1";
I get an error that the clause is ambiguous.

I guess where I'm most confused is not knowing if its:
1. a design flaw
2. a query flaw (everything I've read so far on JOIN seems directed at joining rows - which isn't what I want)
3. Something that is easily handled with a PHP loop (I'm new to PHP too).

I'm doing everything on a command line for now, I haven't written any php for this area of the project.

To Re-write Complex Query With JOINS
I have a problem with one of the JOIN query here.
Below is a brief description of the problem.

tablename : test

RecordId EffectiveDateothertableidvalue
-------- ------------- ------------ -----
12004-01-10110
22004-01-20120
32004-01-20270
42004-01-10280
52004-01-15110
62004-01-25310

Output :
RecordId EffectiveDateothertableidvalue
-------- ------------- ------------ -----
22004-01-20120
32004-01-20270
62004-01-25310

Now I want to use a single SQL query to find a result
where there exist one record for each unique
"othertableid" where the record selected for the
"othertableid" should be the recent one with regard to
"EffectiveDate".

That is from the above records, I want to select
Records with "RecordId" = 2 and 3 because they are the
recent one for "othertableid" = 1 and 2 respectively.
Please be sure that I want to retrive all fields
including "RecordId". The result should not depend on
any other fields but "EffectiveDate" only.

I am using MySQL 4.0.12 and it does not support
"SUBQUERIES" which is now given support in latest
MySQL edition. But I have read in the manual of MySQL
that any "SUBQUERY" SQL statement can be written with
proper "JOINS".

Report Writing Query Problem (joins Again...)
Hola database type folks,

I have been trying to get this right for sometime but now must admit defeat

I have 3 tables

artists -> holds details on the artists
card_gallery -> hold details of card available related to artists via artist_name
scart -> shopping basket related to card_gallery via card_image_id

I am writing a reporting facility that allows the administrator to view all the artists in a table along with their number of cards online and cards sold figures.

Currently I have something like

select
card_gallery.artist_name as cg_an,
count(card_gallery.artist_name) as cg_ca,
count(scart.card_image_id) as s_ci,
from
card_gallery
left join
scart
on
card_gallery.card_gallery_id=scart.card_image_id
group by card_gallery.artist_name
but that doesnt give the correct number of cards sold.
Cards sold are registered in the scart table by setting the processed field to 1

Ideally I would like to be able to view all the artists on one table with the number of cards online and the number sold even if it's 0

Any Tool Where You Can Visually Trace Query Joins?
Talking with a colleague, we wondered if there exists a tool where you can build a query and see all the tables it joins visually but soem sort of line or graph.

Unable To Do Joins With MySQL Query Browser
I downloaded the Query Browser for Windows. I try to join two tables as described in the manual, drag over the JOIN tables tab but nothing happens. The query remains as is.

Relational Db
i have a table "events" and a table " dates". As you may guess, one event has many dates (one-to-many relationship).
i would like to return a table that contains one event per row, with one column containing that event's dates.
target mysql version is mysql 3.23.58
Is it at all possible? I think mysql 4.1 has inner select but it is not possible in this case.

Relational Tables
i'm working on a project that consists of 2 things.
first a info-website: some general info and news items, projects, etc...
second an archive: everything about past projects

i want to structure the data into an mysql database and after reading this article , i've got a general idea how to build up a good database.

http://dev.mysql.com/tech-resources...malization.html

the only thing I'm wondering. how far must you go, i can almost split up all info into seperat tables and link them together. is it maybe a good idea to make 2 or 3 databases with less tables and make links between databases

for instence: adresses. you can make a table with adresses and relational tabels with al the zip codes, city, etc... if i do this with all data for the projects of de site, i get a database with a countless amount of tables. is it then wise to setup an extra database with just all the adress info and thuss less tables in each database?

Relational Databases
i want to set up a relational database in mysql. i want one table (items) to have a field (supplier) which gathers information fromt the table (people) field (supplier). is this possible?

Relational DB Design
I've contructed a fairly simple normalized (3NF), relational database. By simple, I mean each table relates to other table(s) in a 1 to many (1,n) relationship. Now, I am trying to extend this database, but I'm not sure how to proceed given specific qualifiers. Allow me to illustrate:

* 1 Pod has 4 Channels > (1,4) relationship.

* 1 Sensor may occupy 0, 1 or 2 Channels > (0|1|2) relationship.

* 1 Pod has between 0 and 4 Sensors > Indirect relationship between Pod and Sensors. Direct relationship is between Channels and Sensors.

* 1 Sensor connects to 0 or more Pods, but only 1 Pod at any given time. Indirect Many-to-many relationship between Pod and Sensor. Again, the direct relationship is between Channels and Sensors.

I've drawn up a db diagram and attached. PodSernsorAssociation allows a many to many relationship between Pod and Sensor, although it may not be correct to even have this relationship.

Relational Database
I need help with creating a relational database and normalizing it.
I've been studying the process of creating a relational database,
however I am unsure if I am doing it correctly. Below is a sample
list I have created (on paper)of my database design.

Can someone review and let me know if I am doing everything correctly
and give me suggestions for improvement? Code:

Relational Tables
I come from a strong MSSQL background. I recently acquired a project that I need to do with MySQL. In MSSQL I was able to link the tables together, not allowing data to be deleted that was linked into another table. How can I force that type of realtionship on tables in MySQL?

Many-Many Vs Object Relational
In the process of trying to figure this thing out, I've been
doing the old "stand around in the store and read as much as possible
before you look like a derelict" thing. This time, with the O'Reilly
"Managing and Using SQL" (I think that's it).

Anyway, I was looking at a chapter discussing database design (without
any direct reference to impementations in MySQL), and they mention
Obj/Relationaldesign as a way to have a many-to-many relationship. I'm
wondering if they're trying to imply that there are different ways to
implement a many to many. In my bkgnd, I would assume that in any
many-to-many you have to have the joining table, and there's not much
else to do but go to it and select all the rows that have the key
you're interested in.

Relational Database
i want to make a relation between two table, if you delete a specified record in the first table, the record in the second table that related to the first will be deleted too.

eg. if i have a table contains a studentid field and some information about that student and the second table contains the studentid too, and the grades of that student, if i want to delete this student all his grades will be deleted.
how to do that?

Relational Database
I'm redesigning my company website and I've come across a problem that I'm hoping some of you will be able to help me with.

I want to have a "customer login" section of the site. When a customer logs in they can view three categories of files that pertain to that customer. They are Drawings, Manuals and Software. I will be uploading the drawings/manuals and software to the website and so I assume that the table will have to hold the 'link' to these uploads.

I need to design a database that will allow customers to access multiple files but only the files that pertain to that customer.

I am thinking that I'll need two tables, one of customers log in details and one that holds the file names of the relevant drawings, manuals and software that they are able to download.

The problem is that I want multiple customer log ins to be able to see the same files. Basically like a 'permissions' thing. I'm not sure on the best way to do this in a database and I wondered if anyone has any ideas?

Relational DB With MySQL
Is there a way to create relational databases in MySQL like you can in MS SQL? I primarily use MySQL, and I have started to use some MSSQL stuff because I wanted to work on relational dbs, but I just didn't know if there was a way to do it in MySQL like MSSQL

MySQL Relational Database
Anyone here has a suggestions on what to do on this tasks:

1. I want to make a form which has 30 or more fields that our agents will fill in. These are Personal Info, Address and Housing Info, Employment Info, Security Info and Additiona cards info. Now as of the moment, These five categories are being saved in a one table for the query purposes that it is easy to fetch up (i'm a newbie and don't know how to segregate from one to five tables). I'm planning to resetup the database and segregate these five categories into 5 tables also. Now, my question is: these five categories are in one form or page. In a page the format of my web is on below. Now, if agents will click SUBMIT button of course all data will be encoded to the database on each corresponding table. But how I would be able to get or query those data in a one form. If it needs a lookup table, how would I implement that one.

Please help me.. really need your inputs on this matter. Thank you so much....

I. Personal Info:
Firstname: __________ Middlename:_________ Lastname: _______
Homephone:
email address:

II. Address info and house info:
street:_________ city:_______ state:_______ zip:_______
House status:________ House type:____________

III. Employer info:
name:_______________ occupation:___________
salary: ________________
IV. Security info:
Date of Birth:_________ SSN:_________ Mother's Maiden Name:_______

V. Additional Cards Info:
Firstname:________ Middlename:_________ Lastname:_________

(SUBMIT Button)

Properly Querying A Relational Db.
I've recently been working with more relational dbs and I was just wondering...am I working with them properly?

The reason I ask is because I'm wondering if there are easier/more efficient/more "proper" ways to be working with a relational db.

So here is an example: two tables, a project table and an employee table. The project table stores employee id in emp_id and is linked to the employee table by a foreign key (a one to one relation).

SELECT P.project_id, E.first_name, E.last_name, P.name, P.code, P.start_date, P.end_date, P.imdb
FROM project AS P, employee AS E
WHERE E.emp_id = P.supervisor
The thing is eventually we'll start to have tables with 4-5 foreign keys and I'm wondering if this is still "proper". Honestly it may seem a stupid question to some of you experts :-p

MySQL Relational Schema
I have a large mysql database and would like to see a graphical representation of the database schema i.e. primary keys, foreign keys, related tables.

Does anyone know of a good development tool which will display this?

Checking Relational Db Structure
I have a table with the following id, username, password, email, phone, etc. What I want to do is have multiple pages of jobs where these people can submit themselves to. I was thinking of adding 2 more tables. One with jobs like this j_id, j_desc.

Then a second for job submits from users in the main table like this s_id, sj_id. Where the s_id is relative to the id, and j_id is relative to the sj_id. Will this work? Also is there someway of preventing double submits?

Relational Model Software
I need to build the relational model of a database I already have. Does anybody know if there would be a software or so that would make it for me withou me having to rebuild the database? I found some software like CaseStudio, but i'd like somthing simpler, that would import the SQL script or something like this.

Making My Relational Tables Relate
I have created a mysql database and it is normalised to 3rd form.

I can successfully join some of my tables to give the results I am looking for.

However other tables that I join give the wrong data.

I created the table data in a spreadsheet and simply filled in the numbers where table_id foreign key corresponded to table_id primary key in the primary table.

This is probably why some tables relate correctly and others (where the data is repetitive and a reference table was created) do not.

Obviously it appears this is not the way to create relational tables?

Natural Joins And Joins With USING
I was wondering if there is a way to make MySQL 5.0.15 ( final release ) able to use natural joins and joins, using old code that worked with 5.0.11 and earlier.

Since I am new to starting mysql and modifying it, more information the better.

Extracting Data From Large SINGLE-table Database To MULT-table Relational Database
I have a very large single-table database of articles that I want to convert to a multi-table, relational database.

The existing single-table database contains fields for article author, article source, and article category, where several 'author', 'source', and 'category' IDs repeat dozens of times for hundreds of different articles.

I want to create seperate tables for author, source, and category and populate the new tables by extracting data from the original single-table database by unique ID field.

I figured out how to use INSERT and SELECT to pull data in new tables, but can't figure out how to pull only a single instance of a unique author, source, and category to create master reference tables for author/source/category.

Joins
Im trying to show 1 image per product using a table join:

$sql = mysql_query("SELECT p.product_id, p.product_name, p.description, i.photo_filename
FROM products AS p
LEFT JOIN product_images as i ON p.product_id = i.product_id
WHERE filter = ".$_GET['filter']."
ORDER BY product_name ASC");
One product has 2 images linked to it .. with that join rather than displayin the record once with a single image .. it shows the record twice, once for each picture in the db then proceeds to the next record.

What kind of join do I need to have it display 1 image for each record in the loop?

Self Joins
I'm not new to mysql however i have an issue with something that im trying to do.
I store football games in a 'games' table, the table has [among other fields] game_id,game_hteam,game_ateam,game_hscore,game_ascore which relates to the gameid, the hometeam id, the away team id and the relevant scores for each side.....

JOINs
I may be trying to do too much in a single query here, but it would be nice if I could get it working! Apparently, the version of MySQL used by my work does not support the WITH ROLLUP feature, otherwise I think that would work.....

Help With Joins
I have 2 tables (users and comments), where the suers table has a bunch of details about each user, and the comments table has comments left by that user. Each user can have more than 1 comment. I am trying to write query that extracts all users alongwith all their comments.

I have something like:
$sql = "Select * from comments left outer join users on comments.userid=user.userid";

This basically gives me the result, but in an unelegant way. I get as many records for the same user as they have comments. Is there a better way to do this, where I get 1 reslt per user and all their comments are in the form of a list or array?

Joins
I have 2 tables (users and comments), where the suers table has a bunch of details about each user, and the comments table has comments left by that user. Each user can have more than 1 comment. I am trying to write query that extracts all users alongwith all their comments.

I have something like:
$sql = "Select * from comments left outer join users on comments.userid=user.userid";

This basically gives me the result, but in an unelegant way. I get as many records for the same user as they have comments. Is there a better way to do this, where I get 1 reslt per user and all their comments are in the form of a list or array?

Joins
I'm trying to modify a query to avoid using group by or distinct to reduce the amount of time to get results.

I want to get every record from table 1 that has a matching record in table 2
The tables are in a 1 to many relationship ie. One record in table 1 will have many matching records in table 2. I've tried right, left and inner and neither seem to give the desired results - any suggestions from the gurus?

Joins.
I'm using mysql to house a database of quotes, complete with ratings by users. So in one query, I need to get everything with a certain rating from the "quotes" table, get the submitter's username from the "users" table and finally check the "ratings" table to see if the user viewing the quotes has already rated it.

The "ratings" table consists of the user id and the quote id. So ideally, I'll be able to to get the first 25 quotes and end up with each in a row something like this:

submitter's id - quote id - quote text - user id - quote id 2

Joins
I have three tables named project, projecthistory, and billing for my project tracking database. My problems arises when a project doesn't have a projecthistory or any billing items entered for it yet. Then the project doesn't show up at all.

project: id, name
projecthistory: project_id, description, submitdate
billing: project_id, monthlyrate, quantity

SELECT MAX(ph.submitdate), SUM(b.quantity), p.id, p.name, ph.description,
FROM (project p LEFT JOIN billing b ON p.id = b.project_id) LEFT JOIN projecthistory ph ON p.id = ph.project_id;

Ultimately what I need is for ALL projects to show up. If any projecthistory exists, display ONLY the newest one (for that project_id). If any billing exists, display the SUM of the quantities (for that project_id).

Out Of Joins
I have this query:

SELECT c.id AS cid, c.cat_name, f.id AS fid, f.forum_name, f.forum_desc, f.redirect_url, f.moderators, f.num_topics, f.num_posts, f.last_post, f.last_post_id, f.last_poster, t.question, f.parent_forum_id FROM '.$db->prefix.'categories AS c INNER JOIN '.$db->prefix.'forums AS f ON c.id=f.cat_id LEFT JOIN '.$db->prefix.'forum_perms AS fp ON (fp.forum_id=f.id AND fp.group_id='.$forum_user['g_id'].') LEFT JOIN '.$db->prefix.'topics AS t ON f.last_post=t.last_post AND f.last_post_id=t.last_post_id WHERE (fp.read_forum IS NULL OR fp.read_forum=1) AND (f.parent_forum_id IS NULL OR f.parent_forum_id=0) ORDER BY c.disp_position, c.id, f.disp_position

and i want to add the field displayname from table: '.$db->prefix.'users where username = f.last_poster

I have used all of my joins i think (left, right, inner) and i need to add that in, can someone add this in.

Many Joins
I've this table structure:

t2(jb, B)
|
tj12(b, jb)
|
t1(A, b, c)
|
tj13(c, jc)
|
t3(jc, C)

How can I do an elegant "SELECT A, B, C FROM t1" ?

When I try
JOIN tj12 USING(b)
JOIN t2 USING(jb)
JOIN tj13 USING(c)
JOIN t3 USING(jc)
MySQL join tj13 with t2.
Must I use "JOIN ... ON ..." to force MySQL or thre's an other way?

Using Joins
I'm having trouble figuring out which join i should be using to attain the results I am looking for. here's a bit of background information.

I have 3 tables

Table 1 - hp_cats
- cat_id
- cat_name

Table 2 - hp_projects
- project_id
- various other fields that are irrelevant

Table 3 - hp_proj_cat
- pc_id
- cat_id
- project_id
- active

Basically, I have a table of projects, and a table of categories. each project has an entry in hp_proj_cat for each category. if the category is active for that project, active = 1 in hp_proj_cat.

I'm looking for a way to retrieve all projects that have categories say '85, 200, 13, 6' and are active (ie, set to 1).

I know this will involve a join, but am a tad bit confused. I greatly appreciate any help anyone can provide.

Sql / Joins
I have 4 tables: tblProducts (it has all the product names), tblInventory (it has all the products actually in inventory), tblMainIngredient (it has all the main ingredients ids associated with the products' ids), and tblProductIngredient (it has all the ingredients' ids).

I am trying to retrieve all the products and their main ingredient but also those that don't have one. I need to be able to display the product name (in tblproducts) as well as the name of its main ingredient (tblproductingredient). I wrote the following SQL but it doesn't seem to work as it returns only the products that have an ingredient associated with them:

SELECT * FROM (tblProduct INNER JOIN tblInventory ON tblProduct.ProductID = tblInventory.ProductID) INNER JOIN (tblMainIngredient INNER JOIN tblProductIngredient ON tblMainIngredient.IngID = tblProductIngredient.IngID) ON tblProduct.ProductID = tblProductIngredient.ProductID WHERE tblProduct.ProductID = " & productID ORDER BY Name, Price, Comments

Joins
I was wondering if someone cold explain the JOIN to me, I've looked over the forum but don't understand.
I know kind of that the INNER JOIN is the same as WHERE???
whats the difference between LEFT and RIGHT JOINS?
And INNER and OUTER JOINS???
Please help because I'm having major problems with my search sql which I'm trying to write for my uni coursework

Joins
Table Oelsls contains all products bought (history) table Invt contains
the products to sell (Inventory) I'm trying with no success to list all
products in Oelsls table (field name in both tables: part_id) that are not
in the Invt table.
My objective is a grid of all Special (non inventory) products.
Can anyone produce the correct sql statement?

Joins
an explanation of the different types of JOIN.
LEFT, RIGHT, INNER, OUTER etc.
I can find syntax for all the instances but feel a discussion which compares the different types will be very useful.

Self Joins
however, when I do this, I am only able to display the two parts that meet the ON statement, and not have the right side null when there is nothing that exists on a left join. that is how it should be.

for example

select this1.something, this2.something from sametable as this1 left join sametable as this2 on this1.column = this2.column
where this1.form = 'red' AND this2.form = 'blue'

I have used this, and it works. basically, I am joining the red columns with the blue columns and displaying the data on one area because the red columns are different rows and so are the blue columns. anyway, is there a way to make it so that if there is no blue column that exists, the red column will display its contents.

Joins
i have these two queries:Code:

SELECT totalscore FROM top_scores WHERE userid=3
SELECT MIN(score1),MIN(score2),MIN(score3) FROM scores WHERE userid=3

how can i use join to make that query into one, but still selecting from the two seperate tables?

Joins
I'm trying to join the equipment table whenever "nsidetails.equipmentid = equipment.itemid". I get correct data but the equipment table never joins.

SELECT equipmentid, COUNT( equipmentid ) AS numOfItems
FROM nsidetails
LEFT JOIN equipment ON nsidetails.equipmentid = equipment.itemid
WHERE nsidetails.itemstatus = 'SHORT' AND nsidetails.location = '".LOCATION."'
GROUP BY nsidetails.equipmentid
ORDER BY nsidetails.equipmentid

JOINs And UserNames...
i need a bit of help on the following problem.
Table I
Row_i. 1 2 _
Row_ii. 3 1 6
...
Row_j. 9 _ _
The numbers in each row identify users (User_A, User_B, User_C); not all rows have all three users filled in - in the example above, the first row has two users, the second three, and the last only one.
Table II
Row_i. 1 Fred Bloggs
Row_ii. 2 John Smith
Row_iii. 3 Pat Brown
...
Row_j. 6 Tom Adams
...
Row_k. 9 Vicky Brown
...
Row_l. n Name Surname

I can thus identify each user (if present) in Table I.

So far, so good. Now for the problem. I need to generate a list of ALL the names used in Table I, either as User_A, User_B, or User_C, without repetitions, ordered by Surname. Given the example above, the output should be
6. Tom Adams
1. Fred Bloggs
3. Pat Brown
9. Vicky Brown
2. John Smith
Simple, right?... But I guess the old brain is just not cooperating today, because I can't seem to get my query right.
Any simple and elegant solutions out there?
Many thaks in anticipation for your help.

Joins And Limit Used Together
I have a query, which reads all information from tables.

select se.section_name,pr.*,ph.code from prices pr left join pharmacy ph on pr.pharmacy_id=ph.id left join sections se on pr.section_id=se.id where medication_id=7 order by se.order_id asc, cast(`dosage` as SIGNED) asc, cast(`total_price` as SIGNED) asc

result can be viewed here:
http://www.foreign-drugstores-online...oduct.php?id=7

I need to read only top 5 (total price) for each `dosage`

somekind of: group by `dosage` order by total_price desc limit 5 per group

Limiting Inner Joins
This has been bothering me and the relation isn't typical of the other expressions I've had to build.

I have a table of 250 records that I'm returning ie.

SELECT B.ID, B.Title, B.Owner
FROM blogs AS B
WHERE B.Status = 0
I need to return not only the blog name but the latest post they've made on their account. So what I did was used:

SELECT B.ID, B.Title, B.Owner
FROM blogs AS B
INNER JOIN archives AS A ON B.ID = A.BlogID
WHERE B.Status = 0
But when I do this it returns each record in archive with the relevent blog information. What I would like is for it to determine the latest ID insert on the blog.

So if he has posts IDs such as 1010, 1050, 2063 and 2075 then it will return the blog information and the results of record 2075 in the archives table.

I've tried a couple ways so far but each fails, I think what I want to do is somewhat limit the return on the inner join...sort of like add a WHERE clause unto the clause.

I would have done this with a stored proc. or a view but I'm using MySQL 4 and I want to keep the querying function in my code for now.

Not sure if that makes sense to the experts, if my problem isn't clear let me know I'll explain it further.

Conditional Joins
This is similar to a previous thread of mine: http://www.sitepoint.com/forums/showthread.php?t=375169

I have two fields in my user table, 'lastentryid', and 'lastprivateentryid'. What I need to do is join the entry table using that entryid. Unfortunately, the results are skewed if their last entry is private, because lastentryid won't contain it. I am running into this problem in a few places, and I'd really like to solve it without using subqueries. I might even accept better organization on the db level.

Here is my query as it is now

SELECT
user.userid, user.options, user.displaygroupid, user.usergroupid, user.username,
ugroup.opentag as opentaga, ugroup.closetag as closetaga,
dgroup.opentag as opentagb, dgroup.closetag as closetagb,
entry.title, entry.dateline as entrydateline
FROM vb_user as user
LEFT JOIN vb_usergroup as ugroup ON(user.usergroupid = ugroup.usergroupid)
LEFT JOIN vb_usergroup as dgroup ON(user.displaygroupid = dgroup.usergroupid)
LEFT JOIN vb_blog_entry as entry on (entry.entryid = user.lastentryid)
WHERE entry.dateline > $cutoff
ORDER BY user.username ASC
Here is what I want... but this doesn't work.

SELECT
user.userid, user.options, user.displaygroupid, user.usergroupid, user.username,
ugroup.opentag as opentaga, ugroup.closetag as closetaga,
dgroup.opentag as opentagb, dgroup.closetag as closetagb,
entry.title, entry.dateline as entrydateline,
if (
user.lastentryid > user.lastprivateentryid,
user.lastentryid,
user.lastprivateentryid
) as last_entryid
FROM vb_user as user
LEFT JOIN vb_usergroup as ugroup ON(user.usergroupid = ugroup.usergroupid)
LEFT JOIN vb_usergroup as dgroup ON(user.displaygroupid = dgroup.usergroupid)
LEFT JOIN vb_blog_entry as entry on (entry.entryid = last_entryid)
WHERE entry.dateline > $cutoff
ORDER BY user.username ASC