Thanks and Help with Selection from two tables

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
dv_evan
Forum Commoner
Posts: 42
Joined: Wed Apr 09, 2008 8:23 am

Thanks and Help with Selection from two tables

Post by dv_evan »

Deal All,

Thank you all for being of such great help to me over last few months in learning PHP/MySQL.

I do however get stuck on some codings and cannot find help, so I would like you to help me.

Scenario:
Two tables (Table1 and Table2)

Table1 Fields: -> ID, Name,Year
Table2 Fields: -> ID, Name

SQL query:
SELECT * FROM Table1,Table2
WHERE Table1.ID = Table2.ID

Result: This will generate all the records from the tables that has the same ID.


Problem:
If I want as above, all the records from both tables with the same ID along with records from table2 with a specific year (lets say 2008) in one report, how can I acheive this?
Clearification: I need the records from both tables that has the same Id (some will be in year 2008) and also all the remaining records in Table2 that does not have matching ID with Table1 but has Year 2008.

I know I can achive these separately as follows:

SQL query for same ID from both tables as already stated above:
SELECT * FROM Table1,Table2
WHERE Table1.ID = Table2.ID

SQL query for Year = 2008 in table2
SELECT * FROM Table2
WHERE Table2.Year=2008

How can I combine these two into one query for one report, please note that the two queries will have dupications as they are, because by matching IDs you will generate records with year 2008 also. I would like to avaoid duplication, these selected all with matching IDs in both tables and those records with Year = 2008 that were not selected with matching IDs but exist in Table1

If you need any other clarification I will try to explain further.
I appreciates all assistance.
Kindly help
Thanks
Dave
User avatar
markusn00b
Forum Contributor
Posts: 298
Joined: Sat Oct 20, 2007 2:16 pm
Location: York, England

Re: Thanks and Help with Selection from two tables

Post by markusn00b »

Just use the AND clause for your mysql query.

Code: Select all

 
SELECT * FROM `tbl1`, `tbl2` WHERE `tbl1.ID` = `tbl2.ID` AND `tbl2.Year` = 2008
 
dv_evan
Forum Commoner
Posts: 42
Joined: Wed Apr 09, 2008 8:23 am

Re: Thanks and Help with Selection from two tables

Post by dv_evan »

Thanks markusn00b,
But this is not the result I am looking for.
This works well in that it will give records that have the same ID in both Tables with Year=2008.
But I want the result to include those records which do not have the same ID in both tables but have Year=2008.

Table1: (ID, Name)
001 John
002 Tom
003 Mark

Table2: (ID, Name, Year)
001 John 2008
004 Sherry 2008
005 Cindy 2007
006 Dennis 2008

I would like to generate a report that will output these results:

Result:
001 John
004 Sherry
006 Dennis

How to put together a select statment to achieve this.


Notes on Results:
001 John (because of 'Table1.ID=Table2.ID')
004 Sherry (because 'Year=2008' in Table2)
006 Dennis (because 'Year=2008' in Table2)



Thanks
Dave
Cut
Forum Commoner
Posts: 39
Joined: Sat Aug 23, 2008 8:01 pm

Re: Thanks and Help with Selection from two tables

Post by Cut »

Just change his AND to OR.
dv_evan
Forum Commoner
Posts: 42
Joined: Wed Apr 09, 2008 8:23 am

Re: Thanks and Help with Selection from two tables

Post by dv_evan »

Hi Thanks Again,

But I tried this by changing the 'AND' to 'OR' and it gives me a whole set of looping duplications.
If you test it you will see what exactly I am speaking about, I Think it has to do because we are selecting from two Tables (Table1 and Table2).

Any advise or suggestions are welcome to help solved this. It seems simple but is turning out extremely stressful.

Thanks
Dave
Cut
Forum Commoner
Posts: 39
Joined: Sat Aug 23, 2008 8:01 pm

Re: Thanks and Help with Selection from two tables

Post by Cut »

dv_evan wrote:Hi Thanks Again,

But I tried this by changing the 'AND' to 'OR' and it gives me a whole set of looping duplications.
If you test it you will see what exactly I am speaking about, I Think it has to do because we are selecting from two Tables (Table1 and Table2).

Any advise or suggestions are welcome to help solved this. It seems simple but is turning out extremely stressful.

Thanks
Dave
Try

SELECT DISTINCT ID, Name FROM `tbl1`, `tbl2` WHERE `tbl1.ID` = `tbl2.ID` OR `tbl2.Year` = 2008
User avatar
starram
Forum Commoner
Posts: 58
Joined: Thu Apr 10, 2008 1:27 am
Location: India
Contact:

Re: Thanks and Help with Selection from two tables

Post by starram »

Try this.

SELECT * FROM table1 right JOIN table2 ON table1.id=table2.id where table2.year=2008;
Post Reply