One query to retrieve data from either both or one table

[Don E]

Is it possible to query two tables at once but return only one results from one of the tables?For example, say I have a query like this:SELECT * FROM temptable INNER JOIN voteWHERE temptable.id = vote.temp_idWhat I would like to do is either retrieve all columns from both tables, BUT if I can't retrieve from table vote, then how can I only retrieve all from table temptable? I know the syntax isn't correct but here's a idea of what I'm trying to do below:SELECT * FROM temptable INNER JOIN voteWHERE temptable.id = vote.temp_idORSELECT * FROM temptableSo either retrieve all from both tables, if not for some reason, then retrieve only from temptable. Is this possible?

[justsomeguy]

It sounds like you want a left join instead of an inner join.http://www.w3schools.com/sql/sql_join_left.asp

[Don E]

Thanks JSG, that was exactly what I was looking for.Do you know if it's possible to SELECT all rows from a table except one? So for example:SELECT * FROM table EXCEPT where id = 9;I know the above is not correct but just giving you an example of what I mean.

It sounds like you want a left join instead of an inner join.http://www.w3schools.com/sql/sql_join_left.asp

[justsomeguy]

SELECT * FROM table where id != 9;

[Don E]

Would you know how to do that when query two tables with one query? For example:SELECT temptable.id, temptable.name, temptable.email, temptable.comment, vote.yes, vote.noFROM temptable INNER JOIN voteWHERE temptable.id = vote.temp_idI would like to retrieve the rows from the columns above except id number 52.I tried this but it didn't work:SELECT temptable.id, temptable.name, temptable.email, temptable.comment, vote.yes, vote.noFROM temptable INNER JOIN voteWHERE temptable.id AND vote.temp_id != 52

SELECT * FROM table where id != 9;

[justsomeguy]

SELECT temptable.id, temptable.name, temptable.email, temptable.comment, vote.yes, vote.noFROM temptable INNER JOIN voteON temptable.id = vote.temp_idWHERE vote.temp_id != 52

[Don E]

Worked perfect! Thanks! I was wondering though, is it possible to INNER JOIN more tables than 2 then? So instead of using one query to query two tables, can we do it with three or more?For example (below may not be correct, giving example of what I mean):SELECT temptable.id, temptable.name, temptable.email, temptable.comment, vote.yes, vote.no, pictures.pic1, pictures.pic2FROM temptable INNER JOIN vote, picturesON temptable.id = vote.temp_id AND temptable.id = pictures.temp_idWHERE vote.temp_id = 50 AND pictures.temp_id = 50

SELECT temptable.id, temptable.name, temptable.email, temptable.comment, vote.yes, vote.noFROM temptable INNER JOIN voteON temptable.id = vote.temp_idWHERE vote.temp_id != 52

[Don E]

I have figured out(I think) how to query 3 or more tables at once but now when I query the below I get an empty result:SELECT temptable.id, temptable.name, temptable.email, temptable.comment, vote.yes, vote.no, checkvote.vote_statusFROM temptableINNER JOIN voteON temptable.id = vote.temp_idINNER JOIN checkvoteON temptable.id = checkvote.temp_idWHERE checkvote.vote_status = 'no'However, if I change 'no' to 'yes', I get the proper results. For some reason for 'no' I am getting no results though and the checkvote table has many rows in its vote_status column with 'no'. But when I do a simple query like below to see if it will retrieve the 'no' rows specified, it works:SELECT * FROM checkvote WHERE vote_status = 'no'The vote_status column is varchar(5). At first I thought it had to do with the single quotes but from my understanding, if the column is basically going to take strings, single quotes should be used. Also, I tested it without using quotes at all and get error. I am testing these queries in phpadmin. Any input would be greatly appreciated.

[justsomeguy]

When you're using an inner join the only rows that get returned are rows that have matches in all tables. So that will only return records from temptable which have a matching record in the vote table and also a matching record in the checkvote table. Again, you can use a left join or right join to have it return all of the matching records from one table regardless of whether or not they are in the other table.

[Don E]

So in order to get results with this query:SELECT temptable.id, temptable.name, temptable.email, temptable.comment, vote.yes, vote.no, checkvote.vote_statusFROM temptableINNER JOIN voteON temptable.id = vote.temp_idINNER JOIN checkvoteON temptable.id = checkvote.temp_idWHERE checkvote.vote_status = 'no'....there has to be matches in all tables involved on the ON clause. So in other words, the primary key in temptable has to be a foreign key in the tables involved and HAVE to have match values, correct?But in the query above, not only does the above have to be true, but also because of the WHERE clause, there HAS to be at least one 'no' record in the checkvote tables' vote_status column, correct?Thanks for your time JSG aka 'The Old Man From Scene 24'(looks familiar, movie?), much appreciated. You're big help.

[Don E]

...I suppose that's correct?

[justsomeguy]

Yes, your assumptions are correct. They don't necessarily need to be primary keys, but there needs to be matching records if you're using inner join.