stumped with select query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
yaron
Forum Contributor
Posts: 157
Joined: Fri Aug 22, 2003 8:40 am

stumped with select query

Post by yaron »

Hello all,
I wnat to build this select query but i'm reallt stumped with it:
I want to compare rows from different tables or rows form the same table.
what i have is the base table with the proper id and i need to get every single row from that i.e. SELECT * FROM table WHERE id='$id'
Thats easy ,but I need to compare rows form different tables or the same table but different id that has the same specific field value
example:
base table
id field other fields
-- ----- -----------
1 rr
1 pp
1 cc
1 ff
2 rr
2 pp

other table(s)
id field other fields
-- ----- ------------
3 rr
3 ii
4 pp
4 cc

What i need is first select all the rows from base table where id=1 and the rows from the same tables that has that specific field value like the base has e.g. from base table get the rows :rr,pp at the same line as they base
and on othe table get the rows (rr,pp,cc)
so the result will be:

Code: Select all

field(from base)     id=2                               id=3                  id=4
 -----------------       ---                                ----  ---                 -----
rr                      exists(show all fields)   exists(show all fields)   missing
pp                     exists(show all fields)   missing                    exists(show)
cc                      missing                       missing                   exists(show) 
ff                       missing                       missing                    missing

?>
I hope I've managed to explain myself....
Thank u all
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Post by kendall »

yaron,

No yaron you didnt...lol im as confused as a nut but if you want to do comparasion of different tables then you should look up LEFT JOINS and HAVING CLUASES in MYSQL u may also want to take a look at Aliases in MYsql gr8 for manipulating tables

here's a scenario

Code: Select all

SELECT * FROM base table AS base and other table AS other WHERE other.id = base.id and base.id = 'var $id'
it probably not my best effort here but its just for you to get a picture...i think posted some question concerning thing similar you shud do a search for my posts in mysql database forum and see what you come up with. Sure someone else here has a better input.

Kendall
User avatar
Johnm
Forum Contributor
Posts: 344
Joined: Mon May 13, 2002 12:05 pm
Location: Michigan, USA
Contact:

Post by Johnm »

I am with Kendall... "confused as a nut" LOL
but as he mentioned, read up on joins AND ALSO read up on sub queries as well. From what I get of what you are trying to accomplish, a combonation of the two should do the trick. You may find that it is easier or just flat out necessary to make more than one querry and then use PHP to sort things out.

John M
Post Reply