Hello,
I want to select the users from 'userTBL' which does not have any
property in 'live_properties' table, Both table has a same column
called 'username'
I have 2 tables called 'usersTBL' and 'live_properties'
user info stored in 'usersTBL' and their properties stored in
'live_properties'
thanks in advance
Sinan
Sinan Osan wrote:
> Hello,
> I want to select the users from 'userTBL' which does not have any
> property in 'live_properties' table, Both table has a same column
> called 'username'
> I have 2 tables called 'usersTBL' and 'live_properties'
> user info stored in 'usersTBL' and their properties stored in
> 'live_properties'
> thanks in advance
> Sinan
I would suggest that you get a book - or even a google search on how to
JOIN tables. "SQL for Dummies" is an execellant beginners tool. We can
give you the answer, but then you don't learn a whole lot from it.
I also wonder how in the world you actually got a job without knowing some
of the very basics of database queries.
I need a answer or suggestion not a smart comment,
why did you join in to a group like this with an attitude like that
towards members.
This is for my own project not for work you smart ass.
just say it if you don't know how to help.
Sinan Osan wrote:
> I need a answer or suggestion not a smart comment,
> why did you join in to a group like this with an attitude like that
> towards members.
Only because all to often there are those who do not take the time to do
some simple google queries or attempt to figure it out for themselves -
the best way to learn. The recommended book is not to assign any
intellegence level but it is an excellent resource for learning. Being
given one query will not help you understand and learn for the next time.
That being said:
this requires there to be an entry in both table1 and table2 for id.
select a.id from table1 a, table2 b
where a.id=b.id and b.property is null;
if there is no entry in table2 (properties) for a given id:
select a.id from table1 where not exist (select b.id from table2);
see the docs at:
http://dev.mysql.com/doc/refman/4.1/en/exists-and-not-exists -subqueries.html
13.2.8.6. EXISTS and NOT EXISTS
If a subquery returns any rows at all, EXISTS subquery is TRUE, and NOT
EXISTS subquery is FALSE. For example:
SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);
or
SELECT column1 FROM t1 WHERE NOT EXISTS (SELECT * FROM t2);
"Sinan Osan"
news:1143049965.204381.51080@j33g2000cwa.googlegroups.com...
>I need a answer or suggestion not a smart comment,
> why did you join in to a group like this with an attitude like that
> towards members.
I thought noone's response was a bit harsh, but please understand that in
SQL, joining two tables is a very fundamental concept, and there are
abundant resources for learning how to do it. It's comparable to someone
posting to comp.lang.c++ and asking how to code a while loop. After
answering the question about 100 times, even the most helpful person gets a
little impatient.
Here are a couple of possibilities for answering your question:
SELECT u.username, p.property_one
FROM userTBL AS u JOIN live_properties AS p ON u.username = p.username;
SELECT u.username, p.property_one
FROM userTBL AS u, live_properties AS p
WHERE u.username = p.username;
I prefer the former syntax. It is more general-purpose because it can be
modified to different types of joins. The latter syntax conforms to the
earlier SQL-1989 standard.
By the way, I recommend "SQL for Dummies" too. It's not a recommendation
intended to be condescending -- it's a good book for covering the basics.
Regards,
Bill K.
Thanks joined in to a Mysql forum and got it sorted in less than 15
minutes.
by the way how is the "SQL for Dummies" book sales going for you guys
or one guy with two username should I say.
By the way here is the working QUERY
SELECT *
FROM usersTBL AS u
LEFT JOIN propertiesTBL AS p ON u.username = p.username
WHERE p.username IS NULL;
Thanks joined in to a Mysql forum and got it sorted in less than 15
minutes.
by the way how is the "SQL for Dummies" book sales going for you guys
or one guy with two username should I say.
Joined in to MYSQL.com forum and got it sorted in less than 1 hour.
By the way good luck with the "SQL for Dummies" book sales guys or guy
with two username. I reported you to Moderator of this forum.
They are watching you.
"Sinan Osan"
news:1143064968.976204.243720@i39g2000cwa.googlegroups.com.. .
> Thanks joined in to a Mysql forum and got it sorted in less than 15
> minutes.
I'm glad you got the answer you wanted. Good luck with your project.
Regards,
Bill K.
Sinan Osan wrote:
> Joined in to MYSQL.com forum and got it sorted in less than 1 hour.
> By the way good luck with the "SQL for Dummies" book sales guys or guy
> with two username. I reported you to Moderator of this forum.
> They are watching you.
That long eh?? maybe the book title is very accurate. Especially since I
had given you a couple of methods - depending you your version and data
that would have also worked. But of course, since you do not understand
how SQL works, you wouldn't know that... With SQL there are many paths to
the correct answer.
I only wish I was the author of that book. There are many good reference
books on the proper use of SQL. And a simple google search would have
given you a plethora of examples of your query that if you understood what
it is you are doing, would have been able to figure it out. Obviously you
are of the "give it to me now" generation. Sad.
Bill, at this point in this thread, I am not so sure I was all that harsh.
It is these kinds of wanna-be programmers that prevent real programmers
from getting jobs.