I SELECT f.*, c.* sometimes.
-
In last Friday's poll I threw up a query, and some people reacted to it scud-missile style.
SELECT f.*, c.* FROM faqs_topics c RIGHT JOIN faqs_questions f ON (c.id = f.category) WHERE 1 = 1 AND f.id not in (select questionid from faqs_answers) ORDER BY f.createdate
Here is a SQL fiddle. Everything is provided for you. I did make a table name change, but otherwise the select statement is unaltered.
The purpose of the SQL is to find unanswered questions and their topic.
How would you improve this? Paste your code and explanation as a response.
Difficultly bonus: You have to leave
SELECT f.*, c.*
BUT you can change the f and c to something else but the .* has to stay (this is a bonus point so you can do whatever you want).
-
scud-missile style
Is this the North Korean version of Gangnam Style?
Filed Under: Sorry, that wasn't helpful
-
I know a developer who regularly proclaimed that
SELECT table.*
was better than selecting all the columns you actually wanted manually because it saved bandwidth between PHP and MySQL and meant the query cache could be used better. He was not impressed when I told him he was talking utter bollocks.
-
Considering I wasn't expecting any comments on it at all I would say this was Troll version scud.
-
Considering I only have a few fields in each table I think .* is ok. The only reservation I have is that I have ID and ID in two of the primary selected tables.
-
Would someone enlighten me on the necessity of
WHERE 1 = 1 AND...
here?Either there is some SQL magic I'm not aware of, or OP just wrote
if(true && ...)
Also, freaking font, I don't have problems with monospace, ever, but at first I read it as fags_questions. So I was stuck for a second thinking if it's a poll for male homosexuals (and rude one at that), or he's asking for a cigarette...
-
Shits and gigs...There is no purpose for it other than it was already there and I didn't want to remove it for this post
-
There is no purpose for it other than it was already there
Why was it ever there?
Filed under: WHY IS BROWSE?
-
I usually prefer to use an anti-join than "not in (select...)", so:
SELECT f.*, c.* FROM faqs_topics c RIGHT JOIN faqs_questions f ON (c.id = f.category) LEFT JOIN faqs_answers a on a.questionid = f.id WHERE a.questionid is null ORDER BY f.createdate
I usually avoid right joins, too, because LEFT JOINs make more sense to me, and not mixing them keeps things simpler.
-
sql-fiddle database not able to be connected =(
Why the hell are you using right joins.
SELECT f.*, c.* FROM faqs_topics c RIGHT JOIN faqs_questions f ON (c.id = f.category) WHERE 1 = 1 AND not exists (select questionid from faqs_answers as z where f.id = z.questionid) ORDER BY f.createdate
-
Interesting answer let me test it out...
-
Probably because my code had it to begin with. Its valid so what's wrong with right joins?
-
Connection error...I see the error now.
SELECT f., c.
FROM faqs_topics c
RIGHT JOIN faqs_questions f ON (c.id = f.category)
LEFT JOIN faqs_answers a on a.questionid = f.id
WHERE a.questionid is null
ORDER BY f.createdateFiled Under: You borked SQLFiddle.
-
Because there's no reason to use a right join, ever. Your base table should always be the first item in the list (IMO) - and you join things against it to establish relationships.
Right joining just confuses things going from an additive table to a base table, and if you use left joins anywhere you go from an additive->base->additive, and it's just confusing.
-
(tapping feet), well then you better fix it...Since you have an opinion and all ;)
Filed under: Where is your solution.
-
What? You really don't know how to invert it? Do you need help just getting relevant fields too? D:
select f.*, c.* from faqs_questions f left join faqs_topics c on (c.id = f.category) where not exists (select questionid from faqs_answers as z where f.id = z.questionid) order by f.createdate
Filed under: I have two tables, F, and C. I'm asking for * from both. but I'm not just using select *
-
(tapping feet), well then you better fix it...Since you have an opinion and all ;)
Filed under: Where is your solution.
You're into the open source scene, aren't you.
-
-
If you really need and use every single column in the result and you are referencing the columns by name and not index in the reader, then select * isn't that bad.
If using column # in the reader, the second someone changes the structure on FAQS_QUESTIONS, you're FAQed.
-
You're into the open source scene, aren't you.
No I just like it when people tell me "its wrong". So I deflect and say, "Ok, then fix it"...That usually shuts them up because it requires additional thinking. No more critic
-
I do not have to use all colums. I did that because each table has 3 to 8 columns and the data is light.
-
I hate using
select *
except in a couple of circumstances:- Quick and dirty ad hoc query
- Slicing out part of a
with
clause, so using the column names I already described. Typically I'll have one or more of these, and using an asterisk makes maintenance easier, since I only have one place to change, and it's technically in the same query.
-
[code]SELECT f.,c.
FROM (SELECT *
FROM faqs_questions
WHERE not exists (SELECT 1 FROM faqs_answers
WHERE questionid=faqs_questions.id)
) as f
LEFT JOIN faqs_topics as c
ON c.id = f.category
ORDER BY f.createdate[/code]Another alternative. I wouldn't personally select * ever in a real production query, just if I'm doing a quick and dirty data pull for someone. But this is what he wanted so I did it.
-
Ok...here is my own solution so you guys know I am in the competition.
SELECT q.*,(SELECT description FROM faqs_topics WHERE id=q.category) AS catDescription FROM faqs_questions q WHERE q.id NOT IN (SELECT questionid FROM faqs_answers) ORDER BY q.createdate
Or maybe you are not impressed...?
Filed under: coding bunk-o free since 2003
-
If id=q.category can return more than one value, your query will explode :P
-
I'm taking a look at your solution.
You are the subquery master (that deserves some respect)...You were coding inside out. Nice approach.
-
Or maybe you are not impressed...?
Looking at how your query is progressing, I think we may have different goals.
-
I think he's aiming to make an autoposter to claim frist
-
Looking at how your query is progressing, I think we may have different goals.
AND...there goes myself confidence..
Filed under: creative solutions are ok, I'm ok, your ok
-
It cannot...(but if it does then I will be F'd in the A)
-
Is that a good, or a bad thing in your eyes?
-
Your liking of that post raises more questions than it answers
-
-
- 1 for wit and humor. 2+ for troll skills.
And your solution? Or are you all chit-chat. (scrolling up...hmmm...I don't see it).
-
-3 for sharing the observation skills of codinghorror.
http://what.thedailywtf.com/t/i-select-f-c-sometimes/1264/16?u=matches
-
Your lack of a solution speaks more volume. Even I hung my nuts out there which @boomzilla clearly kicked as soon as he saw his shot.
Filed under: FIFA on your nutsacks
-
Wait, there was a question somewhere?
What was the question?
-
I'm sorry, I am going to have to give you -19821374982347 for your lack of a 'cohesive' SQL query.
So far I have THREE functional was to do this query and one bork'd version.
-
I'm completely baffled as to what you're trying to accomplish, so I'm just going to go elsewhere now unless you are more specific
-
Ummm...I really like this part here:
WHERE not exists (SELECT 1 FROM faqs_answers WHERE questionid=faqs_questions.id)
Nicely done!!!!
-
-
I feel like there's some meta trolling going on which has subtly slipped by me this time. It's like being in a room that has recently been sterilized, and you notice some people around who don't normally run in the same circles, and they all appear to be seated in a semi circle.
-
Shit... me too.
-
I felt kind of bad that I started a Coding Help thread with a joke. So I came back and gave some serious advice, but @Frank's updates make me think he's not being serious, so I've realized that previous guilt was just a burp trying to get out.
In the interest of STFU, here's the right answer, without knowing more about the data, and ignoring the obvious WTF factor of not selecting specific columns:
SELECT f.*, c.* FROM faqs_questions f LEFT JOIN faqs_topics c ON (c.id = f.category) LEFT JOIN faqs_answers a on a.questionid = f.id WHERE a.questionid is null ORDER BY f.createdate
-
I like it. I can read it. Very clean. (sorry for social engineering away from selecting specific columns, that was not an absolute condition)... Should have made that clear.
-
What? You really don't know how to invert it? Do you need help just getting relevant fields too? D:
select f., c. from faqs_questions f left join faqs_topics c on (c.id = f.category) where not exists (select questionid from faqs_answers as z where f.id = z.questionid) order by f.createdate
…
select f., c. from faqs_questions f left join faqs_topics c on (c.id = f.category) where not exists (select questionid from faqs_answers as z where f.id = z.questionid) order by f.createdate
I fucking see it now...Sorry about that.
-
Because oneboxing?
-
Boomzilla's is a very good answer and probably the best answer in an OLTP environment.
As for my answer, I come from a data warehouse querying background, hence the use of the subquery to eliminate as much data as possible before joining. In this case it doesn't matter much because the tables are small, but in multi-billion row tables, getting the dbms to filter then join a few rows pulled out can be way better than joining it all and then weeding it out after.
Also, "In general, if your fields are properly indexed, OR if you expect to filter out more records (i.e. have a lots of rows EXIST in the subquery) NOT EXISTS will perform better."
See: http://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/
-
My background is largely in working with enterprise call center reporting (generally millions of rows) - and I've found filtering before joining to always be best, but i've also found that there are ridiculously many ways to optimize a query based on the type of data you want to see.
Best query? Weeeell, what are you looking for?
-
select f., c.
from faqs_questions f
left join faqs_topics c on (c.id = f.category)
where not exists (select questionid from faqs_answers as z
where f.id = z.questionid)
order by f.createdateYes must have been...It checks out good work on that simple flip...You forgot the asterisks but it came back clean once I added them
select f., c.
from faqs_questions f
left join faqs_topics c on (c.id = f.category)
where not exists (select questionid from faqs_answers as z
where f.id = z.questionid)
order by f.createdateOh boy..That wasn't your fault. The markdown thieved your asterisks or something. Are we seeing a bug?