MYSQL :: Join 3 Tables - Distinct Results

Join 3 Tables - Distinct Results
I've got a database that keeps track of sales of widgets. Each company
that belongs to my organiztion is to report their widget sales or no
sales every month.

There are several different types of widgets. Not all companies sell or
report all types of widgets.

We want to report how many companies have reported or not reported their
sales (ie. x companies of a possible y companies have reported sales
this month - y will always be the same - lets say 5).

Because of the way that sales are input, "big widgets" are reported in 2
different tables called widgets_a and widgets_b. If they don't have any
sales to report, they still report and it goes into a table called
no_reports. Each table has a couple of common fields - ManufacturerID
and OrderDate.

I can search all of the tables individually to find if a manufacturer
has reported -

SELECT DISTINCT ManufacturerID FROM widgets_a WHERE OrderDate >=
'2003-06-01' AND OrderDate <= '2003-06-30';

but I want to search through the 3 tables and find how many distinct
manufacturers have reported in the given month.

Selecting Distinct Results In A Join
SELECT p.post_title
, p.post_name
, c.cat_name
, DATE_FORMAT(p.post_date, '%M %D, %Y') AS dateadded
FROM wp_posts AS p
LEFT JOIN wp_post2cat AS pc ON p.ID = pc.post_id
LEFT JOIN wp_categories AS c ON pc.category_id = c.cat_ID
WHERE MATCH(p.post_content, p.post_title)
AGAINST(TRIM('merchant') IN BOOLEAN MODE)
ORDER BY p.post_date DESC
I am doing a fulltext search on a Wordpress database. Some posts are in multiple categories. When using the above query I will get a result for every category a post is in. I don't want that. I have tried using distinct with no effect. The wp_categories has a row for each category a post is in and how I am joining it seems to be the culprit.

DISTINCT With Multiple Tables And INNER JOIN ?
My query works OK this way, but I need every "kohde_name" only once
(preferred the earliest "hav_date").

I've tried with "DISTINCT kohde_name" in many ways, but always end up with
syntax error...:(
Is there any solution with DISTINCT, or do I need anotjer solution?

my (simplified) query:
SELECT henk_name,henk_number, kohde_name
,kohde_txt,havainto_id,hav_kohdetxt, hav_date
FROM ((henkilo INNER JOIN havaitsija ON hja_henkilo_id=henkilo_id)
INNER JOIN havainto ON hja_havainto_id=havainto_id)
INNER JOIN kohde ON hav_kohde_id=kohde_id
WHERE ...

Getting Two Distinct Fields From One Tbl From A Join Query Of Two Tables
Im trying to get distinct (the same row returned only once) user_id and
forename from a tbl of users (they are always distinct) where user_id
in that tbl equals friend_id in another table friend_id, which 'should'
be unique ie there 'should' only be one row of a 'friend connection',
but in the case of bugs etc this might not be true, what i want to do
is select only the row of tblusers with the user details that are
matched by join condition below, but only ONCE for each, ie not in as
many times as there are the same connection in tblfriends.

For some reason the result i am getting is a repitition of the 2 fields
in tblusers (that i am selecting) for as many rows as there are in
tblfriends that match the join condition.

How can i make it only return a row once?

Join 3 Tables, Limit Results From 3rd Table
Yet again I have a query about joins and even though I keep reading I cannot find a solution for my problem that is relevant or that I understand.

I have 3 tables (I have shortened the number of fields to those that are relevant):

category:

Quote:

catid

product:

Quote:

prodid
catid
prodname

prodimgs:

Quote:

imgid
prodid
imgname

I can select all products per category and I can select all images per product, but where I am having the problem is selecting all products for one category and at the same time selecting all the images per product and limiting the number of images displayed to one without limiting the products to one.

I have tried various joins and tested a lot, getting different results but none of them are returning what I need.

Select all products for a category:

PHP

SELECT prodid, prodname FROM product WHERE catid='$this->catid'

Works fine.

Select all images per product per category and limit results of image to 1:

PHP

SELECT product.prodid, product.catid, product.prodname, prodimgs.imgname FROM product, prodimgs WHERE product.catid ='$this->catid' AND prodimgs.prodid = product.prodid LIMIT 1

This returns only one product and one image per category despite there being more.

Using the same code as above but removing the limit I get a display of all images per product with the product name repeated over and over.

I know I need to use joins somehow but I have tried various ways and tested the sql but am always getting an error on the joins and I don't know how or where to put the limit so that it is only applied to the prodimgs table.

So what I want is:

1. User click on a category

2. On the next page all product names linked to the category are displayed, but once only.

3. Next to each product name, one image related to that product is to be displayed if an image is stored in the prodimgs table

Combining Results From Querying Two Join Tables
I'm using two queries to pull data from a two join tables and am looking for a way to get them into one result.

The first query is:
select group_concat(x) as genre_id, group_concat(y) as genre_name from (
select genres.id as x, genres.g_name as y from genres where id in (
select genre_id from genres_movies where movie_id = 70000103
)
) as tbl

+---------------------+---------------------------------------------------------+
| genre_id | genre_name |
| 864,131,813,191,321 | Documentary,Indie Documentaries,Political Documentaries |
+---------------------+---------------------------------------------------------+
The second is:
select group_concat(a) as director_id, group_concat(b) as director_name from (
select director.id as a, director.p_name as b from directors where id in (
select director_id from directors_movies where movie_id = 70000103
)
) as tbl2

+----------------------------+------------------------------------+
| director_id | director_name |
| 20006021,20063045,20063046 | Chris Smith,Dan Ollman,Sarah Price |
+----------------------------+------------------------------------+
Does anyone see a way to get something like this?
+---------------------+---------------------------------------------------------+----------------------------+------------------------------------+
| genre_id | genre_name | director_id | director_name |
| 864,131,813,191,321 | Documentary,Indie Documentaries,Political Documentaries | 20006021,20063045,20063046 | Chris Smith,Dan Ollman,Sarah Price |
+---------------------+---------------------------------------------------------+----------------------------+------------------------------------+

Many To Many Select Using DISTINCT Or Not Return The Same Results
I was on MySQL website looking for an answer, but still need assistance please.

I want to do the opposite of the following:

SELECT * FROM myTable WHERE id IN(1,2,3,4,5) ;

I want to use my array(1,2,3,4,5) to exclude items in the result set, not include them.

So, I want to do something like this:

SELECT * FROM myTable WHERE id NOT_IN(1,2,3,4,5) ;

Select Distinct Results Without Throwing Away Data?
id series_id image date
6 2 1191956895.jpg 2007-10-09
5 3 1191956773.jpg 2007-10-09
4 2 1191451083.jpg 2007-10-03
3 1 1191451026.jpg 2007-10-03
2 1 1191451012.jpg 2007-10-03
1 1 1191450488.jpg 2007-10-03

if you take a close look at the code above you might be able to see that i have these images assoicated with one of 3 series. id like to be able to select the most recent from each one and discard the rest. however a command link distinct on the series_id throws away all the other data around it that i need.

the results would look more like

seires_id image
2 1191956895.jpg
3 1191956773.jpg
1 1191451026.jpg

i imagine this would be much like a forum sorting the most recent topics posted in a forum by finding the most recent posts for each distinct topic.

Running A 'Distinct' Query Returns Some Results TWICE !?
I've spent hours trying to find out why a perfectly simple query that uses the 'distinct' keyword such as:

Select distinct(field1)
From tablename;

works fine based on eg. field1, but when running it on eg. field2 in the very same table, it returns 2 results (yet, only on some words!). Pls see example of result below:

Eg of a 'faulty' result: .....

SELECT DISTINCT Unexpected Results With Multiple Columns
SELECT DISTINCT (query) FROM `data` WHERE 1 LIMIT 0 , 30
and it works fine. When I start selecting more than one column, though, it returns all the rows weather the query is distinct or not. Here is the query that doesn't work.

SELECT DISTINCT (query), data. * FROM `data` WHERE 1 LIMIT 0 , 30
It seems like DISTINCT() is looking for a completly distinct row. How can I get it to just look for a single distinct column?

SELECT DISTINCT On A Single Field - Full Row Results
I've been trying to figure out how to do this and can't seem to wrap my thoughts around how to do it.

Basically I want to select every row that is distinct based on a single field, like:

Author | Book Name | Book Num
Bob | ThisBook | 1
George | ThatBook | 2
Fred | HisBook | 3
Bob | HerBook | 4

How would I select each author only once, but return the entire row for that unique result? Basically I'd want Bob, George, and Fred to all appear, but Bob to only appear once, not twice.

And this is assuming I can't change the table to be better structured, I don't have direct access to it. I realize how it SHOULD be set up, but unfortunately it is not.

Join Vs. Inner Join Vs. Implied Join = Different Results ??
I SUM() only on the order table in all queries below. Here's a set of queries that I thought would/should yield the exact same results:

QUERY 1:
SELECT COUNT( o.orderID )
FROM order o
WHERE DATE( o.orderDATE ) = &#55614;&#57159;-01-04'
AND o.orderSTATUS = 300

yields 161

QUERY 2:
SELECT COUNT( o.orderID )
FROM order o
LEFT OUTER JOIN credit_card cc ON o.orderID = cc.orderID
WHERE DATE( o.orderDATE ) = &#55614;&#57159;-01-04'
AND o.orderSTATUS = 300

yields 175

QUERY 3:
SELECT COUNT( o.orderID )
FROM order o, credit_card cc
WHERE o.orderID = cc.orderID
AND DATE( o.orderDATE ) = &#55614;&#57159;-01-04'
AND o.orderSTATUS = 300

yields 157

Outer Join And Distinct
I just started building a website and use MySQL as my backend database. I'm having a little trouble creating an SQL query that will get the correct information out.

Here's my problem:

I have 3 tables:
people: stores the name of people who can vote
votetype: stores the type of votes there are (In favor, Against, Withheld)
decisions: the dicisions that were voted on

Then I have another table that links them all together called votes. Votes has three columns: personID, typeID and decisionID. personID and decisionID are primary key. All are foreign key to obvious tables.

I want to retrieve for a given person the votes he made for all decisions. So: a list of all decisions with the vote he or she made and a NULL if there is no vote.

So far I have this query:
SELECT DISTINCT d.ID, d.name, d.date, v.typeID AS vID, t.name FROM decisions d, votetype t LEFT OUTER JOIN votes v ON v.decisionID = d.ID WHERE (v.personID=1 OR v.personID IS NULL) AND (v.typeID=t.ID OR v.typeID IS NULL) ORDER BY d.date;

For some reason the DISTINCT doesn't work and I get a decision that is not voted for three times (once for each type while v.typeID column is NULL).

How can I fix this so each decision only shows up once?

Jurgen

-------------------------
I've found a solution:

SELECT DISTINCT d.ID, d.name, d.date, v.typeID AS vID, IF(v.typeID IS NULL,NULL,t.name) AS stem FROM decision d, votetype t LEFT OUTER JOIN votes v ON v.decisionID = d.ID WHERE (v.personID=1 OR v.personID IS NULL) AND (v.typeID=t.ID OR v.typeID IS NULL) ORDER BY d.date;

Distinct And Join Query In MySQL
I have a query question, it may be really easy but somehow I cannot get it

What I have is a table with columns, two of which I will name Item1 and Item2,

I would like to get a distict set of pairs Item1-Item2 from this table, more specifically if table looks like this ....

How To Join Results ?
Is it possible to join results of the select query ?

So If I get data from two rows (firstname, secondname)Can I somehow join query results like this: firstname.secondname

Example:

QUERY: SELECT firstname,secondname FROM table;

RESULTS:
John.Doe
Mike.Monroe
Kate.Moss

Limit Inner Join Results ?
Is it possible to limit the results of an inner join or sub select ?
For example, let say that I have a table of countries and a talbe of cities:

Sql Returning Too Many Results On JOIN
I have three tables which I'm joining...

AGENTS
========
agent_id
commission
promo_code

AGENT_PAYMENTS
==============
payment_id
agent_id
amount

SALES
========
sale_id
promo_code
status

I need to select all the sales info where an agent's promo code has been used (and the status of the sale is "C" (complete)). THe trouble is, the SQL I've used is doubling, trebling etc the sum of the payments made to an agent - this depends on how many results are returned for the number of sales... eg: 4 sales means the SUM is being multiplied by 4.

here's the SQL Code:

Join Duplicated Results Issue
I have two tables, one is a catalogue of products and another one with media connected to the catalogue items. the media can be either 'main image' or an 'alternative product view'. I need to retrieve 4 products that are marked as live (c.status = Ƈ') and their 4 corresponding main images (m.category = 'main') with no repeats on the products.

I have the following query:

SELECT DISTINCT c.id, c.category_id, c.title, c.description, m.filename, m.description, m.copyright
FROM catalogue c
LEFT JOIN media m
ON m.parent_id = c.id
WHERE c.status = Ƈ'
AND m.category = 'main'
LIMIT 4
the problem I have is that some products have more than one 'main image' so this query is returning a duplicate product because it has two main images, even if the product only exists once on the catalogue..

Connecting Three Tables With Left Join And Ordinary Join
I have 3 Mysql tables:

Week (with columns day and hour)
Activity (with columns day, hour, activityid and ac_text)
Person (with columns name and activityid)

I would like to create a scheme showing the activities during a week sorted on days and hours. If I ignore the person table I can fix it with the statement:
Select …. From week left join activity on (week.day = activity.day) and (week.hour = activity.hour) order by day, hour

I can then make a loop (I am usin asp.net) that writes the activities.
My problem is when I try to combine the persons to the activtities in an given hour. How do I do that ? (activity.activityid = person.activityid).

I have a little extra question. When I make the join above and print the result (day, time and activity) there isn’t any output if no activity matches a given day and hour. How do I do when I always want to print day and hour and add activity where such exist.

Select Distinct From Two Different Tables
I'd like to run a query that will pull distinct items from two separate tables. For example, I have a "user1" table and a "user2" table.

In the "user1" table is a field called "username". In the "user2" table is a field called "thisuser". Some users are in both tables - some are only in one.

I want to run a query to get me a list of all of the users, and only show them once even if they appear in both tables

Select Distinct On 2 Tables
using mysql 4.0.21

is this the best way to get distinct names from both tables where shift is
20031007:

SELECT m.user_full_name FROM press_maint m
WHERE shift_date = 20031007
UNION
SELECT p.user_full_name FROM press_prod p WHERE shift_date = 20031007
GROUP BY user_full_name

Distinct Select On 2 Tables
I'm trying to do a select distinct data from from 2 tables and the solution I'm having almost works but it's still pulling multiple records from both tables.

Here's the Select statement I'm trying:

SELECT DISTINCT table_1.table_1_id, table_1.title, table_2.field_1 FROM project, conversation WHERE table_1.table_1_id = table_2.table_2_id

Select Distinct On Two Tables
I have two tables, both containing an 'authors' column. Is there a way
to get a unique list of authors from the two tables?

I tried SELECT DISTINCT `authors` from `table1`, `table2`;

but I got an "Column 'authors' in field list is ambiguous" error.

Is there also a query to return only the count of distinct authors from
the two tables?

Distinct Select On 2 Tables
I'm trying to do a select distinct data from from 2 tables and the solution I'm having almost works but it's still pulling multiple records from both tables.
Here's the Select statement I'm trying:

SELECT DISTINCT table_1.table_1_id, table_1.title, table_2.field_1 FROM project, conversation WHERE table_1.table_1_id = table_2.table_2_id

Left Join Producing Unexpected Results
The following SQL query is producing undesirable results. What I am trying to do is get the ID of the people listed in the persons table, who are not on a given list, on the listtrack table. The listtrack table has an entry for each Person_ID<->List_ID pairing. I can find out easily who is on a given list, but it's finding who is not that is giving me bother.

SELECT people.PersonID
FROM people LEFT JOIN listtrack ON people.PersonID=listtrack.Person_ID
WHERE (listtrack.List_ID<>4 Or listtrack.List_ID IS NULL)
AND people.Company Like '(none)'

Join Results From Select Multiple Statment
how I can join results from multiple queries in one result without geting a prodict of the tables

ex:
table1_Col1, table1_Col2, table1_Col3...table2_Col1, table2_Col2, table2_Col3...table3_Col1, table3_Col2, table3_Col3...
table1_Col1, table1_Col2, table1_Col3...table2_Col1, table2_Col2, table2_Col3...table3_Col1, table3_Col2, table3_Col3...
, , ...table2_Col1, table2_Col2, table2_Col3...table3_Col1, table3_Col2, table3_Col3...
, , ... , , ...table3_Col1, table3_Col2, table3_Col3...
, , ... , , ...table3_Col1, table3_Col2, table3_Col3...

using the 4.0 sql so no subqueries supported.

Distinct Field From Two Similar Tables
I have two 'similar' tables

a) open_works_table
b) closed_works_table
now, closed_works_table have 2 extra fields compared with
open_works_table all other fields are same(name and type).

The reason for two similar tables is because open works is quite used
and contain only few rows compared to the huge number of closed
works(rarely used).

I want to get the DISTINCT list of all job_types(a field in both
table) from the two tables. How to get it? these two tables are not
related, just similar. I think, I can just pass a query
SELECT DISTINCT(job_type)
FROM {concatenated openworkstable and closedworks table}

But how to join two tables one below the other? or is there is some
other solution?

Distinct Field From Two Similar Tables
I have two 'similar' tables

a) open_works_table
b) closed_works_table
now, closed_works_table have 2 extra fields compared with
open_works_table all other fields are same(name and type).

The reason for two similar tables is because open works is quite used
and contain only few rows compared to the huge number of closed
works(rarely used).

I want to get the DISTINCT list of all job_types(a field in both
table) from the two tables. How to get it? these two tables are not
related, just similar. I think, I can just pass a query

SELECT DISTINCT(job_type)
FROM {concatenated openworkstable and closedworks table}

But how to join two tables one below the other? or is there is some
other solution?

Distinct, Order By And Limit From Multiple Tables
(I am slowly going insane over this problem)

I want to fetch the "items" that received the latest 5 comments, without showing the same item multiple times (when it has, for example, 3 comments that fall into the "latest 5 overall" category)

When I run the SQL it uses the first comment on each distinct items.id (while it should show the last/latest), and the results are therefore rather useless.

I'm using MySql 4.1.9

(I did search and see some similarish threads, but I'm too braindead right now to extract a solution on my own)

SELECT items.id, items.name, comments.id AS comment_id, comments.created AS thetime
FROM comments, items
WHERE
comments.parent_id = items.id AND
items.status = 0
GROUP BY items.id
ORDER BY thetime DESC
LIMIT 5

CREATE TABLE comments (
id INTEGER(11) NOT NULL AUTO_INCREMENT,
title VARCHAR(255) DEFAULT NULL,
created DATETIME DEFAULT NULL,
parent_id INTEGER(11) DEFAULT NULL,

UNIQUE KEY id (id)
)TYPE=ISAM ;

CREATE TABLE items (
id INTEGER(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
status INTEGER(11) DEFAULT Ɔ',

UNIQUE KEY id (id)
)TYPE=ISAM ;
--

Getting Distinct Field Values From Joined Tables
I have two tables that each have a column 'name' The value of 'name' may be repeated in both tables. If I do, 'select a.name, b.name from tablea a, tableb b' I get all the names. However, I would like to get the distinct names. But mysql gives me a syntax error if I do 'select distinct(a.name, b.name) from tablea a, tableb b'. Is there a query that will give me the distinct names from both tables?

Distinct Email Addresses In 2 Tables With Different Field Names
I have 2 tables with table A containing an 'email' field and table B
containing 2 fields 'primaryemail' and 'secondaryemail'. Now is it possible
to issue a query that would return only the unqiue email addresses in these
3 fields? So a long list with no duplicate emails(distinct).

Cannot Alias Locked Tables / Join To Unlocked Tables ?
If you manually issue a table lock then query that table, aliasing the table generates an error. If you try to join the table to another table that is not locked, you will receive an error. What is the reason for this?

Getting Results From 3 Tables
I have three tables: Person with key nId plus other fields, volchar with fields nVolCharId, nId, nActivityNum and activitynames with two fields nActivityNum and txtActivityName.
One person can be signed up for many activity so I want my output to look like: LastName, FirstName, etc plus a list of txtActivityNames (eg. Smith, Bob (activities: golf, swimming, basketball)).
Is there a way to structure a query to get those kind of results so they can be displayed in table form?

Merging Results From Two Tables
I would like to ask for some help fixing this query:

How To Combine Results From 2 Tables ?
This is for a website for classified Ads. The Ads can be posted by registered users or the Ads can come from external partner sites.

Therefore, I have kept 2 user tables - one for registered users and one for non-registered users, and 1 table for Classified Ads. The Ads table holds Ads from both registered users and external sites, with a column which indicates the source of Ad, i.e. it has two values internal and external.

I want to display all Ads on a page. The user info is to be read from the 2 user tables depending on the source of the Ad. Most of the fields of the 2 user tables are same. However, since we ask registered users to provide more information, the table for registered users has more fields.

Sorting Results From 2 Tables
I have 2 tables with movies and directors. In a third table the movies are connected with their director(s). A movie may have several directors. I need to have a list with movie titles and their director(s):

Title 1 (Director 1)
Title 2 (Director 2)
Title 3 (Director 3)
Title 4 (Director 1, Director 2)
Title 5 (Director 2)
etcetera...

Is there a way to put the directors with their movie without getting several results per movie? Or should I filter the results afterwards in PHP?

Ordering Results From Two Different Tables
I have a two tables as below:

Table: Invoices
id | InvNum | InvDate | CustNum | etc....
---------------------------------------------
1 | 100332 |2005-04-03| 1 |
2 | 100333 |2005-04-01| 2 |

Table: Customers
id | Name | Address | etc.....
--------------------------------------
1 | Bill's, Inc | 95 Lane |
2 | Abe's, LLC| |

Where Invoices.Custnum = Customers.id

I am obviously keeping two tables because the multiple invoices may have the same customer.

Anyway, I have a search tool for the invoices allowing the user to search by multiple variables (Such as invoice number, date, etc.) and order the ensuing results. One of the sorting options I need to have is by customers.name asc, invoices.invdate desc. What I need is a query that will give a result set of all the invoices matching the search parameters sorted first alphabetically by the customer's name and then by the date. I am sure that there is a way to do this but I am not very familiar with JOINS. So first does anyone have any ideas? Secondly, can anyone recommend to me a good tutorial on JOINS?

Querying From Two Tables - Multiple Results?
I have two tables, A and B. A has 10 rows and B has 5 rows. I make a query like:

SELECT a.name, a.address
FROM A a, B b
WHERE a.zipcode!=b.zipcode

The problem is that since there are 5 rows in B, I get back 5x the results. I can use LIMIT except the number of rows in A is not known, it may not be known. How can I fix this?

Combining Results From Two Pairs Of Tables
Is there a way in Query Browser to view a trigger? What about in MySQL Administrator? I can't seem to find a way to view a trigger after it has been created.

Also, wil the triggers be saved in the backup?

Querying From 2 Tables With Strange Results
What I want is to run a query that select the complete.a2 field and echos the value, where dn_records.number='5553331111'

Whats happening is all the values for complete.a2 are echoing back, even if dn_records.number isn't equal to '5553331111'

I am getting same results using the below php script as I am usnig the MySQL Query Browser.

<?php
mysql_connect( 'localhost', 'user', 'pass' )
or die( "Error! Could not connect to database: " . mysql_error() );

mysql_select_db( 'customers' )
or die( "Error! Could not select the database: " . mysql_error() );

$result = mysql_query("select complete.a2, dn_records.number from complete, dn_records where dn_records.number='5553331111'")
or die(mysql_error());

while($row = mysql_fetch_array( $result )) {
 echo "<tr><td>";
 echo $row["a2"];
 echo "</td></tr>";
}

?>

Joining Tables & Grouping Results
I am working with two tables, one listing members and another listing transactions they have made. I would like to create a list that has member details (from the member table) and total value of transactions each member has made based on how many entries in the transaction table that member has.

I guess I can run a query like this:
select * from member, transaction where member.id=transaction.member_id
and then go thru each row to produce a summary of results im after...

Merging The Results Of Multiple Tables
I have two tables. The first table is "users", the second table is "songs". The idea is that each user (one record in the users table) can upload several songs (several records in the songs table).

I have it so that each new Song added, has a foreign key that points to a user record. So dozens of Songs could all point to the same user.

My question is, I want to run a query that returns information about a user, including all their songs. So, the return table would look like:

username alias songname1 songname2 songname3
username alias songname1 songname2
username alias songname1 songname2 songname3 songname4

etc.. The songnames come from the SONGS table, and username/alias come from the USERS table, but the final result should be one table. What I've managed to get working is returning all the songs of a user, but as different rows in the table:

username alias songname
Bob bob Happy Song
Bob bob Sad Song
Bob bob Super Song

But I really want that to be returned as:

username alias songname1 songname2 songname3
Bob bob Happy Song Sad Song Super Song

So I want to merge n number of sub records, into a single user record, and return the results as 1 per user. How do I perform this feat?

Use A Mapping Table To Get Query Results From Several Tables
Ok, so I have 4 tables...

Projects -> id, project_name, project_type
Variables -> id, variable_name
Symbols -> id, symbol_name
Mapped -> project_id, variable_id, symbol_id

if I had to find

(project_id=5, variable_id=2, symbol_id=3) AND
(project_id=2, variable_id=14, symbol_id=1) AND
(project_id=34, variable_id=78, symbol_id=44)

How could I write a query that would return the results for these all at once?

I would like project_name, project_type, variable_name and symbol_name returned from their respective tables for each. Is that possible? I've been looking at inner joins and managed to make it work for 2 ids but not 3

Looking For Help In Getting Proper Search Results From Two MySQL Tables
I am building a site where there are several travel related products in a MySQL database. Each product has "x" amount of items. For example, Harrison Hot Springs Hotel has 3 items - Cash price, Points, and Points plus Cash.

I need to do a search on "items" that are equal to or less than the number of Points (item_points) and just can't seem to get it right. Their are two tables involved "products" and "prod_items".

This is the code I am using to get search results by product category and it works just fine. I'm sorry I've tried to indent my code properly so it's easier to read but don't know how to do that here.

//get product info and show
$get_prods = "select id, prod_name, city, prod_brief, valid_m, valid_d, valid_y, expiry_m, expiry_d, expiry_y from products where category='$category' order by city, prod_name";
$get_prods_res = mysql_query($get_prods) or die(mysql_error());

while ($prods = mysql_fetch_array($get_prods_res)) {
$prod_id = $prods[id];
$prod_name = stripslashes($prods[prod_name]);
$city = stripslashes($prods[city]);
$prod_brief = stripslashes($prods[prod_brief]);
$valid_m = $prods[valid_m];
$valid_d = $prods[valid_d];
$valid_y = $prods[valid_y];
$expiry_m = $prods[expiry_m];
$expiry_d = $prods[expiry_d];
$expiry_y = $prods[expiry_y];

echo "$prod_name - $city - <a href="prod_details.php?prod_id=$prod_id">Details</a> 
$prod_brief 
Purchase options for this product are valid until $expiry_m $expiry_d, $expiry_y ";

$prod_name = addslashes($prods[prod_name]);

//get item codes
$get_codes = "select item_code, item_desc, item_points, item_cash from prod_items where prod_name = '$prod_name' order by item_code";
$get_codes_res = mysql_query($get_codes) or die(mysql_error());

if (mysql_num_rows($get_codes_res) > 0) {
while ($codes = mysql_fetch_array($get_codes_res)) {
$prod_name = stripslashes($prods[prod_name]);
$item_code = $codes['item_code'];
$item_desc = $codes['item_desc'];
$item_desc = stripslashes($codes['item_desc']);
$item_points = $codes['item_points'];
$item_points = number_format($item_points);
$item_cash = $codes['item_cash'];
$item_cash = number_format($item_cash, 2);

if ($item_points == 0) {
echo "$item_code - $item_desc - $$item_cash ";
}
elseif ($item_cash == 0.00) {
echo "$item_code - $item_desc - $item_points points ";
}
else {
echo " $item_code - $item_desc - $item_points points plus $$item_cash ";
}
}

}

}

Returning 1 Tables Results From Two Table Query
I am executing a query which finds a criteria from certain records in table A and returns all values from table B if the criteria in table A is true. I want to return only the data from table B but can't find an easy way to do that? E.g the MySQL 'From' expression contains both tables.

Use A Mapping Table To Get Query Results From Several Tables
Hi all,

Ok, so I have 4 tables...

Projects -> id, project_name, project_type
Variables -> id, variable_name
Symbols -> id, symbol_name
Mapped -> project_id, variable_id, symbol_id

if I had to find

(project_id=5, variable_id=2, symbol_id=3) AND
(project_id=2, variable_id=14, symbol_id=1) AND
(project_id=34, variable_id=78, symbol_id=44)

How could I write a query that would return the results for these all at once?

I would like project_name, project_type, variable_name and symbol_name returned from their respective tables for each. Is that possible? I've been looking at inner joins and managed to make it work for 2 ids but not 3 ....

Most Recent Results, Without Using Subqueries Or Temp Tables.
I have a table whose schema is essentially as follows:

Results(name varchar(20), version varchar(20), configuration varchar(20), status varchar(20), datetime timestamp(14))

Essentially, what I would like to accomplish is to get the most recent record for name, and also for the combination of name and version.

I'm using MySQL 4.0.12 and therefore do not have access to subqueries, and would like to avoid using a temp table, which I am doing right now with limited success and considerable overhead.

I've also tried using a group by statement and using max(datetime) to get the most recent timestamp, but I end up getting the right timestamp, but the incorrect values in all of the other columns.