Query: Inner Join Bug

Self Join Query
I am currently stuck on how the concept of a self-join works. At least in the format I want it.

I need to create a query that will show only Active parts. Parts are being replaced by new revisions and new models constantly ....

View Replies !   View Related

---

Join-query
I cannot figure out how to query the db.
I got 3 tables:

players (player_id, name, number)
stat_start (opponent_id, name_id) name_id = players.player_id
stat_avb (opponent_id, name_id name_id = players.player_id

Now I want to list all my players, and count how many times they have occured in stat_start and stat_avb.

I guess I need to do an Inner join as i want all my players listed...

View Replies !   View Related

---

Using Join In A SQL Query
I have a problem which cant sort out.
I have 3 tables where the primary keys in two tables also serve as keys in the third table.
Table1:one two three four
Table2:five six seven eight
Table3:nine ten eleven

one holds the same information as nine
five does the same for ten

Now I need to get information from the first and third tables but also need to use the second table to get the information from the third table.
I can get the info from the first table grand , but its joining the 3 tables together is where I get stuck.
I know its very abstact but this is the way I got the Tables.

View Replies !   View Related

---

Wish To Join A Query
I have this query:
PHP Code:

 SELECT * FROM `news` ORDER BY `id` DESC LIMIT 6 

I wish to also select the users id and username from the members table where the users id equals the "owner" field from the query above. As using another query apparently would be stupid?

View Replies !   View Related

---

Sub Query In An Inner Join
I am trying to write a complex query linking 3 tables, the second of which is a querry of the third. I am using the following syntax: ....

View Replies !   View Related

---

Using Both JOIN And AVG In One Query
I'm looking to use both JOIN and AVG in the same query but not sure how I'm going to it. I'm VERY new to using MySQL.

The ultimate goal is to take one table which counts votes for various criteria in a poll. I want to average those results together. Then I want to join that averaged table with another table and GROUP By URL (which is the column common to both tables).

I've successfully used AVG with the first table and used JOIN (with a totally different) table, I'm not sure how to approach using both together.

View Replies !   View Related

---

Help With A Query Join?
I have a query whereby I look in two tables a teams table and results table to output some data for some football scores.

SELECT th.team_name AS home_team, ta.team_name AS away_team
FROM results r
INNER JOIN teams th ON r.team_one_id = th.team_id
INNER JOIN teams ta ON r.team_two_id = ta.team_id
Now I want to add a third join there on another table named reports to see if the match_date in the results table matches a match_date in the reports table

SELECT th.team_name AS home_team, ta.team_name AS away_team, re.match_date
FROM results r , reports re
INNER JOIN teams th ON r.team_one_id = th.team_id
INNER JOIN teams ta ON r.team_two_id = ta.team_id
INNER JOIN reports WHERE r.match_date = re.match_date
Now this works great, but I want to output everything in the results table, and not just where results.match_date = reports.match_date as there may not be a report for every result and I want to still output every result nomatter if there is a report or not?

View Replies !   View Related

---

Using 'if' In A 'join Query' ?
I have two tables items and food_names in mysql db

items ->structure
****************************************
id food_items

1 veg

2 non-veg

3 veg & non-veg
****************************************

food-names ->structure

****************************************

id items_id foods

1 1 vbvcb

2 2 cvbvbv

3 3 gfdgdfgd

4 3 bbvcbvcb

*******************************************

if i choose veg if(id=1) I want to write a db query for both veg and veg & non-veg else if i choose non- veg I want to write a db query for both non-veg and veg & non-veg else if i choose 'veg & non-veg' then
i have to dispaly all.

View Replies !   View Related

---

JOIN Query Works With 3.23 But Not 4.0.13
I have a simple join query below which queries the name of schools with count of
student numbers for each school from two tables School,Student.

select sc.name,
Sum(if(student.SNO is not null, 1, 0)) nmbr,
from school sc
left outer join student
on sc.school_id = student.school_id
group by sc.school_id

this runs just 0.30 sec with Mysql3.23 but 30 Sec with Mysql 4.0.13.

View Replies !   View Related

---

Query Involving JOIN
I have two tables populated during the use of an application to log
user events and application states. They are named "EventTable" and
"StateTable" and the structures follow:

EventTable:

ID EventTimeStep EventID
-- ------------- ---------
1 5 E1
2 22 E2
3 56 E3

StateTable

ID StateTimeStep StateID
-- ------------- -------
1 1 S1
2 39 S2

I want to perform a query that reports the StateID of the application
at the time that each event was logged to the EventTable. The desired
output is:

ID TimeStep EventID StateID
-- -------- ------- -------
1 5 E1 S1
2 22 E2 S1
3 56 E3 S2

I have tried to create a query with an INNER JOIN where the value for
the StateID output field comes from the last row in the StateTable
WHERE StateTable.TimeStep <= EventTable.TimeStep and where I use a
GROUP_BY EventTable.ID to merge the following rows from the join:

3 56 E3 S1
3 56 E3 S2

However, the closest I can get is a query that gives me the wrong
state when applying the GROUP BY clause

3 56 E3 S1

I also think that the queries I have written is slow and inefficient.
Is there a better way to perform this query or is my database design
fatally flawed?

View Replies !   View Related

---

Join Query By Date
I've these tables:
- PERSON (id,name,age,....)
- EXAM (id,date,note,exam_type,id_user)

One person has 0 or more exams.

I have to do this report:

name, age, date, note, exam_type

This looks easy, but I have to list for each person, his/her LAST EXAM GIVEN. I mean, only the last exam must be shown for each person, and well, if person has no exam yet... to show blank (if possible)

View Replies !   View Related

---

MySQL JOIN Query
I am a recreational MySQL user, and I tend to make some non-optimal queries. In my latest project I made some LEFT JOIN queries that were extremely slow.
I made two changes that made the queries almost instant.
1) indexed the columns that the joins were working on
2) optimized the tables being joined (using phpMyAdmin > Operations)

All I can say is WOW!!! I had no idea that the resulting difference in speed would be so great. After I did step (1) it added a tremendous amount of speed, and after I did step (2) the queries responed almost immediately. So if you are running slow with JOINS, give indexing and optimization a try.

View Replies !   View Related

---

Large Join Query
I am at a loss. Right now I have a PHP script that needs to run a huge join query. They query joins two tables of approximately 200,000 records. I'm running mysql 3.x so I can't use a view... at this point indexes don't seem to help. I am thinking the only way to do this is with script processing

View Replies !   View Related

---

Left Join Query
I have this query

SELECT user.user_id, email, password, full_name, company_name,
first_login_date, last_login_date, first_draft_date, last_draft_date,
first_submit_date, last_submit_date, rowc.*
FROM user LEFT JOIN rowc ON rowc.user_id = user.user_id
WHERE user.user_type = 'USER' ORDER BY user.email

Which returns all records in user table plus rowc records if there are matches in the user id.

but user.user_id is null when there are no matching recordings in the rowc.
Isn't left join meant to pull all the records in the user table? What am I doing that is wrong here?

View Replies !   View Related

---

Table Join Query
I'm trying to join multiple database tables and having a little trouble with it, hope someone can help:
There are 3 tables with columns: id, user, date, time, action
I'm trying to get user, date, time and action from all 3 tables where the user equals "anyuser" and sort the result by date DESC, time DESC.

View Replies !   View Related

---

Run Outer Join Query
I am trying to run following query in mysql.

select a.name, a.record, b.data from A a, B b where a.name*=b.name and b.date='2006-07-28'



How can I do this in mysql? I looked up LEFT JOIN but still can't figure it out.

View Replies !   View Related

---

Multiple Join Query
I have a database with quite a lot of tables all with foreign keys of each other. One of the tables has a one to many relationship (it being the one) with two tables, but it only uses one of them for any one record. Which one is used depends on a field in the main table. This essentially defines three different types of the same item, which has mostly the same fields, but two of them also require additional, multiple record (hence the one to many) information. So the table has a field 'type' which takes the value 1 (in which case no other table is referenced), 2, (in which case the first other table is used) and 3 (in which the other table is used).

What I would ideally like to do is have one SQL query which returns information (from the main table) about any record which applies to the day (as it is part of a calendar system) specified in the query. The problem is that to decide if the record is relivent, the query must look through at most two of the three tables. I doubt that there is a way to specify in an SQL query to join to a table depending on the field of the first, and to join all three and perform the query could produce unexpected results if there were for any reason records in the third which referred to the first table. I could do this with several queries, but it would probably be more efficient to do it by one query. Would I be able to do an outer join on this?

View Replies !   View Related

---

Delayed JOIN Query
I have tried to join 3 tables via 2 different column entries.

the query is very slow.

I suppose it has to do with the design of the tables. I read that for JOIN it is important to define primary keys.

My problem is, that in one of the tables some entries of the column (which is used for joining) are empty. When I try to make a primary key on the table, phpmyadmin says that there are double entries in the column... I have tryed to declare the entries as NULL, but it still says that there are double entries (by the way, the other entries are not double, I have checked).

For a better understanding, here my command: ....

View Replies !   View Related

---

Help With Join Query Mysql 4.0
As I understand subqueries do not work in version 4.0

Here is what i am trying to do;

select a, b from table1 where table1.b != (select distinct c from table2);

Other words:
table1.b and table2.c have values I want to match on

I run
select a, b from table1;

subtract all out using

select distinct c from table2;

and leave me with a and b from table 1 that have no corresponding value of c in table 2

View Replies !   View Related

---

SQL Query JOIN And LIKE Clause
I need to run a query which uses a like query like the one below, no problem:

SELECT * FROM footballers WHERE sname LIKE '%$criteria%'

however, if I want to join two tables and still use the LIKE clause how would the syntax look??

My guess below was incorrect. any help would be appreciated. thank you

WHERE sname LIKE '%$criteria%' AND (footballers.footballerID = PremTeams.footballerID)

View Replies !   View Related

---

Join Query Chaos
I cannot get this join to give me what I need. Here is my setup.

I have 3 tables: courses, modules, sessions.

courses has this structure:

View Replies !   View Related

---

Join Query Problem
student
-------
id name
1 name1
2 name2
3 name3

Class
-----
cls_id stu_id marks
1 1 100
1 2 300
5 1 400
5 3 500

I want to show only those student that are not class &#391;'
So I wrote

select s.id,s.name,c.marks
from student s,class c
where s.id=c.stu_id
and
c.cls_id<>&#391;'

but it gives

1 name1 400
3 name3 500

but name1 is already in class &#391;' .
I desire the output only
5 3 500



View Replies !   View Related

---

Need Query To Join 2 Tables Through A 3rd
I have a table called client (primary key = clientID), another called clientFamily (pk = clientFamilyID) and a table that joins those two called client2clientFamily. The latter table only has two rows: clientID and clientFamilyID (no pk).

On the client table, say the client has 3 children and 2 siblings. Those would go on the clientFamily table. The part I don't know how to do is how to assign those 3 kids and 2 sibs to that particular client. I know I do it via the client2clientFamily table but I don't know the query.

I'm also not sure I'm thinking of this right. The fields on clientFamily start off like:

clientFamilyID
spouseFirstName
spouseMiddleName
spouseLastName
spouseOccupation
motherFirstName
motherLastName
motherOccupation
fatherFirstName
fatherLastName
fatherOccupation

I think that part's ok, but if I add:

childFirstName
childLastName
childBirthday
childSex

and

sibFirstName
sibLastName
sibOccupation

how are, for example, a child's birthday and sex are going to be linked to the correct child? And a sib's occupation linked to the correct sib? Now I'm wondering if I need a separate table for clientChildren (and a joining table client2clientChildren) and another one for clientSibs (& client2clientSibs) instead of putting everything in clientFamily. Ugh.

View Replies !   View Related

---

Join Query Problem... Help!
I have two tables:

STORES
-------
id
name

ORDERS
-------
id
total
cost
store_id
date_ordered
I want to select ALL stores and their total sales for a date range. Here's my query:

SELECT
stores.id,
stores.name,
SUM(orders.total),
SUM(orders.cost),
COUNT(orders.id)
FROM
stores
LEFT JOIN
orders ON stores.id = orders.store_id
WHERE
orders.date_ordered > 'xxxx-xx-xx xx:xx:xx'
GROUP BY
stores.id
This works, except that it only selects stores that have orders. If I leave out the WHERE clause, it then selects ALL stores, even without orders, and populates the sums with "null" That is what I want - if the stores don't have any orders, the results should simply return null or 0. However it seems that stores without orders are simply not being returned in the results at all.

View Replies !   View Related

---

Left Outer Join Query
I have noticed when I do a Left Outer Join in short form that many rows become missing as result of null values.
i.e Left Outer Join (t1,t2,t3) ON (....)
T3 being table with null values in
It seems to be all to hard when I log a bug.

View Replies !   View Related

---

Join Query Result Difference Between 3.23.49 And 4.0.13
I have what seems to me a very common operation i'm performing. I need to find the balance on an invoice. i was not having any problems until the production server was upgraded to mysql v4.0.13-standard for pc-linux. There must be a better way to query for this information than the method i'm using, since the result with v4.0 is not what I expected, nor what I received with v3.23. I'm including sample data and queries with
my results.....

View Replies !   View Related

---

Select With Join Query Question
I'm trying to write a select query that involves 2 tables. One table
(Submissions) has a one to many relationship with the other table
(Jobs). I'm trying to find all the records in Jobs that do NOT have a
corresponding record in Submissions with a status of 1.

The problem I'm having is that when there is more than one record in
Submissions that match a record in Jobs and one Submissions record has a
status of 1 and one record doesn't, my query matches the one with status
!=3D 1 and returns the record for Jobs (even though it has a record in
Submissions with a status of 1 also).

I've tried a variety of queries including left outer joins and more
simple join relationships. I'm using MySQL 3.23.47 on Windows.

Here's an example query:

select j.job_id from jobs j left outer join submissions s on (j.job_id =
=3D
s.job_id) where s.status_id !=3D 1 group by j.job_id

Any help is greatly appreciated.

View Replies !   View Related

---

DELETE Query With Left Join?
I want to delete items from a table that are determined with a left join.

using

DELETE ( SELECT * FROM item_colors LEFT JOIN items ON item_colors.item_id =
items.item_id WHERE items.group_id =3 ) FROM item_colors

does not work.

how would be the right syntax?

The SELECT part returns the items I want to delete from item_colors
correctly.

View Replies !   View Related

---

Simple JOIN Query Works With 3.23 But Not 4.0.13
I have a simple join query below which queries the name of schools with count of student numbers for each school from two tables School,Student.....

View Replies !   View Related

---

Help Needed For Query Involving JOIN
I have two tables populated during the use of an application to log
user events and application states. They are named "EventTable" and
"StateTable" and the structures follow:

View Replies !   View Related

---

Union Query To Join 4 Tables
I am trying to make a union query to join 4 tables. I have reduced the tables to just 5 fields and made sure that the field types and names are the same. I keep getting an error message ODBC call failed.

I have tried various combinations of the tables and find that I can use any 2 of them but as soon as a third is included in the statement the query fails.

Is there a limit on the number of tables in a union query. The union query looks like this in its simplest form when I have made sure that the fields names, types and position match but I have also tried by specifically naming the fields in the same order for each table

select * from T1 union select * from T2 union select * from T3 UNION select
* from T4;

View Replies !   View Related

---

Table Join Query Error
The query’s work fine when I try them separate.

$query = "SELECT id,loginName,car,yearmod,city FROM Member UNION SELECT imgName1 FROM Gallery";
$result = mysql_query($query)
or die ("DOH!");

I have tried with and without the "(" ")"

phpinfo says my MySQL Client API version is 4.0.21 so that shouldn’t be a problem, right?

View Replies !   View Related

---

Foreign Key Speeds Up INNER JOIN Query?
I've read the foreign key topic on the mysql manual but it says nothing if foreign keys optimize the queries with INNER JOIN on the foreign key. I've read that indexes in general speed it up, but is it valid too for foreign index?

I am often linking a medium sized table to a huge sized table via INNER JOIN and I wonder if setting up foreign keys would speed up the INNER JOIN. And is this effect valid to all engines? If not, to which ones is it valid?

View Replies !   View Related

---

Simple JOIN Query Issues
What I want to do is have multiple tables interact with each other.

payments TABLE
id | paidfrom | paidto | paidmethod
1 | 2 | 1 | 2
2 | 3 | 1 | 3

users TABLE
userid | username
1 | Jake
2 | Todd
3 | Spencer

methods TABLE
methodid | methodstring
1 | Cash
2 | Check
3 | Credit Card

View Replies !   View Related

---

Outer Join Count Query
I want to create an outer join along with usage of group by & count...

So, I want the rows with count=0 to be part of the result...

Select a.area, count(l.id) as no_listing from area a left join listing l on a.area=l.area group by a.area order by a.area;

Note that an area can have multiple listings...

Now, this query returns me only the areas with some positive count of listings, in spite of using a left join...

View Replies !   View Related

---

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 ....

View Replies !   View Related

---

Table Join Invalid Query
I have this table join below and I am getting an invalid query. How can I put the 15th line ($sql .= " group.id = master.id
"; and the 17th line ($sql .= " where ".$currentrow_sql ; together. Is it even possible to do that?

View Replies !   View Related

---

Figuring Out When A Query Uses An Index On Join
The variable "Select_full_join" is showing a very high number when the recommendation is to have this at zero.

I can get a full list of all queries generated on the site but how do I figure out which ones aren't using an index in the join?

View Replies !   View Related

---

Problem In Left Join Query?
I have a pretty complex query and it's practically crashing my system due to generating more data than I am looking for.

I basically have 2 tables. They should contain very similar data as they contain information about the same types of things. I was asked to join the 2 tables, getting any information from table 1 that does not exist in table 2, but keep everything of table 2. Anyway I tried this query and it just hangs forever. One problem could be because I have no ID field to match up between the tables. I'm trying to join them based on an address, city, and zip.

Select * INTO OUTFILE 'output.tab' FIELDS TERMINATED BY ' ' LINES TERMINATED BY '
'
FROM table2 LEFT JOIN table1 ON
table2.address = table1.address and
table2.city = table1.city and
LEFT(table2.zip,5) = table1.zip;
My resulting file should be 13000 records in length, with 3000 records that have additional information based on table1 data.

View Replies !   View Related

---

Join Query - I'm Losing My Mind
I'm not sure if I've totally lost my mind on this, but I can't seem to find the solution. I have two tables:

projects
---------
project_id
project_name
project_type

project_status
--------------
profile_id
project_id
status
comment

So lets say I have the following data

projects
--------
1, Project 1, Internal
2, Project 2, Internal
3, Project 3, External

project_status
--------------
1, 1, submitted, comment
1, 2, final, comment2
2, 2, initial, comment3

I want to select all rows from projects joined with the data from project_status using project_id as the key, like this:

project_query
--------------

project_id
project_name
project_type
status
comment

BUT I only want to join on rows in project_status where profile_id = 1, i.e. I get this data from the query:

1, Project 1, Internal, submitted, comment
2, Project 2, Internal, final, comment2
3, Project 3, External, , ,

Basically I don't know how I can join the project table with the project_status table which it only displays results with profile_id = 1.

View Replies !   View Related

---

Having Troubles With A LEFT JOIN Query
I'm currently building a private members area for a website where designs can be submitted and rated before they are released to the public under one unified "pack".

What I am trying to do is to create a query that lists the latest submissions along with their ratings (if they have been rated) and if the user has rated that submission or not. Here is my query:

SELECT s.id as id,
s.title as title,
s.users as creators,
ROUND(AVG(r.rating), 1) as rating,
r2.id as userhasrated
FROM submissions AS s
LEFT JOIN sub_ratings AS r
ON r.subid = s.id
LEFT JOIN sub_ratings AS r2
ON r2.userid = ".$user->getUserId()." AND r2.subid = s.id
LEFT JOIN packs AS p
ON p.id = s.pack AND p.released = 0
WHERE s.state > -1
GROUP BY
r.subid
ORDER BY s.id DESC
LIMIT 10
The problem is that it only returns two rows: one with some submission that hasn't been rated and one with some submission that has been rated (but only rated by the user indicated by $user->getUserId()).

How can I fix my code so that it returns as many rows as I desire?


View Replies !   View Related

---

Slow Query W/ Join & Ordering
I am trying to figure out why I have a hugely slow query (~2 seconds in my testing environment). Details are below:

It involves two tables, products and vendors.

Products is a huge table, so I will only include the (ostensibly!) relevant fields in its description:

CREATE TABLE `products` (
`id` int(11) NOT NULL auto_increment,
`vendor_id` smallint(6) NOT NULL default &#390;',
`product_code` varchar(255) NOT NULL default '',
`internal_name` varchar(255) NOT NULL default '',
`lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

PRIMARY KEY (`id`),
UNIQUE KEY `product_code` (`product_code`),
KEY `vendor_id` (`vendor_id`)
) ENGINE=MyISAM;
Vendors are much more straightforward:



CREATE TABLE `vendors` (
`id` smallint(6) NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM;
The following query executes in no MORE than 0.01 seconds:


SELECT DISTINCT p.id
, p.product_code
, unix_timestamp(p.lastmodified) as lastmodified
, p.internal_name
FROM products as p
ORDER BY p.product_code ASC
LIMIT 0, 30;
And has the following attributes:

+----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+
| 1 | SIMPLE | p | index | NULL | product_code | 257 | NULL | 25124 | Using temporary |
+----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+
When I join with the vendors table, so that I can fetch the vendor's name for each product, I use the following query, which takes about 1.88 seconds:



SELECT DISTINCT p.id
, p.product_code
, unix_timestamp(p.lastmodified) as lastmodified
, p.internal_name
, v.name as vendor_name
FROM products as p
LEFT JOIN vendors as v ON v.id=p.vendor_id
ORDER BY p.product_code ASC
LIMIT 0, 30;
It has the following characteristics:

+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+
| 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 25124 | Using temporary; Using filesort |
| 1 | SIMPLE | v | eq_ref | PRIMARY | PRIMARY | 2 | te_inventory.p.vendor_id | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+
Note the addition of the filesort. I'm unhappy enough about the temporary, which I don't really understand, but the filesort is, I'm fairly sure, killing me.

Closer investigation (or maybe just common sense if you aren't a MySQL newbie like me) shows that the ORDER BY clause is responsible, for when I join without the ORDER BY, my query time goes back down to 0.01 seconds or so:



mysql> explain SELECT DISTINCT p.id
-> , p.product_code
-> , unix_timestamp(p.lastmodified) as lastmodified
-> , p.internal_name
-> , v.name as vendor_name
-> FROM products as p
-> LEFT JOIN vendors as v ON v.id=p.vendor_id
-> LIMIT 0,30;
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+
| 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 25124 | Using temporary |
| 1 | SIMPLE | v | eq_ref | PRIMARY | PRIMARY | 2 | te_inventory.p.vendor_id | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+
Any clues on how I can get the execution time to go down when I am sorting? I'm also curious why MySQL is using a temporary table,

View Replies !   View Related

---

MySQL Query Assistance (JOIN)
This is probably easy for some of you DB gurus, but for me, I am having some trouble. Even after R(ing)TFM.

Background: This is for a home-grown photo gallery that I am making for my daughter, as she is going to study in Italy next year.

The query I CAN do is to pull all the sets of images from the DB and their associated images aka: `filename`

PHP

<?php
        
$query = "SELECT sets.sid AS `setID` , `name` , `filename`
        FROM `sets` , `pix_to_sets` , `pix`
        WHERE sets.sid = pix_to_sets.sid
        AND pix_to_sets.pid = pix.pid
        ORDER BY sets.time_stamp DESC";

?>


However, it would be "super cool" if I can limit this query to retrieve ALL the sets, but ONLY get one "filename" per set.

Any suggestions?

I am willing to read, so if you want to point me to a page in the manual that would help, I would appreciate it.

Or, if you are so kind as to show me the query, PLEASE explain it, so I can understand it, and do it myself next time.

View Replies !   View Related

---

Query Trouble With LEFT JOIN
My query as it echoes out in PHP...

SELECT sales_reps.sr_id,
sales_reps.order_id,
sales_reps.name,
sales_reps.job_number,
UNIX_TIMESTAMP(shop_orders.date) as date,
sales_reps.stage,
sales_reps.status, ....

View Replies !   View Related

---

Help Me Refine This Large Join Query
$sql = "SELECT f.*, p.post_time, p.post_username, u.post_subject,u.post_text,v.username
FROM (( " . phpbb_forums . " f
LEFT JOIN " . phpbb_posts . " p ON p.post_id = f.forum_last_post_id )
LEFT JOIN " . phpbb_posts_text . " u, phpbb_users v ON u.post_id = p.post_id and ON v.user_id=p.poster_id)
ORDER BY f.cat_id, f.forum_order";

Right now its select all the posts from every forum, I just want it to select the last post from every forum and the second join on phpbb_users don't seem to be working as the user_id doesn't seem to be matching on the poster_id, any suggestions?

The query also doesn't work, I want phpbb_posts to left join to two tables, posts_text and posts_topics, how to I do that?

View Replies !   View Related

---

How To Join Three Table Into A Little Complicated Query
I am using version 4.1.22. I simplifying this for example's sake. I have three tables:

order:
order_id
tip_amount

item:
item_id
order_id
price
vendor_id

vendor:
vendor_id
vendor Name

Each order can have many items. Each item has one vendor.

I need a query that displays all orders grouped by order_id, EXCEPT where there are multiple vendors where I need it to show grouped by order_id in the first column then grouped by vendor_id in the second column. Plus I need the tip_amount to show ONLY on the first line of the order.

It would like this (notice order# 299 has two entries and the tip amount was added to the first record vendor record that order):

PHP

order_id    vendor_name    total    tip298          ABC             $30     $2299          DEF             $25     $4299          XYZ             $12     300          LKW             $17     $7...

View Replies !   View Related

---

JOIN Query Related Question...
I have two tables as shown below:

Table 1: Actor.............................Table 2: Movie
Field:...ID.....Last......First..............Field:...ID.....Last......Movie
...........1.....Willis......Bruce.......................1.....Willis......Die Hard
...........2 ....Cruze....Tom..........................2.....Cruze.....MI-III
...........3.....Pitt.......Brad
...........4 ....Depp.....Johnny

Then I JOIN the tables with Query which works fine, following is the result:
Field:...Last......First........Movie
..........Willis......Bruce......Die Hard
..........Cruze....Tom.........Cruze MI-III
..........Pitt.......Brad
..........Depp.....Johnny

MY QUESTION: What JOIN option should I use so it would display data that are in both tables (such as Bruce Willis and Tom Cruze).


View Replies !   View Related

---

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?

View Replies !   View Related

---

Join Query And Sort By Date From 2 Tables
I'm having trouble with a (simple?) concept in MySQL join. This query gives me the correct data from all 3 tables, but I need to sort by the date from 2 of the tables: ....

View Replies !   View Related

---

Creating Non-existent Rows In Query With Join
I want to make report using PivotTable/CrossTab and I used an application to create it.
The problem is, I want to so show NULL value to the temp table that will be the source of my report.

I'm using this query:

View Replies !   View Related

---