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
Thanks and Help with Selection from two tables
Moderator: General Moderators
- 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
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
Re: Thanks and Help with Selection from two tables
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
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
Re: Thanks and Help with Selection from two tables
Just change his AND to OR.
Re: Thanks and Help with Selection from two tables
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
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
Re: Thanks and Help with Selection from two tables
Trydv_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
SELECT DISTINCT ID, Name FROM `tbl1`, `tbl2` WHERE `tbl1.ID` = `tbl2.ID` OR `tbl2.Year` = 2008
Re: Thanks and Help with Selection from two tables
Try this.
SELECT * FROM table1 right JOIN table2 ON table1.id=table2.id where table2.year=2008;
SELECT * FROM table1 right JOIN table2 ON table1.id=table2.id where table2.year=2008;